Power Automate Parse JSON is not understanding the array

(0) Share

Report

Posted on by johnjohnPter

1,809 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?

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (0)

v-yueyun-msft on at

Like (0)

Report
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

Was this reply helpful? Yes No
johnjohnPter 1,809 Super User 2024 Season 1 on at

Like (0)

Report

@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?

Was this reply helpful? Yes No
v-yueyun-msft on at

Like (0)

Report
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

Was this reply helpful? Yes No