Power Automate Parse JSON is not understanding the array

(0) Share

Report

Posted on by johnjohnPter

1,558 Super User 2024 Season 1

I am working with a 3rd party API which return a list of contracts, here is a sample of the JSON:-

{
 "OOH.wu.2023.9.53": {
 "Brand": "***",
 "Contract Number": "OOH.wu.2023.9.53",
 "Client Name": "****",
 "Sector": "FOODS",
 "Media_list": [
 {
 "Network": "****",
 "Amount": "4000.000",
 "Duration": "1 Month",
 "of_Faces": "1",
 "Start_Date": "01-Oct-2023",
 "End_Date": "01-Nov-2023",
 "Delivery Date": "01-Oct-2023"
 },
 {
 "Network": "****",
 "Amount": "4800.000",
 "Duration": "1 Month",
 "of_Faces": "1",
 "Start_Date": "01-Oct-2023",
 "End_Date": "01-Nov-2023",
 "Delivery Date": "01-Oct-2023"
 },
 {
 "Network": "****",
 "Amount": "4250.000",
 "Duration": "1 Month",
 "of_Faces": "1",
 "Start_Date": "01-Oct-2023",
 "End_Date": "01-Nov-2023",
 "Delivery Date": "01-Oct-2023"
 }
 ],
 "No of Faces": "3"
 },
 "OOH.wu.2023.7.50": {
 "Brand": "****",
 "Contract Number": "OOH.wu.2023.7.50",
 "Client Name": "****",
 "Sector": "****",
 "Media_list": [
 {
 "Network": "****",
 "Amount": "4250.000",
 "Duration": null,
 "of_Faces": "1",
 "Start_Date": "14-Aug-2023",
 "End_Date": "14-Sep-2023",
 "Delivery Date": "14-Aug-2023"
 },
 {
 "Network": "****",
 "Amount": "4250.000",
 "Duration": null,
 "of_Faces": "1",
 "Start_Date": "09-Aug-2023",
 "End_Date": "09-Sep-2023",
 "Delivery Date": "09-Aug-2023"
 }
 ],
 "No of Faces": "2"
 }
 
}

but when i use Parse JSON action inside my automated flow, it did not understand that i need to iterate over a list of contracts, rather it statically parse the objects as follow:-

For example, i was expecting to get one Brand property and when i chose it >> power automate will add ApplyToEach.. so is the issue with the JSON? or i can manage this inside my flow? as in our case we might get 10 contracts and not just 2 contracts

... any advice where is the issue and how i can fix it?

All responses (3)

Answers (0)

v-yueyun-msft on at

Like (0)

Report
Re: Power Automate Parse JSON is not understanding the array

Hi , @johnjohnPter
As for your json, the field Name is not the static like this:
OOH.wu.2023.7.50
If this , it is hard to extract the value if the json key is dynamic . The best way is directly use the expression to extract your value.

If this reply can help you , you can click mark this reply as solution (Accept solution) which can help more people, thanks in advance!

Best Regards,
Yueyun Zhang
johnjohnPter 1,558 Super User 2024 Season 1 on at

Like (0)

Report

Re: Power Automate Parse JSON is not understanding the array

@v-yueyun-msft thanks for the reply. but as you can see i have lot of properties so is not there an easier way to extract the data?
v-yueyun-msft on at

Like (0)

Report
Re: Power Automate Parse JSON is not understanding the array

Hi , @johnjohnPter
According to your description, do you mean you want to loop the [Brand] field in your json?
And for your json, it is Object instead of an array so it can not loop in your side.
For your need , you can try to use the Xpath to extract the data array you need from your json.
This is my test json:
{ "OOH.wu.2023.9.53": { "Brand": "Brand1", "Contract Number": "OOH.wu.2023.9.53", "Client Name": "****", "Sector": "FOODS", "Media_list": [ { "Network": "****", "Amount": "4000.000", "Duration": "1 Month", "of_Faces": "1", "Start_Date": "01-Oct-2023", "End_Date": "01-Nov-2023", "Delivery Date": "01-Oct-2023" }, { "Network": "****", "Amount": "4800.000", "Duration": "1 Month", "of_Faces": "1", "Start_Date": "01-Oct-2023", "End_Date": "01-Nov-2023", "Delivery Date": "01-Oct-2023" }, { "Network": "****", "Amount": "4250.000", "Duration": "1 Month", "of_Faces": "1", "Start_Date": "01-Oct-2023", "End_Date": "01-Nov-2023", "Delivery Date": "01-Oct-2023" } ], "No of Faces": "3" }, "OOH.wu.2023.7.50": { "Brand": "Brand2", "Contract Number": "OOH.wu.2023.7.50", "Client Name": "****", "Sector": "****", "Media_list": [ { "Network": "****", "Amount": "4250.000", "Duration": null, "of_Faces": "1", "Start_Date": "14-Aug-2023", "End_Date": "14-Sep-2023", "Delivery Date": "14-Aug-2023" }, { "Network": "****", "Amount": "4250.000", "Duration": null, "of_Faces": "1", "Start_Date": "09-Aug-2023", "End_Date": "09-Sep-2023", "Delivery Date": "09-Aug-2023" } ], "No of Faces": "2" } }

You can try to use this code to extract the [Brand] field.
xpath( xml(json( concat( '{"root":' , string( outputs('Compose')) , '}' ) )) , '//Brand//text()' )
Then you can loop this Brand array in your side ;
The result is as follows:
For this , you can refer to :
Solved: Getting JSON keys in flow - Power Platform Community (microsoft.com)

If this reply can help you , you can click mark this reply as solution (Accept solution) which can help more people, thanks in advance!

Best Regards,
Yueyun Zhang