How to handle different JSON payloads without a schema

(0) Share

Report

Posted on by ArbeidTest

Spoiler (Highlight to read)

Hi, I need some help with how to do this.

We have about 20 different forms that create JSON payloads. Each of them has a different schema

I could create 20 different flows, use ParseJSON, specify schema, but I would prefer to have one flow that can handle the all the forms.

Luckily the data is quite flat, so I don't have to loop through any subobjects.

I would like to get the data into a format where I can loop through the key/value pairs so that I can process them later.

I have tried to convert to string and split on "," but this failed as the free text fields can have commas.
I didn't manage to split on NewLine either

Its driving me crazy, as if I did this in Powershell I could do this in one command
$parsed = $data | ConvertFrom-Json

Any ideas? Do I really have to write my own parser function to make this work?

Example 1
{
"E-post": "kevin.b@test.com",
"FirstName": "Kevin",
"FreeText": "Anything, including commas and quotes",
"Mobile": 5553344,
"Process": "Test",
"Random": 657899154,
"Receipt": "JVBERi0xLjcKJ",
"Referance": "[ID]:009900025791",
"SYSTEM_Finalized": true,
"SYSTEM_Time": "2024-01-17T14:29:29.6701953Z",
"SYSTEM_User": "",
"Surname": "Bacon",
"UserIdentifier": 75435451111
}

Example 2
{
"E-post": "bob.b@test.com",
"FirstName": "Bob",
"Text": "Anything, including commas and quotes",
"Mobile": 55533344,
"Process": "Testing",
"RandomNumber": 657899154,
"Receipt": "JVBERi0xLjcKJ",
"ReferanceCode": "[ID]:009900025791",
"SYSTEM_Finalized": true,
"SYSTEM_Time": "2024-01-17T14:29:29.6701953Z",
"SYSTEM_User": "",
"Surname": "Bacon",
"UserID": 75435451111
"OtherData": "djlkajdlkas"
}

Hi, I need some help with how to do this.We have about 20 different forms that create JSON payloads. Each of them has a different schemaI could create 20 different flows, use ParseJSON, specify schema, but I would prefer to have one flow that can handle the all the forms.Luckily the data is quite flat, so I don't have to loop through any subobjects.I would like to get the data into a format where I can loop through the key/value pairs so that I can process them later.I have tried to convert to string and split on "," but this failed as the free text fields can have commas.I didn't manage to split on NewLine eitherIts driving me crazy, as if I did this in Powershell I could do this in one command$parsed = $data | ConvertFrom-JsonAny ideas? Do I really have to write my own parser function to make this work?Example 1{"E-post": "kevin.b@test.com","FirstName": "Kevin","FreeText": "Anything, including commas and quotes","Mobile": 5553344,"Process": "Test","Random": 657899154,"Receipt": "JVBERi0xLjcKJ","Referance": "[ID]:009900025791","SYSTEM_Finalized": true,"SYSTEM_Time": "2024-01-17T14:29:29.6701953Z","SYSTEM_User": "","Surname": "Bacon","UserIdentifier": 75435451111}Example 2{"E-post": "bob.b@test.com","FirstName": "Bob","Text": "Anything, including commas and quotes","Mobile": 55533344,"Process": "Testing","RandomNumber": 657899154,"Receipt": "JVBERi0xLjcKJ","ReferanceCode": "[ID]:009900025791","SYSTEM_Finalized": true,"SYSTEM_Time": "2024-01-17T14:29:29.6701953Z","SYSTEM_User": "","Surname": "Bacon","UserID": 75435451111"OtherData": "djlkajdlkas"}

Categories:

General topics

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

Chriddle 8,513 Super User 2025 Season 2 on at

Like (1)

Report
Copy link

Link copied!
Something like this?

From:

xpath( xml(json(concat('{"Root":{"Item":', outputs('Example1'),'}}'))), '/Root/Item/*' )

Map key:

xpath( item(), 'name(/*)' )

Map value:

first( xpath( item(), '/*/text()' ) )

Result:

[ { "key": "E-post", "value": "kevin.b@test.com" }, { "key": "FirstName", "value": "Kevin" }, { "key": "FreeText", "value": "Anything, including commas and quotes" }, { "key": "Mobile", "value": "5553344" }, { "key": "Process", "value": "Test" }, { "key": "Random", "value": "657899154" }, { "key": "Receipt", "value": "JVBERi0xLjcKJ" }, { "key": "Referance", "value": "[ID]:009900025791" }, { "key": "SYSTEM_Finalized", "value": "true" }, { "key": "SYSTEM_Time", "value": "2024-01-17T14:29:29.6701953Z" }, { "key": "SYSTEM_User", "value": null }, { "key": "Surname", "value": "Bacon" }, { "key": "UserIdentifier", "value": "75435451111" } ]

Was this reply helpful? Yes No
Ethan_009 4,838 Moderator on at

Like (1)

Report
Copy link

Link copied!

Hi @ArbeidTest ,

You can try another way to add a new key with Object value and then use any schema within that object.

You can also keep a static key to determine which type of schema it is, later use parseJson for the dynamic key from the trigger, all internal objects/keys will be then easily used.

Hope this helps

Was this reply helpful? Yes No
ArbeidTest 18 on at

Like (0)

Report
Copy link

Link copied!

@Chriddle That worked a treat! I've been struggling with that for a while. No idea what its doing, but i'll work that out at a later date.

I see i can easily loop though select and access the values of key and value.
Many thanks indeed!

Was this reply helpful? Yes No
Chriddle 8,513 Super User 2025 Season 2 on at

Like (0)

Report
Copy link

Link copied!

This expression creates an XML from the data object that you can use xpath to query it.

xpath has some nice features that don't come standard with the usual Power Automate functions.

For example, it can create an array of an object's properties, as shown above.

Was this reply helpful? Yes No
Chriddle 8,513 Super User 2025 Season 2 on at

Like (0)

Report
Copy link

Link copied!

XML requires some different encoded characters than JSON (E.g. <space>), so you may notice differences in object property names.

But that's not too difficult to manage if necessary.

It is encoded with the hexadecimal number of the Ascii table and you can decode it with some replaces:

https://powerusers.microsoft.com/t5/Building-Flows/extracting-data-from-json-with-changing-key-based-on-date/m-p/2467376/highlight/true#M275757

Was this reply helpful? Yes No