topic Parse JSON embedded array to single rows in Talend Studio

Parse JSON embedded array to single rows

VB2020 — Sat, 16 Nov 2024 01:43:17 GMT

Hello,

I have some JSON code (below) which I need to parse and transform into a single row in an output CSV or XLSX file. Should this be done as separate fields for 'questionId', 'answer' and 'comment' in tExtractJSONFields components or could this be done in the tMap component? The number of nodes within each 'responses' embedded array would remain consistent - 3 nodes within each instance of 'responses' (i.e., questionId1, questionId2, questionId3, and so on for the other fields). The below JSON is a small example - the actual JSON is much larger but the structure and content is exactly the same as below.

I need to change this:

{

"data": [

{

"id": "101",

"type": "doc",

"attributes": {

"documents": {

"responses": [

{

"questionId": "1",

"answer": "N",

"comment": null

{

"questionId": "2",

"answer": "N",

"comment": null

{

"questionId": "3",

"answer": "Y",

"comment": null

}

]

}

"meta": {

"totalCount": null

}

into the output image attached (I was unable to paste it in place here)

Please advise as to how this can be done. Thank you.

Re: Parse JSON embedded array to single rows

evansdar — Fri, 07 Aug 2020 13:49:50 GMT

This configuration worked for me, although you may need to change it a bit once you use the full JSON

Re: Parse JSON embedded array to single rows

VB2020 — Mon, 10 Aug 2020 09:43:51 GMT

@Daniel Evans That's exactly what I need. I'm trying to work from your example, but please clarify - how did you manage to choose "content" from the 'JSON field' dropdown in your tExtractJSONFields_1 subjob when "content" isn't in my sample JSON? Is "content" something you added to your schema? If so, could you please show me where you added it?

Re: Parse JSON embedded array to single rows

evansdar — Mon, 10 Aug 2020 13:20:17 GMT

It really depends on how your JSON is being retrieved. Are you using a webservice, or pulling directly from a file? I named the column myself, and used a tFileInputRaw with 'Read as String' selected for your example.

The JSON Field here is not anything within the JSON itself, rather how Talend/Java identifies the JSON you are passing

Re: Parse JSON embedded array to single rows

VB2020 — Wed, 12 Aug 2020 10:39:58 GMT

@Daniel Evans Your solution has worked for me also. Many thanks for your help and guidance.