Parse JSON Array

[Anonymous]

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"
      }
    }

[PrasadAthalye]

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"
      }
    }
]
}

[Anonymous]

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

[PrasadAthalye]

So is your issue resolved?

[Anonymous]

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.

[PrasadAthalye]

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

[Anonymous]

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]

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]

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 · Accepted solution]

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

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!

[Anonymous]

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]

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