cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Parse JSON Array

Hi all,

 

I'm having a hard time writing a decent expression to parse out numerous json data. I have included a sample of the json and the flow that I am building. I would like to parse it right into sharepoint list but my expressions don't seem to be working. I have watched various videos but no luck so far. I think I'm not getting the expression for the array correct but I'm not sure.

 

Edit: So what I'm looking for is an expression I can use to parse the data. If it was a regular json, I could use:

body(Parse_JSON)?['ChemicalComposition']['Key']['Value']['Minimum']

but since it's an array, I'm not sure how to manipulate the above expression to get the min and max (or how to identify the position of what I'm looking for) of all the values.

 

 

"ProductClassification": [
    {
      "Key": "Slab",
      "Value": false
    },
    {
      "Key": "Blooms",
      "Value": false
    },
    {
      "Key": "Billets",
      "Value": false
    }
],
  "ProductClassificationComments": null,
  "ChemicalComposition": [
    {
      "Key": "Aluminum",
      "Value": {
        "Minimum": "96.9",
        "Maximum": "98.95"
      }
    },
    {
      "Key": "Antimony",
      "Value": {
        "Minimum": "0",
        "Maximum": "0"
      }
    }

 

 

ProfileCrater3_0-1613077347876.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Paulie78
Super User
Super User

This was quite an interesting challenge, you can do it like this:

ProfileCreator.png

Start with your JSON and put it into a select action, put the select action into text mode. Use this expression for the "from":

outputs('JSON')['ChemicalComposition']

This will bring out the ChemicalComposition array as the source for the select. Then in the map use:

concat
(
  '"', 
  item()['Key'],'-Max": ',
  item()['Value']['Maximum'],
  ', "',
  item()['Key'],'-Min": ',
  item()['Value']['Minimum']
)

The purpose of this it to make each record into a JSON property, like this:

  "Aluminum-Max": 98.95,
  "Aluminum-Min": 96.9,

Then in the final compose step use:

json(concat('{', join(body('Select'), ','), '}'))

This will create a single json object from the array created in the select. Which looks like:

{
  "Aluminum-Max": 98.95,
  "Aluminum-Min": 96.9,
  "Antimony-Max": 0,
  "Antimony-Min": 0,
  "Bismuth-Max": 0,
  "Bismuth-Min": 0,
  "Boron-Max": 0,
  "Boron-Min": 0
}

Then you can simply put this into a Parse JSON step and you will be able to add your SharePoint record on that basis.

 

Complex and simple at the same time!

 

View solution in original post

11 REPLIES 11
PrasadAthalye
Super User
Super User

JSON output you pasted seems to be incomplete. Can you paste the entire output?

 

I tried below and it works...

 

{
"ProductClassification": [
    {
      "Key": "Slab",
      "Value": false
    },
    {
      "Key": "Blooms",
      "Value": false
    },
    {
      "Key": "Billets",
      "Value": false
    }
],
  "ProductClassificationComments": null,
  "ChemicalComposition": [
    {
      "Key": "Aluminum",
      "Value": {
        "Minimum": "96.9",
        "Maximum": "98.95"
      }
    },
    {
      "Key": "Antimony",
      "Value": {
        "Minimum": "0",
        "Maximum": "0"
      }
    }
]
}
Please Like and Mark this as Answer if it resolves your Issue.

Power Automate Community
Anonymous
Not applicable

@PrasadAthalye Thanks for posting. I forgot to remove the comma at the end 😁

So is your issue resolved?

Please Like and Mark this as Answer if it resolves your Issue.

Power Automate Community
Anonymous
Not applicable

Not at all @PrasadAthalye . I provided a sample JSON above. The actual JSON is actually pretty lengthy. I am having trouble parsing the array (i.e. getting the right expression) into the sharepoint list. If it would help, I can post the entire json.

Yes Please. There is an attachment possible in the response. Can you please attach the entire JSON? 

Please Like and Mark this as Answer if it resolves your Issue.

Power Automate Community
Anonymous
Not applicable

I don't see where I can add an attachment. Although, that would be really helpful. I'll post it here then delete it after you have copied it. @PrasadAthalye 

 

 

 

 

 

 

Paulie78
Super User
Super User

It's actually really tricky because you have to transpose that array into a single row so that you can easily insert it into your SharePoint list.

Anonymous
Not applicable

Thanks for the reply! I'm no expert on flows by a long shot but I knew  was doing something wrong. I had a feeling there were extra steps but can't seem to figure out what those steps are. @Paulie78 

Paulie78
Super User
Super User

This was quite an interesting challenge, you can do it like this:

ProfileCreator.png

Start with your JSON and put it into a select action, put the select action into text mode. Use this expression for the "from":

outputs('JSON')['ChemicalComposition']

This will bring out the ChemicalComposition array as the source for the select. Then in the map use:

concat
(
  '"', 
  item()['Key'],'-Max": ',
  item()['Value']['Maximum'],
  ', "',
  item()['Key'],'-Min": ',
  item()['Value']['Minimum']
)

The purpose of this it to make each record into a JSON property, like this:

  "Aluminum-Max": 98.95,
  "Aluminum-Min": 96.9,

Then in the final compose step use:

json(concat('{', join(body('Select'), ','), '}'))

This will create a single json object from the array created in the select. Which looks like:

{
  "Aluminum-Max": 98.95,
  "Aluminum-Min": 96.9,
  "Antimony-Max": 0,
  "Antimony-Min": 0,
  "Bismuth-Max": 0,
  "Bismuth-Min": 0,
  "Boron-Max": 0,
  "Boron-Min": 0
}

Then you can simply put this into a Parse JSON step and you will be able to add your SharePoint record on that basis.

 

Complex and simple at the same time!

 

View solution in original post

Anonymous
Not applicable

A-M-A-Z-I-N-G! Thank you so much! I'll try to use this as a format for the others. Thank you again! 😆

Anonymous
Not applicable

@Paulie78  Sorry to bother you but one more question. How do I implement this with arrays without "Minimum", "Maximum"? such as:

 "ToughnessTemperature": [
    {
      "Key": "DropWeightTearTesting",
      "Value": null
    },
    {
      "Key": "ImpactCharpyTesting",
      "Value": null
    },
    {
      "Key": null,
      "Value": null
    }
  ],
  "ToughnessJoules": [
    {
      "Key": "DropWeightTearTesting",
      "Value": null
    },
    {
      "Key": "ImpactCharpyTesting",
      "Value": null
    },
    {
      "Key": null,
      "Value": null
    }

 

I tried editing the concat expression but it's not working for some reason.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (1,922)