Merge two JArrays and output to data table

Hi Experts

I have two JSON arrays that contains data for a survey; one that contains the questions of the survey and the other containing the responses including the answers to the questions.

I would like to output the answers to a data table - one row per response - with the headers ResponseId, SubmitDate, ResponderMail, ResponderName and a column for each question defined dynamically with the question title as the column name.

The array of questions look like this:

{"questions":[{"id":"r8da630b676c040538dfbe5a947ce0580","title":"Potential","order":2500500.0,"type":"Question.Choice","required":true},{"id":"r98a0411d74cf42cfa1b3d6446f9f1c18","title":"Performance","order":1000500.0,"type":"Question.Rating","required":true},{"id":"r0fe5a59a072b4dabad17e479b50e7981","title":"Readiness","order":4000500.0,"type":"Question.Choice","required":true}]}

The array of responses looks like this:

{"value":[{"id":1,"startDate":"2023-01-24T08:14:43.3137132Z","submitDate":"2023-01-24T08:14:50.3847132Z","responder":"noreply@noreply.com","responderName":"John Doe","answers":"[{\"answer1\":\"Ready now (RN)\\t\",\"questionId\":\"r0fe5a59a072b4dabad17e479b50e7981\"},{\"answer1\":\"High\",\"questionId\":\"r8da630b676c040538dfbe5a947ce0580\"},{\"answer1\":\"1\",\"questionId\":\"r98a0411d74cf42cfa1b3d6446f9f1c18\"}]","releaseDate":"0001-01-01T00:00:00Z","quizResult":null,"emailReceiptConsent":null,"submitLanguage":null,"msRewardsData":null,"FormsProData":null},{"id":2,"startDate":"2023-01-24T09:52:57.8419087Z","submitDate":"2023-01-24T09:53:04.8049087Z","responder":"noreply@noreply.com","responderName":"John Doe","answers":"[{\"answer1\":\"Ready later (RL)\\t\",\"questionId\":\"r0fe5a59a072b4dabad17e479b50e7981\"},{\"answer1\":\"Medium\",\"questionId\":\"r8da630b676c040538dfbe5a947ce0580\"},{\"answer1\":\"5\",\"questionId\":\"r98a0411d74cf42cfa1b3d6446f9f1c18\"}]","releaseDate":"0001-01-01T00:00:00Z","quizResult":null,"emailReceiptConsent":null,"submitLanguage":null,"msRewardsData":null,"FormsProData":null}]}

The data table output should be like:

ResponseId SubmitDate ResponderMail ResponderName Potential Performance Readiness
1 2023-01-24T08:14:50 noreply@noreply.com John Doe Ready now (RN) High 1
2 2023-01-24T09:53:04 noreply@noreply.com John Doe Ready later (RL) Medium 5

Somehow I need to merge the two arrays or at least be able to lookup the question title for each answer by matching the question ID.

How can this be achieved?

Hi @jacchr

Please take a look at this topic on how to change the JArray to Datatable:

Then you can use merge the datatables based on id with Join Data Tables activity:
image

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.