Extracting array values in json on power automate desktop

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

[VJR]

Hi @pdyg_22 

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

[pdyg_22]

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?

[VJR]

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

[pdyg_22]

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. 

[VJR]

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?

[pdyg_22]

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

[pdyg_22]

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)

[VJR]

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.

[pdyg_22]

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.