How to transform a JSON defined as object to a JSON in array format

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I want to create an array from an xml extract.

I managed to convert the xml to JSON but it is defined as an object.

Here's the format I currently have:

**************************start of sample************************************

{
"?xml": {
"@version": "1.0",
"@encoding": "utf-8"
},
"SOAP-ENV:Envelope": {
"@xmlns:SOAP-ENV": "http://schemas.xmlsoap.org/soap/envelope/",
"@xmlns:SOAP-ENC": "http://schemas.xmlsoap.org/soap/encoding/",
"@xmlns:xsd": "http://www.w3.org/2001/XMLSchema",
"@xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance",
"SOAP-ENV:Header": {
"@xmlns:SOAP-ENV": "http://schemas.xmlsoap.org/soap/envelope/"
},
"SOAP-ENV:Body": {
"ns2:retrieveNominationOutput": {
"@xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance",
"@xmlns:ns2": "http://www.synergy.net.au/wts/collgar/",
"status": {
"statusCode": "0"
},
"nomination": {
"nominationId": "661561",
"energyNominationTable": {
"intervalNomination": [
{
"intervalStart": "2023-05-16T08:00:00",
"counterPartyNomination": {
"nominatedEnergy": "11.110",
"counterParty": "WPGENER"
}
},
{
"intervalStart": "2023-05-16T08:30:00",
"counterPartyNomination": {
"nominatedEnergy": "71.963",
"counterParty": "WPGENER"
}
},
{
"intervalStart": "2023-05-16T09:00:00",
"counterPartyNomination": {
"nominatedEnergy": "96.153",
"counterParty": "WPGENER"
}
},
{
"intervalStart": "2023-05-23T07:30:00",
"counterPartyNomination": {
"nominatedEnergy": "31.472",
"counterParty": "WPGENER"
}
}
]
}
}
}
}
}
}

**************************end of sample************************************

I want to transform it to the following format... I need help to enable me to do the transformation, ie just taking the parts that I can manage as an array.

**************************start of sample************************************

[
{
"intervalStart": "2023-05-16T08:00:00",
"counterPartyNomination": {
"nominatedEnergy": "11.110",
"counterParty": "WPGENER"
}
},
{
"intervalStart": "2023-05-16T08:30:00",
"counterPartyNomination": {
"nominatedEnergy": "71.963",
"counterParty": "WPGENER"
}
},
{
"intervalStart": "2023-05-16T09:00:00",
"counterPartyNomination": {
"nominatedEnergy": "96.153",
"counterParty": "WPGENER"
}
}
]

**************************end of sample************************************

Thanks a lot!

biTiqa

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

HenWang Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Anonymous ,

Can you try this expression:
I put the sample value into this "sample" variable
variables('sample')?['SOAP-ENV:Body']?['ns2:retrieveNominationOutput']?['nomination']?['energyNominationTable']?['intervalNomination']

You can replace the beginning part about this variable with the output dynamic content from your side.

Best Regards,
Hen Wang

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Thank you Hen Wang,
I followed your details above but the flow returns blank. I think I understand how you structure it but still I don't get the expected output.
Here's what I have defined so far, can you help checking which part that I didn't do right pls?

Was this reply helpful? Yes No
Verified answer

HenWang Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Anonymous ,

Sorry, my bad. Please try below expression, I missed the "SOAP-ENV:Envelope" property:

variables('varSOAOutput')?['SOAP-ENV:Envelope']?['SOAP-ENV:Body']?['ns2:retrieveNominationOutput']?['nomination']?['energyNominationTable']?['intervalNomination']

Best Regards,
Hen Wang

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

it works!!! Thanks so much for your help... I have accepted your solution resolving my query. Thanks heaps...

Was this reply helpful? Yes No
HenWang Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Anonymous ,

For the nominationId, please use:
variables('sample')?['SOAP-ENV:Envelope']?['SOAP-ENV:Body']?['ns2:retrieveNominationOutput']?['nomination']?['nominationId']

BR,
Hen Wang

Was this reply helpful? Yes No