Selecting values from nested JSON array

[RichardUchytil]

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!

[abm]

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

[RichardUchytil]

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!  🙂

[abm]

Hi @RichardUchytil 

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

Thanks

[abm]

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 "
}

[RichardUchytil]

That is correct.

[abm]

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

[RichardUchytil]

Correct again.  

[RichardUchytil]

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

[abm]

Hi @RichardUchytil 

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

Thanks

[abm · Accepted solution]

Hi @RichardUchytil 

As promised here it is

https://youtu.be/YGFHWBt9f_U

If you have any further questions please let me know.

Thanks

[RichardUchytil]

That's awesome!  I'll give it a try tomorrow morning and let you know.  Thanks much for the video, that was super helpful!!

[RichardUchytil]

THAT WORKED GREAT!!!  That you very much for taking the time to put together that video, it was a HUGE help!!!

[abm]

Hi @RichardUchytil 

Glad to hear that all working well. If you have any questions please post in this community.

Thanks

[whatsfordinner]

This might be a tad late, but I recently had a similar issue and I found that the most elegant (though not the simplest) way to solve it is by converting the JSON to XML and selecting objects via XPath. All done in a single step. Here is the link to the tutorial https://forwardforever.com/using-xpath-in-your-flow/