Parse JSON embedded array to single rows

[VB2020]

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.

[evansdar · Accepted solution]

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

 

[VB2020]

@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?

[evansdar]

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

[VB2020]

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