Skip to main content

Notifications

Power Automate - General Discussion
Answered

How to handle different JSON payloads without a schema

(0) ShareShare
ReportReport
Posted on by 18
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"}
  • Chriddle Profile Picture
    Chriddle 7,375 on at
    Re: How to handle different JSON payloads without a schema

    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

     

  • Chriddle Profile Picture
    Chriddle 7,375 on at
    Re: How to handle different JSON payloads without a schema

    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.

  • ArbeidTest Profile Picture
    ArbeidTest 18 on at
    Re: How to handle different JSON payloads without a schema

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

     

  • Ethan_009 Profile Picture
    Ethan_009 4,836 on at
    Re: How to handle different JSON payloads without a schema

    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

  • Verified answer
    Chriddle Profile Picture
    Chriddle 7,375 on at
    Re: How to handle different JSON payloads without a schema

    Something like this?

    Chriddle_0-1705671371892.png

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

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,666

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,996

Leaderboard