โ02-18-2021 08:58 AM
I have a REST API that returns something like the following:
entity":
{
โitemsโ:
[
{
โtableNameโ:
โmytableโ
โcountโ:
7995
โcolumnNamesโ:
[
โc1โ,โc2โ,โc3โ,โc4โ
]
โrowsโ:
[
[r1c1value,r1c2value,r1c3value,r1c4value],[r2c1value,r2c2value,r2c3value,r2c4value],[โฆ],[โฆ],[โฆ],[โฆ],[โฆ],[โฆ],[โฆ],[โฆ]
]
}
]
}
When I refer to jsonPath($, โentity.items[*]โ) it gives me columns as an array of columns and the rows are an array of rows.
How can I parse this so that I can map it to a Mapper to put into SQL?
Thanks
Solved! Go to Solution.
โ02-18-2021 03:25 PM
If I captured your input data correctly, this attached pipeline should help get you started:
Community.9525.slp (4.8 KB)
The mapper combines the Array map() method with the sl.zipObject() method to bring the column names and row values together in an object array. The following splitter will flatten the data for your SQL Insert.
โ02-18-2021 09:30 AM
Hi @Garrett
Probably, you should try with JSON Splitter Snap, and try to split the arrays containing the values/fields that you want to map further.
Regards,
Spiro Taleski
โ02-18-2021 09:34 AM
pull out two arrays separately using a mapper 1) entity.items.columnNames 2) entiy.items.rows and then map the values.
โ02-18-2021 03:25 PM
If I captured your input data correctly, this attached pipeline should help get you started:
Community.9525.slp (4.8 KB)
The mapper combines the Array map() method with the sl.zipObject() method to bring the column names and row values together in an object array. The following splitter will flatten the data for your SQL Insert.
โ02-19-2021 11:31 AM
This looks really good and looks like the solution. I am getting an error and it is probably because Iโm slightly off on the object above.
Failure: Expecting object for JSON-Path field reference โentityโ, found: null, Reason: Expecting object for JSON-Path field reference โentityโ, found: null, Resolution: Change the path to refer to an object
It really is:
{statusline:{},entity{},headers{}}
When I refer to anything in there though I have always used $entity as you did so I am not understanding why it isnโt doing the reference properly.
I added a first splitter to get:
jsonPath($, โentity.items[*]โ) this then let:
the Mapper Object see columnNames and rows in the Input Schema on the left which when I just connect the JSON output to the mapper there was nothing showing in the Input Schema.
It looks good now but the Json Splitter isnโt returning anything and hence the mapper. No errors. No output.