Hi everyone,
I am calling an API that brings back JSON data. I then want to convert this JSON data into a format I can put into a table (e.g. CSV). I googled this and found I can use the Query with JSON SQL resource (alasql). However, I am running into one problem and have a question on how to transform the child data into a single column.
Here is the JSON data I get back from the API (with the first query - let's called it query1). Note that I have eliminated some of the fields so it is not too long to read:
{
"ID": "2413910",
"ProductIDFormatted": "N98R016202033",
"AnalysisIDFormatted": "1GWKRYM",
"NumOfStonesInReport": "6",
"TotalWeight": "1.00",
"PrintedReportImageLocation": "2413910-0.jpg,2413910-1.jpg",
"Version": "13",
"ReportProgram_ID": "715",
"cid": 475,
"CertPagesAvailable": 2,
"interfaceBranding": null,
"diamondBrandPath": null,
"diamondBrand": "navstr",
"status": "ok",
"stones": [
{
"ID": "4692220",
"StoneNumInSet": 1,
"StoneIDFormatted": "N98R016202033",
"ShapeFormatted": "Round",
},
{
"ID": "4692221",
"GemExReport_ID": "2413910",
"StoneNumInSet": 2,
"StoneIDFormatted": "N98R016201954",
"ShapeFormatted": "Round",
},
{
"ID": "4692223",
"GemExReport_ID": "2413910",
"StoneNumInSet": 3,
"StoneIDFormatted": "N98R016201702",
"ShapeFormatted": "Round",
},
{
"ID": "4692225",
"GemExReport_ID": "2413910",
"StoneNumInSet": 4,
"StoneIDFormatted": "N98R016201966",
"ShapeFormatted": "Round",
},
{
"ID": "4692222",
"GemExReport_ID": "2413910",
"StoneNumInSet": 5,
"StoneIDFormatted": "N98R016202049",
"ShapeFormatted": "Round",
},
{
"ID": "4692224",
"GemExReport_ID": "2413910",
"StoneNumInSet": 6,
"StoneIDFormatted": "N98R016202067",
"ShapeFormatted": "Round",
}
]
}
In query2, I then have the following:
select {{ query1.data.ID }}
from {{ query1.data }}
The issue/problem is when I run query1 it comes back with 15 rows of the same ID: 2413910 (screenshot here: https://share.getcloudapp.com/4gu1BgEP. It should come back with 1 row since there is only one ID in the query1.data.ID. I noticed that the JSON data actually have 15 parent fields. Is that why it is returning 15 rows, and if so, what is the proper query to bring back one row?
The question I have is it possible to convert the six children IDs into one column separated by commas? For example, for the JSON data above, I like to get it into this format:
Column 1 Column 2
2413910 4692220,4692221,4692223,4692225,4692222,4692224
Any help would be greatly appreciated!
