Parse dynamic JSON for every array-element and every key/value pair

[teqs]

Hello,

I need to build something similar to the existing "Create HTML table"-connector by myself in flow (and as flow).

For this, at start of the connector, the input is a text which has a JSON inside. Because the JSON can have 1 to x array-elements and the inside of the JSON structure can be of every type, I can not give a JSON schema. The Flow should handle every JSON-array it will get, same as the existing "Create HTML table"-connector is doing already.

I am struggling at parsing the JSON.

I simply want to do a action (write some HTML) for every key-value pair in the JSON, and for every object (array-element).

So I have to build something like:

// outside loop, iterate overy every json array-element in the JSON
for_every_array_element_in_a_JSON i
{

  // inside of the loop, iterate overy every json key/value pair of one json array-element
  for_every_key_value_pair_in_a_array_element(i)
  {
    // do something with the key, e.g. write it to a HTML string
    // do something with the key, e.g. write it to a HTML string
  }

}

I know flow has no real for-loop. I think i have to work with to "Apply to each"-loops in flow.

But I do not understand how I can iterate over all array-elements/objects in the outer loop and to iterate over all key/value pairs of every json-data-object in the inner loop.

Someone have a few tips?

If your need a example JSON, here is one.  But please be aware that I do not need a solution which is working only with this one. I just need to find solution to iterate over the array-elements in the outer loop and then iterate over every key/value pair in the inner loop. Regardles of the specific JSON schema in detail.

Following example has a JSON with 2 array-elements and 5 key/value pairs per each array-elements.

[
{
"ID": "\"3\"",
"ItemInternalId": "14",
"Name": "14",
"ID": 14,
"Editor": {
  "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
  "Department": null,
  "JobTitle": null
  }
},
{
"ID": "\"6\"",
"ItemInternalId": "15",
"Name": 15,
"Editor": {
  "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
  "Department": null,
  "JobTitle": null
  }
}
]

Two more Examples

[
{
"ID": "\"3\"",
"day1": "14",
"week2": "14",
"user": {
  "name": "jens",
  "Department": "development"
  }
},
{
"ID": "\"6\"",
"day1": 15,
"week2": 15,
"user": {
  "name": "mike",
  "Department": "testing"
  }
}
]

[
{
"usecase": "3",
"day1": "14",
"week2": "14",
"month": "12",
"years": "2019"
} , {
"usecase": "4",
"day1": "15",
"week2": "14",
"month": "12",
"years": "2019"
} , { 
"usecase": "9", 
"day1": "15", 
"week2": "76", 
"month": "12", 
"years": "2019" 
} , { 
"usecase": "5", 
"day1": "15", 
"week2": "14", 
"month": "45", 
"years": "2020" 
} 
]

Best regards and thanks a lot!

[abm]

Hi @teqs 

In your example JSON I am struggling to understand what's the outerloop and innerloop. 

Did you tried your JSON Data to Parse JSON flow step. Then in next step it will construct a foreach loop automatically so that under the dynamic properties you can retrieve the Key/Values.

[teqs]

Hi @abm 

If you understand the main goal, then it is just fine. Perhaps outer and inner loop are not needed, if someone knows a better idea.

The JSON has different array-elements and every array-element has some key value pairs. Because of this I was thinking of two loops to iterate over both. (I edited the main post a bit to make it more clear)

See the example JSON and be aware that the solution has to deal with every JSON structure, same as "create HTML flow". As I said already.

I had a look at "Parse JSON" alread ybut I see no solution for this because I have to enter a JSON schema there. Perhaps I miss something and I can enter the JSON schema dymamiclly and iterate over array-elements and key/value perhaps with it. But for now, I did not get it managed. Or I miss something in your idea.

[abm]

Hi @teqs 

That's more clear now and thanks for editing the original post.

For Parse JSON it expects a fixed schema so you won't be able to use it as dynamically.

If you know all possible JSON structure then try to construct as one schema and try it. I am not sure this will work but worth a try.

[teqs]

Hi @abm 

Thanky for your trying to help, but I can't give "all possible JSON structure", because they are dynamically. It's really the same idea as behind the official "create HTML table" connector in MS Flow. You can pass a JSON to "create HTML table" and get a HTML table back. The JSON can differ, any count of array-elements, any stucture inside of the array-elements.

I want to do also such a general solution in flow as in "create HTML table". Of course the JSON structure has some similarities:

- 1 to x array elements

- each array element consisting of 1 to x key:vale attributs

Same as the JSON which you can send to "create HTML table".

Perhaps someone has a idea for a workarround? Perhaps I have to read the JSON line by line and seperate array-elements and key/values in flow by using of compose-nodes. This would be a much bigger workarround. I am wondering why there is no solution to read array-elements and key-value pairs just one-by-one.

Or I am simply beeing not able to design such a variant JSON schema and flow is capable if I know how.

[teqs]

@abm 

I edited the main post again and added two more variants of JSON which are possible. As you see, different count of array-elements and different structure inside of a array-element. But all have the similarities as stated above. 

[teqs]

I get it done to do a loop for every array-element in the JSON. That's not the problem.

But I do not get any solution to iterate over the key-value pairs (at JSON it is called "properties" I think) in one of the JSON arrays.

But this could make my question much easier, without speaking of inner and outer loops anymore. 🙂

So my main questions is:

- How do I iterate over all key/value pairs (Properties) in a JSON without knowing the key-names (and don't know the property count)?

- So, I look for a possibility, to get all keys and their values out of a JSON.

- As I said, the JSON is dynamic, but consist of n key-value pairs in every case. So, how do I make a loop get all key/values (properties) of a JSON one-by-one and do a action for every key/value one-by-one?

- See JSON examples in the main post.

[abm]

Hi @teqs 

I don't think you can achieve this via Parse JSON Flow step as your case it's going to be dynamic. Like you mentioned you could possbily do via Compose step using lots of string manipulations which then the solution might going to be very complex. 

All I could think is you need to host a custom API handler and pass the JSON object as input parameter. Response Output parameter you could return this as an array back to flow. But this need some programming effort and hosting of API efforts.

https://stackoverflow.com/questions/46569434/c-sharp-json-convert-any-dynamic-object-to-key-value-pa...

You can also post this under Flow ideas.

https://powerusers.microsoft.com/t5/Flow-Ideas/idb-p/FlowIdeas

[teqs · Accepted solution]

Im found a solution.

It is descibed here to parse JSON and transform it to XML and then search keys with XPATH in Flow:

http://johnliu.net/blog/2017/11/foreach-property-in-microsoftflow-json-with-xpath-microblog

Here is another idea with credits to the one above:
https://powerusers.microsoft.com/t5/General-Flow-Discussion/Getting-JSON-keys-in-flow/td-p/99743

And here is another solution with doing the JSON to XML transformation:
https://powerusers.microsoft.com/t5/General-Flow-Discussion/convert-json-output-into-xml/td-p/160893

[abm]

Hi @teqs 

Thats good to know. Thanks for letting me know about it.