• EN
Question

API with separate arrays for columns/rows

  • 1 April 2024
  • 4 replies
  • 65 views
lneidhardt
Rank
Badge +1

Hi there,

Relatively new to FME and very new to JSON and API’s. I’m working with the Smartsheet API and I’m stuck on how to the get the data set up properly, I’ve gone down many of the JSON Transformer rabbit holes and I can’t find a similar example to how this data is set up. 

When I use the JSON Fragmenter, I get columns and rows returned as separate arrays:  

The columns are set up like this: 

And the rows/cells:

I’ve gotten as far as exposing the attributes for each array separately, but I don’t know how to get cells back to their associated rows/columns. And I’m sure there’s a better way than I’m doing it :)

4 replies

AliAtSafe
Rank
Userlevel 1
Badge +9

Hi, @lneidhardt, welcome to the community.

There is a tutorial series that covers API’s and JSON here. Great examples and explanations.

Its possible using multiple JSONFragmenter Transformers as explained by a community user in a post here, though it is a bit complex.

To retrieve specific parts, I would try out the JSONFlattener transformer (JSON tutorial).

Hope this helps!

debbiatsafe
Rank
Userlevel 3
Badge +17

Hello @lneidhardt 

It appears the target API returns each row’s cell headers as a reference to the column JSON object via columnId on the row/cell object and id on the column object. To make linkages between the two, you can:

  • Flatten the column JSON object on each feature as a list attribute
  • Create features for each cell of each row, exposing columnId
  • Use ListSearcher to find matching list index between columnId and id column list attribute
  • Create attributes with matched attribute name
  • Recreate each sheet row by aggregating

Alternatively, if you are comfortable using JSONiq/XQuery for JSON, you can create a new JSON document based on the JSON response but with the cell values and headers on each sheet row. This is a more advanced method that requires knowledge of JSONiq/XQuery for JSON but uses fewer transformers.

Attached is a workspace demonstrating both approaches.

1 Attachment
lneidhardt
Rank
Badge +1

Amazing - thank you so much for the help!!

danilo_fme
Rank
Userlevel 5
Badge +30

Hello @lneidhardt 

It appears the target API returns each row’s cell headers as a reference to the column JSON object via columnId on the row/cell object and id on the column object. To make linkages between the two, you can:

  • Flatten the column JSON object on each feature as a list attribute
  • Create features for each cell of each row, exposing columnId
  • Use ListSearcher to find matching list index between columnId and id column list attribute
  • Create attributes with matched attribute name
  • Recreate each sheet row by aggregating

Alternatively, if you are comfortable using JSONiq/XQuery for JSON, you can create a new JSON document based on the JSON response but with the cell values and headers on each sheet row. This is a more advanced method that requires knowledge of JSONiq/XQuery for JSON but uses fewer transformers.

Attached is a workspace demonstrating both approaches.

Good Job!

Reply


Community Stats

28,812
Posts
109,833
Replies
36,630
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.