Unanswered

Extracting array values in json on power automate desktop

(0) Share

Report

Posted on by pdyg_22

Hi I am new to power automate desktop. I need to populate the specific field data onto an excel based on the "ref" key in the json file. However I'm not sure how to just extract the array values for the responses key, and pull out the specific ref value to the right excel field. I am stuck at retrieving the "ref" key corresponding values.

Eg: On excel I have columns for name, dob, marital status, gender, hobbies. On the json file, my ref keys contain these values, which should return the text/date/options values on the excel. If my column is name -> I should retrieve the ref key for name -> and return the text key value pair of John.

Here's the json sample:

{
 "id": "123",
 "responses": [
 {
 "data": {
 "id": "987abc",
 "order": 1,
 "type": "text",
 "ref": "name",
 },
 "type": "text",
 "text": "John"
 },
 {
 "data": {
 "id": "988abc",
 "order": 4,
 "type": "date",
 "ref": "DOB",
 },
 "type": "date",
 "date": "Sat Jan 01 2022"
 },
 {
 "data": {
 "id": "989abc",
 "order": 5,
 "type": "radio",
 "ref": "MaritalStatus",
 },
 "type": "option",
 "option": {
 "id": "xyz123",
 "index": 1,
 "ref": "Single",
 "text": "Single"
 }
 },
 {
 "data": {
 "id": "990abcO",
 "order": 2,
 "type": "checkbox",
 "ref": "Hobbies",
 },
 "type": "options",
 "options": [
 {
 "id": "xyz124",
 "index": 1,
 "ref": "Cardio",
 "text": "Swimming"
 },
 {
 "id": "Hxf7v4JMyy",
 "index": 3,
 "ref": "HIIT",
 "text": "Home workout"
 }
 ]
 },

Appreciate the advice!

Categories:

Power Automate Desktop

I have the same question (0)

All responses (9)

Answers (0)

VJR 7,635 on at

Like (0)

Report

Hi @pdyg_22

Make use of the "Convert JSON to a Custom Object" action.

Was this reply helpful? Yes No
pdyg_22 10 on at

Like (0)

Report

Hi @VJR , thank you. I did that, but as my responses are nested in an array, what I got was just 2 key-value pairs, with the response key having all the array objects.

I want to be able to return the value in an excel sheet when it reference against the "ref"
key like this. How should I do it?

Was this reply helpful? Yes No
VJR 7,635 on at

Like (0)

Report

What does it show when you click on the more button manually? You can access them via PAD too.

Was this reply helpful? Yes No
pdyg_22 10 on at

Like (0)

Report

This is a series of the flow after i click More on each. I have been trying out for days but I just can't seem to extract just the responses array and reference the ref key to excel to get the corresponding text value.

Was this reply helpful? Yes No
VJR 7,635 on at

Like (0)

Report

Are there any changes to the json you shared above? It gave me an error while validating it? Can you share the one that generated the above output in PAD?

Was this reply helpful? Yes No
pdyg_22 10 on at

Like (0)

Report
Oh yes, here you go
{ "id": "123", "responses": [ { "data": { "id": "987abc", "order": 1, "type": "text", "ref": "name" }, "type": "text", "text": "John" }, { "data": { "id": "988abc", "order": 4, "type": "date", "ref": "DOB" }, "type": "date", "date": "Sat Jan 01 2022" }, { "data": { "id": "989abc", "order": 5, "type": "radio", "ref": "MaritalStatus" }, "type": "option", "option": { "id": "xyz123", "index": 1, "ref": "Single", "text": "Single" } }, { "data": { "id": "990abcO", "order": 2, "type": "checkbox", "ref": "Hobbies" }, "type": "options", "options": [ { "id": "xyz124", "index": 1, "ref": "Cardio", "text": "Swimming" }, { "id": "Hxf7v4JMyy", "index": 3, "ref": "HIIT", "text": "Home workout" } ] } ] }

Was this reply helpful? Yes No
pdyg_22 10 on at

Like (0)

Report

Hi @VJR, I have managed to get the data into a list like the screenshot below. Now I'm stuck at getting the values from text/date/option/options value and populating them into an excel according to the ref key (which should correspond to excel column ref)

Was this reply helpful? Yes No
VJR 7,635 on at

Like (0)

Report

For multiple columns datatable to be used.

The below 3 lines will give you the first three Excel columns

%JsonAsCustomObject['responses'][0].text%
%JsonAsCustomObject['responses'][1].date%
%JsonAsCustomObject['responses'][2]['option'].text%

And then since there are multiple hobbies you will need to loop through them.

The first iteration gives Swimming and the second gives Home workout

The idea is to look here.

Was this reply helpful? Yes No
pdyg_22 10 on at

Like (0)

Report

Hi @VJR , thanks a lot for this! Is there a way to not reference based on the index? Because the json file I have given is a sample, but it will contain more json objects, perhaps 30 objects in the response key array.

What I'm thinking of is to first display the results in the display message by accessing the value of the "ref" key and map it to the correct excel field, and create a variable in the name of the excel field. After that, i'll access the text/date/option/options key and assign the value to the excel field variable.

eg:
ref = Name
Excel field = Full Name
text = Henry Mason
So I will map Name to full name and display a message of : Full name : Henry Mason

What I'm having trouble is the applying the index to numerous objects, and for a key which is an array of objects (the options key in the json file), I'm not sure how to display multiple answers to it.

Was this reply helpful? Yes No