Answered

Filtering Nested JSON Arrays

(0) Share

Report

Posted on by ATarrant

Hi all,

I have a JSON (see below) which contains a series of form submissions. Each submission includes a formSubmissionId and a series of fields with their corresponding fieldReferenceId and fieldValue.

What I want to do is produce a new array listing all the records where the fieldReferenceId '18c837cd-1dae-4961-9164-3de23e9c954b' has a fieldValue of 'a1GJ7000000sZ9cMAE'.

In the example JSON below, there are two submitted records. I want to extract only the second one, because it has a fieldValue of 'a1GJ7000000sZ9cMAE' for the fieldReferenceId '18c837cd-1dae-4961-9164-3de23e9c954b'.

Due to the potential size of the data, I believe using a 'FilterArray' would be more efficient than nested 'Apply To Each' actions.

I really appreciate any help with this.

Thanks.

{
 "statusCode": 200,
 "headers": {
 "Date": "XXXXXX",
 "Content-Type": "XXXXXX",
 "Content-Length": "XXXXXX"
 },
 "body": {
 "data": [
 {
 "formId": 72684,
 "formSubmissionId": 123456789,
 "createdTimeStamp": "2024-05-16T08:26:29.913",
 "modifiedTimeStamp": "2024-05-16T08:26:29.93",
 "fields": [
 {
 "fieldReferenceId": "d9005789-8b54-4b24-ab44-16723ae7ddfc",
 "fieldValue": "123, Apple Road"
 },
 {
 "fieldReferenceId": "a6e7ace1-f4cb-4c2d-894d-73693164d82f",
 "fieldValue": "sn9 2uh"
 },
 {
 "fieldReferenceId": "18c837cd-1dae-4961-9164-3de23e9c954b",
 "fieldValue": "0WOJ7000000XsXyOAK"
 }
 ],
 "userGroups": [],
 "assignedToUsers": [],
 "unassignedFromUsers": []
 },
 {
 "formId": 9347,
 "formSubmissionId": 6299592,
 "createdTimeStamp": "2024-05-16T08:26:29.913",
 "modifiedTimeStamp": "2024-05-16T08:26:29.93",
 "fields": [
 {
 "fieldReferenceId": "d9005789-8b54-4b24-ab44-16723ae7ddfc",
 "fieldValue": "456 Orange Crescent"
 },
 {
 "fieldReferenceId": "a6e7ace1-f4cb-4c2d-894d-73693164d82f",
 "fieldValue": "sw3 8rf"
 },
 {
 "fieldReferenceId": "18c837cd-1dae-4961-9164-3de23e9c954b",
 "fieldValue": "a1GJ7000000sZ9cMAE"
 }
 ],
 "userGroups": [],
 "assignedToUsers": [],
 "unassignedFromUsers": []
 }
 ]
 }
}

Categories:

General topics

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

Chriddle 8,515 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Compose (your JSON)

Spoiler (Highlight to read)
{ "statusCode": 200, "headers": { "Date": "XXXXXX", "Content-Type": "XXXXXX", "Content-Length": "XXXXXX" }, "body": { "data": [ { "formId": 72684, "formSubmissionId": 123456789, "createdTimeStamp": "2024-05-16T08:26:29.913", "modifiedTimeStamp": "2024-05-16T08:26:29.93", "fields": [ { "fieldReferenceId": "d9005789-8b54-4b24-ab44-16723ae7ddfc", "fieldValue": "123, Apple Road" }, { "fieldReferenceId": "a6e7ace1-f4cb-4c2d-894d-73693164d82f", "fieldValue": "sn9 2uh" }, { "fieldReferenceId": "18c837cd-1dae-4961-9164-3de23e9c954b", "fieldValue": "0WOJ7000000XsXyOAK" } ], "userGroups": [], "assignedToUsers": [], "unassignedFromUsers": [] }, { "formId": 9347, "formSubmissionId": 6299592, "createdTimeStamp": "2024-05-16T08:26:29.913", "modifiedTimeStamp": "2024-05-16T08:26:29.93", "fields": [ { "fieldReferenceId": "d9005789-8b54-4b24-ab44-16723ae7ddfc", "fieldValue": "456 Orange Crescent" }, { "fieldReferenceId": "a6e7ace1-f4cb-4c2d-894d-73693164d82f", "fieldValue": "sw3 8rf" }, { "fieldReferenceId": "18c837cd-1dae-4961-9164-3de23e9c954b", "fieldValue": "a1GJ7000000sZ9cMAE" } ], "userGroups": [], "assignedToUsers": [], "unassignedFromUsers": [] } ] } }
{ "statusCode": 200, "headers": { "Date": "XXXXXX", "Content-Type": "XXXXXX", "Content-Length": "XXXXXX" }, "body": { "data": [ { "formId": 72684, "formSubmissionId": 123456789, "createdTimeStamp": "2024-05-16T08:26:29.913", "modifiedTimeStamp": "2024-05-16T08:26:29.93", "fields": [ { "fieldReferenceId": "d9005789-8b54-4b24-ab44-16723ae7ddfc", "fieldValue": "123, Apple Road" }, { "fieldReferenceId": "a6e7ace1-f4cb-4c2d-894d-73693164d82f", "fieldValue": "sn9 2uh" }, { "fieldReferenceId": "18c837cd-1dae-4961-9164-3de23e9c954b", "fieldValue": "0WOJ7000000XsXyOAK" } ], "userGroups": [], "assignedToUsers": [], "unassignedFromUsers": [] }, { "formId": 9347, "formSubmissionId": 6299592, "createdTimeStamp": "2024-05-16T08:26:29.913", "modifiedTimeStamp": "2024-05-16T08:26:29.93", "fields": [ { "fieldReferenceId": "d9005789-8b54-4b24-ab44-16723ae7ddfc", "fieldValue": "456 Orange Crescent" }, { "fieldReferenceId": "a6e7ace1-f4cb-4c2d-894d-73693164d82f", "fieldValue": "sw3 8rf" }, { "fieldReferenceId": "18c837cd-1dae-4961-9164-3de23e9c954b", "fieldValue": "a1GJ7000000sZ9cMAE" } ], "userGroups": [], "assignedToUsers": [], "unassignedFromUsers": [] } ] } }

Select

From

xpath( xml( json( concat( '{"Root":', outputs('Compose'), '}' ) ) ), '//data[fields[fieldReferenceId="18c837cd-1dae-4961-9164-3de23e9c954b" and fieldValue="a1GJ7000000sZ9cMAE"]]' )

Map

json(item())['data']

Result

[ { "formId": "9347", "formSubmissionId": "6299592", "createdTimeStamp": "2024-05-16T08:26:29.913", "modifiedTimeStamp": "2024-05-16T08:26:29.93", "fields": [ { "fieldReferenceId": "d9005789-8b54-4b24-ab44-16723ae7ddfc", "fieldValue": "456 Orange Crescent" }, { "fieldReferenceId": "a6e7ace1-f4cb-4c2d-894d-73693164d82f", "fieldValue": "sw3 8rf" }, { "fieldReferenceId": "18c837cd-1dae-4961-9164-3de23e9c954b", "fieldValue": "a1GJ7000000sZ9cMAE" } ] } ]

Was this reply helpful? Yes No
ATarrant 6 on at

Like (0)

Report
Copy link

Link copied!

Thanks Chriddle - worked like a charm!

Was this reply helpful? Yes No
ATarrant 6 on at

Like (0)

Report
Copy link

Link copied!
Hi Chriddle,
I was just using your expression, and I want to change the static
fieldValue="a1GJ7000000sZ9cMAE"
To a dynamic value earlier within the same flow. The reference to the dynamic value is:
triggerBody()?['body']?['VisitID']
Would you be able to tweak y our expression to accept this? I've tried with the expression below, and whilst it runs, it doesn't return any records, when it should:
xpath( xml( json( concat( '{"Root":', outputs('GetDataCollector'), '}' ) ) ), '//data[fields[fieldReferenceId="18c837cd-1dae-4961-9164-3de23e9c954b" and fieldValue=triggerBody()?['body']?['VisitID']]]' )

Was this reply helpful? Yes No
Chriddle 8,515 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
You have to concat this into the xpath string:

xpath( xml( json( concat( '{"Root":', outputs('Compose'), '}' ) ) ), concat( '//data[fields[fieldReferenceId="18c837cd-1dae-4961-9164-3de23e9c954b" and fieldValue="', triggerBody()?['body']?['VisitID'], '"]]' ) )

Was this reply helpful? Yes No