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"
}
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:
@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.
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
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"
}
]