Dynamically create array from object (parse complex JSON)

[rgr2023]

Hello everyone,

I wanted to seek some advice regarding a task I'm currently working on. I have a complex JSON that I'm fetching from an API, and I need to extract specific data from it to populate an SQL database.  Specifically, I am interested in retrieving the temperature, distance, and readTimestamp when the type is "lastil90ReadingsV1" and the readingType is "laser".

At the moment, my approach involves creating an Object variable and then converting it into an Array. After converting the JSON to an Array, I filter the array to extract the relevant data, and then I parse the JSON. Finally, I send the extracted data to an SQL database.

However, I encounter a challenge with this method as I need to hardcode the keys of the objects. As the JSON will become larger with hundreds of objects instead of just nine, my current solution is not feasible for the future.

I'm looking for a more dynamic approach to create an array from all the objects without hardcoding their keys. If anyone has suggestions or alternative methods, I would greatly appreciate your input. I've attached a snippet of the JSON for reference. Feel free to ask for further clarification if needed.

Thank you in advance for your help and suggestions!

[lbendlin]

 After converting the JSON to an Array, I filter the array to extract the relevant data, and then I parse the JSON.

that sounds a little backwards.  Wouldn't you want to parse the JSON first?

There is a problem with your JSON though, it is not well formed.

See how in line 2 the item identifier is a hard coded text.  It is somewhat mitigated by line 5 where the nodeId is properly indicated. However this is still a hurdle in Power Automate

Unable to process template language expressions in action 'Compose' inputs at line '0' and column '0': 'The template language expression 'variables('Content')[0]' cannot be evaluated because property '0' cannot be selected. Object properties can only be selected by names. Please see https://aka.ms/logicexpressions for usage details.'.

This means it expects named references which you don't have at the top layer of the JSON.

Then you have another issue that your type parameter is ambiguous as that name is used in line 6, and line 21 and 28 ! Which one do you want to use?

Specifically, I am interested in retrieving the temperature, distance, and readTimestamp when the type is "lastil90ReadingsV1" and the readingType is "laser".

readTimestamp is a parent attribute to the readings collection. Are there any other parent attributes you need to pull?

[rgr2023]

I think I’ve already posted a reply but I cannot see it, so I’ll post it again.

Thank you for your prompt response. I must admit that I'm new to JSON and this is my first time working with it, so I apologize if my previous post wasn't clear enough.

The issue with the JSON is that it comes from a third-party API, and I have no control over its structure; therefore, I cannot modify it and must work with it as is. Initially, I tried parsing the JSON, but unfortunately, that approach didn't work for me. So, I decided to take an alternative approach. Even with this new approach, I had to make some adjustments to the JSON schema to make the parsing action function correctly. I’ve attached the schema.

In my opinion, it should be Line 6 because I need to access the "readings" object and then retrieving the "distance" and "temperature" values within "readingType": "laser" (which is present only in "type": "lastil90ReadingsV1"). As you can see, it's a bit complex, especially for someone like me who is still getting familiar with JSON.

If I understand the JSON correctly, the parent of "type" (line 6) is indeed the parent of the object "78463." Assuming my explanation is accurate, I only need to retrieve this parent attribute, and it should be sufficient.

Please let me know if you require any additional information. Your help is greatly appreciated.

[lbendlin]

I played with this a bit yesterday and have come to the conclusion that your JSON is pretty much unparseable by Power Automate.   Power Automate expects objects and arrays with clearly defined names.  Your JSON mixes these concepts and also throws in variable node names.

I am not saying it is invalid JSON, but I am saying it is far outside of the structure expected by Power Automate's parser.  If you look at the schema you provided - even that has multiple contradictions.

It might be possible to manually parse the text row by row (ignoring the JSON structure) but that's not something I would  recommend doing.

[rgr2023]

Thank you for taking the time to review and play around with the JSON. I appreciate your feedback. I agree that the current JSON structure makes it challenging to parse correctly.

However, do you think it is possible to improve my workaround. If I can iterate through all objects in createArray without hardcoding, it would be a significant enhancement, despite the initial JSON parsing challenge.

createArray(variables('ReadingsObject')['78463'],variables('ReadingsObject')['78531'],variables('ReadingsObject')['78590'],variables('ReadingsObject')['78602'],variables('ReadingsObject')['78655'],variables('ReadingsObject')['78705'],variables('ReadingsObject')['78709'],variables('ReadingsObject')['78733'],variables('ReadingsObject')['78758'])

Open to your suggestions. Thanks!

[lbendlin]

That would make it a little less horrible, but then you may hit the next brick wall because inside each nodeId you have a random number of subsections ("id")  . However some of them can be filtered by type.  Let me play with that idea some more.