How to filter a JSON object with dynamic keys with Power Automate
Вставка
- Опубліковано 5 вер 2024
- This video explains how to filter a JSON object in #powerautomate which has dynamic key value pairs. Because the filter array action expects you to know the key name before you can filter on it, this can be difficult to achieve.
This technique is one that I haven't used before so I thought it was interesting to share. The original forum post is here:
powerusers.mic...
Hey, thank you for sharing this solution, was really struggling with same problem!(Property names for me were dynamic dataverse IDs which I needed to updated, and keys were True\false values defining if this ID needs an update)
For anyone who would come across this solution I`ll post all formulas down here:
createArray(body('Post_Card')['data'])
split(trim(body('Create_CSV_table')),decodeUriComponent('%0D%0A'))
range(0,length(split(outputs('Split_CSV')[0],',')))
split(outputs('Split_CSV')[0],',')[item()]
split(outputs('Split_CSV')[1],',')[item()]
Thank you very much Sir.
You have always been one of the most of my favorite channels when I seek solutions for my concerns. I am lucky to know your channel.
That’s very kind of you, thank you! 🙏
100% like everyone else has said, you are the man sir!
Subbing now
Hey, thank you for sharing this! I was scratching my head for a while trying to figure this out, and this is the EXACT solution I needed. I'm needed this exact solution to process the key-value pairs from the "Get changes for an item or a file" action. Thank you! You're very generous.
You're very welcome!
Another great vid Paulie, thank you! Will be adding this solution to my toolbox for future use.
Thank you Jeremy! This was a tricky one!
Exactly what i'm looking for, thanks for sharing Paul :)
Great to hear!
Brilliant solution! Thank you very much for sharing!
Very slick way of interfacing with this. I hit a snag implementing it since I wanted to expand this to all general JSON objects to key pairs. If any of the values have a comma in then, as you can imagine this craps the bed. Challenge you to find how to escape those inline text commas?
I will take that challenge 😂 send me a text file containing your example JSON please?
@@PaulieM Thanks for entertaining this. I've scrubbed some of the data here but just add some commas into the value pairs or even quotes. Looking for a robust solution that can handle the dynamic nature of the key values in addition to handling escape characters, additional commas, or inline quotes or even when the object comes back with number values not in quotes within the object for the value. This is a sample JSON object I get back from an SQL that I loop through and parse this one item out.
It starts going off the rails when trying to split on the comma as inline commas in the value can't tell the differences from the ones at the end and that starts to cascade into other issues with quotes in the values later as well.
[
{
"@odata.etag": "",
"ItemInternalId": "c073da9d-c751-4342-ae25-c9b4c4854b07",
"Id": 1179773000021739000,
"Opportunity Name": "XYZ",
"Procurement Status": "ABC",
"Opportunity Summary": "This requirement shall include all technical, management, and quality control processes required to enable the Agency to plan, design, configure, implement, deliver, operate, and enhance More XYZ. he scope of the requirement encompasses the major objective areas listed below. For related background, see the Attachments to the SOO.",
"Contract Vehicle": "[\"UNDETERMINED\"]",
"Capability Alignment": "[\"Design\",\"Development\",\"Testing\",\"Engineering\",\"Integration\"]",
"Solicitation Number": 123,
"No. of Expected Awards": "Multiple - Number Unknown",
"Value (TCV $)": 1,
"Duration": 60,
"Value (OCV$)": 1,
"Competition Type _x002d_ Socio_x002d_Economic _x0026_ Size": "[\"Undetermined\"]",
"pWin": 10,
"Opportunity Type": "New Contract - New Customer",
"Time Phase Factored Revenue": "0",
"Factored OCV": "0.1",
"Last Activity Time": "2024-08-23T14:27:38",
"Unique Identifier": "998",
"Opportunity Description": "This Statement of Objectives (SOO) for a multiple award Blanket Purchase Agreement (BPA) outlines the broad objectives of the AGENCY to improve business workflows through utilizing “Low-Code/No-Code” Platforms to enhance the delivery of modernized applications. As it relates to this requirement, Low-Code/No-Code Platforms are a software development environment that allows users to create applications without having to write code. This can be done by using a graphical user interface (GUI) to drag and drop components, or by using a visual programming language. This definition is Platform agnostic. By leveraging the integrated platforms’ capabilities, AGENCY can achieve greater efficiency, agility, and innovation."
}
]
Saved the day, Many thanks!
Didn't work for me unfortunately. It looks like my outputs contain nested arrays that are also split with comma so not sure how to get around that!
woah impressive !!!
Best hack I have ever seen
Hey, nice video! How could you do to go backwards? I mean, going from that Array to a dynamic key value pairs object? Thanks a lot!
Do you have an example input and desired output you can provide?
Great Tutorial, wish it helped with my situation, but unfortunately there are commas inside of the dynamic keys and this throws the split off.
Would be interested in having a look at your sample.
If i have output of parse jason like this {
data:
{
“data1”:”one”,
“data2”:””,
“data3”:”data3”
}
}
now you can see data two value is just empty “”
so i want to remove data two and want output like this
{
data:
{
“data1”:”one”,
“data3”:”data3”
}
}
how to achieve this for you have any idea?
in filter array use string('') remaining all same
Will this work with complex JSON that includes Objects and Arrays with objects? I am going to give it a go, but essentially depending on the config of said item, the output of JSON Objects and arrays change continuously. I am trying to work out a method to dynamically update the outputs for all values that do not contain null into a word document. Essentially the end goal is creating documentation for the config itself. If your interested to give it a go, let me know and I can send you a sample of some data I have, but essentially the output will come directly out of a HTTP REST API.
Use the get in touch form on my blog and I will take a look
Hello, thank you for sharing. Do you have any suggestions on the following scenario: my json has comments data which mesns that I could have a comma in the text. The comma split doesn't work in this scenario.
It’s always tricky in this case. I’d be interested to take a look at it with you.
@@PaulieM Thank you for responding. How would you like to connect ? I'm currently working on a way to do this using xpath, but having a hard time with getting the values (I've got the keys correctly). Your suggestions are much appreciated!
@@matiaskezman1 please fill in the contact form on my blog.
Very nice hack.