Unanswered

Parse Json Schema

(0) Share

Report

Posted on by rziya

Hello,

I am having trouble parsing the JSON from an API that I can not change. Sample http response is below it contains a projects object with id's like: prid001, prid002, ... that change for each project. The schema produced from sample does not give me the project ids. What would be a correct schema for a json with objectIds like this?

Thanks

Ruchan

{
 "body": {
 "projects": {
 "prid001": {
 "data": {
 "inquiryNumber": "1",
 "customer": "E",
 "name": "Name 1"
 },
 "projectType": "c"
 },
 "prid002": {
 "data": {
 "inquiryNumber": "2",
 "customer": "B",
 "name": "Name 2"
 },
 "projectType": "c"
 }
 }
 }
}

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (0)

fchopo 8,003 Moderator on at

Like (0)

Report
Hi @rziya

If you need to access the project id's, then json file should look like this:

{ "body": { "projects": [ { "id": "prid001", "data": { "inquiryNumber": "1", "customer": "E", "name": "Name 1" }, "projectType": "c" }, { "id": "prid001", "data": { "inquiryNumber": "2", "customer": "B", "name": "Name 2" }, "projectType": "c" } ] } }

Actually, with the current schema, Power automate will expect always an attribute called "prid001" and "prid002".

Are you able to manipulate that JSON?

Best,

Ferran

Was this reply helpful? Yes No
rziya 12 on at

Like (0)

Report
Hi @fchopo ,

Thanks for the reply.

Unfortunately I am unable to change the incoming JSON.

I did work with this API/JSON using Power Query in Excel and DataFlows. There I was able to parse this json in a few steps. See the Power Query below... How can I duplicate this in Power Automate?

let Source = Json.Document(jsonInput), body = Source[body], projects = body[projects], #"Converted to Table" = Record.ToTable(projects), #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"data", "projectType"}, {"Value.data", "Value.projectType"}), #"Expanded Value.data" = Table.ExpandRecordColumn(#"Expanded Value", "Value.data", {"inquiryNumber", "customer", "name"}, {"Value.data.inquiryNumber", "Value.data.customer", "Value.data.name"}) in #"Expanded Value.data"

Was this reply helpful? Yes No
fchopo 8,003 Moderator on at

Like (0)

Report
Hi @rziya

I did a test on my side and I'm able to get project Ids. I use a Parse JSON, using the sample you provided:

The schema is the following:

{ "type": "object", "properties": { "body": { "type": "object", "properties": { "projects": { "type": "array", "items": { "type": "object", "properties": { "id": { "type": "string" }, "data": { "type": "object", "properties": { "inquiryNumber": { "type": "string" }, "customer": { "type": "string" }, "name": { "type": "string" } } }, "projectType": { "type": "string" } }, "required": [ "id", "data", "projectType" ] } } } } } }

Hope that helps!

Ferran

Was this reply helpful? Yes No
rziya 12 on at

Like (0)

Report
Hi Ferran,

Thanks for the reply, I guess your schema would work if my JSON had a tag for id like. But it does not and I am unable to change it. As my original json without a tag for ids is also a valid json, I was hoping it would be possible to write a schema for it...

{ "body":{ "projects":[ { "id":"", "data":{ "inquiryNumber":"", "customer":"", "name":"" }, "projectType":"" } ] } }

But my Json does not have the id tag.

Was this reply helpful? Yes No
fchopo 8,003 Moderator on at

Like (0)

Report
Hi @rziya

Sorry, I think I did something wrong.

My schema was based on your sample, and generated automatically by Power Automate:

And generated schema is the following:

{ "type": "object", "properties": { "body": { "type": "object", "properties": { "projects": { "type": "object", "properties": { "prid001": { "type": "object", "properties": { "data": { "type": "object", "properties": { "inquiryNumber": { "type": "string" }, "customer": { "type": "string" }, "name": { "type": "string" } } }, "projectType": { "type": "string" } } }, "prid002": { "type": "object", "properties": { "data": { "type": "object", "properties": { "inquiryNumber": { "type": "string" }, "customer": { "type": "string" }, "name": { "type": "string" } } }, "projectType": { "type": "string" } } } } } } } } }

As I said, there's no id attribute, so the schema will be different each time (you have "prid001" and "prid002") but in next iterations, they could be different (like "prid003"). I guess that's not possible to manage that with Parse JSON action and probably you need to treat it like a string, but not sure it's going to work.

Hope that helps!

Ferran

Was this reply helpful? Yes No
rziya 12 on at

Like (0)

Report

Thanks, at the moment I am using a dataflow, that I posted earlier, to parse and save the fields into a dataverse table, then I read the dataverse table within my flow...not ideal, but works...

Was this reply helpful? Yes No
mailrhk 14 on at

Like (0)

Report

i have created a flow for azure vm deployment, can anyone help me to get the output on parse JSON.

Was this reply helpful? Yes No