Unanswered

Selecting values from nested JSON array

(2) Share

Report

Posted on by RichardUchytil

173

I've got a Power Automate Flow that is parsing a PDF using Adobe Services. I had it working fine but I realized I'm missing some data that is in a nested JSON array and I can't figure out how to select that. My a snippet of what the JSON looks like:

{
"Path": "//Document/Sect/Table[3]/TR[2]/TD/P",
"HasClip": false,
"TextSize": 12.0,
"Lang": "en",
"Page": 0,
"Text": "31-MAY-2021 "
},
{
"Path": "//Document/Sect/Table[3]/TR[2]/TD[2]/P",
"Lang": "en",
"Page": 0,
"Kids": [
{
"Path": "//Document/Sect/Table[3]/TR[2]/TD[2]/P/Sub",
"HasClip": false,
"TextSize": 12.0,
"Page": 0,
"Text": "Pharmacy Claims processed "
},
{
"Path": "//Document/Sect/Table[3]/TR[2]/TD[2]/P/Sub[2]",
"HasClip": false,
"TextSize": 12.0,
"Page": 0,
"Text": "for period 16-MAY-2021 thru 31-MAY-2021 "
}

]

}

The stuff in green is what I need. Here's my flow:

The first Path and Text are working, they are item()?['Path'] and item()?['Text'].

For KidsPath and KidsText I tried item()?['Path']?['Kids']?['Path'] and item()?['Path']?['Kids']?['Text'] but that's not working.

Any suggestions? Thanks!

Categories:

Building flows

I have the same question (0)

All responses (14)

Answers (1)

abm abm 32,865 Most Valuable Professional on at

Like (0)

Report

Hi @RichardUchytil

"Kids": [
{
  "Path": "//Document/Sect/Table[3]/TR[2]/TD[2]/P/Sub",
"HasClip": false,
"TextSize": 12.0,
"Page": 0,
  "Text": "Pharmacy Claims processed "
},
{
  "Path": "//Document/Sect/Table[3]/TR[2]/TD[2]/P/Sub[2]",
"HasClip": false,
"TextSize": 12.0,
"Page": 0,
  "Text": "for period 16-MAY-2021 thru 31-MAY-2021 "
}

]

Since Kids element property is an array, it won't be possible to store the values under the select. Is this a fixed array and always have 2 values only? Then it is possible to do something like

item()?['Path']?['Kids'][0]?['Path']

item()?['Path']?['Kids'][1]?['Path']

If you have dynamic array then it won't be possible to do.

Your other option is use Parse JSON to iterate it. The construct your own JSON schema which will flatten the kids array values and other elements together so that you can use that in a Select separately.

If you need further help I could make a tutorial video for this. Please let me know.

Thanks

Was this reply helpful? Yes No
RichardUchytil 173 on at

Like (0)

Report

That item()?['Path']?['Kids'][0]?['Path'] didn't work, I got this error:

InvalidTemplate. The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '{ "Path": "@item()?['Path']", "Text": "@item()?['Text']", "Kids": "@item()?['Kids']", "KidsPath": "@item()?['Path']?['Kids'][0]?['Path']" }' failed: 'The template language expression 'item()?['Path']?['Kids'][0]?['Path']' cannot be evaluated because property 'Kids' cannot be selected. Property selection is not supported on values of type 'String'. Please see https://aka.ms/logicexpressions for usage details.'.

And it is a dynamic array, there can be multiple records. This is from a PDF invoice and this is the middle section where they itemize the charges. I am able to select the Kids array like this:
Kids = item()?['Kids']

But now I'm not sure how to parse through that array. I tried adding another Select on that array but I'm not sure what the From should look like. I tried body('Select')['Kids'] and outputs('Select')['Kids'] but that didn't work.

FYI, I'm parsing all of this and inserting into a SQL Server table. I had it working fine until I realized I was missing the Kids data. Thanks much for your help! 🙂

Was this reply helpful? Yes No
abm abm 32,865 Most Valuable Professional on at

Like (0)

Report

Hi @RichardUchytil

Thanks for your reply. I will get back to you soon.

Thanks

Was this reply helpful? Yes No
abm abm 32,865 Most Valuable Professional on at

Like (0)

Report

Hi @RichardUchytil

I have noticed some don't have kids property. Is that correct?

{
  "Path": "//Document/Sect/Table[3]/TR[2]/TD/P",
"HasClip": false,
"TextSize": 12.0,
"Lang": "en",
"Page": 0,
  "Text": "31-MAY-2021 "
}

Was this reply helpful? Yes No
RichardUchytil 173 on at

Like (1)

Report

That is correct.

Was this reply helpful? Yes No
abm abm 32,865 Most Valuable Professional on at

Like (0)

Report

Hi @RichardUchytil

Thanks for your quick reply.

Another question related to JSON Data

"HasClip": false,
"TextSize": 12.0,

I have noticed these are missing as well. Is that means some of the properties are optional?

Thanks

Was this reply helpful? Yes No
RichardUchytil 173 on at

Like (0)

Report

Correct again.

Was this reply helpful? Yes No
RichardUchytil 173 on at

Like (1)

Report

Would it be easier to convert the JSON to XML and deal with the XML?

Was this reply helpful? Yes No
abm abm 32,865 Most Valuable Professional on at

Like (0)

Report

Hi @RichardUchytil

I am making a video about this. Send you soon.

Thanks

Was this reply helpful? Yes No
Verified answer

abm abm 32,865 Most Valuable Professional on at

Like (5)

Report

Hi @RichardUchytil

As promised here it is

https://youtu.be/YGFHWBt9f_U

If you have any further questions please let me know.

Thanks

Was this reply helpful? Yes No