Unanswered

Easier way to select a specific value from a JSON array

(0) Share

Report

Posted on by Digital

1,207

I have a SharePoint Note column that holds a JSON string. The string contains survey responses submitted by a Power App.

I want to access values from the JSON string in Flow by giving a key e.g. 'Add value of question number 1 to a variable'

I have achieved this using a single value as a test but it's quite slow and I expect by the time I've added multiple variables.

First I parsed the JSON.

Then I used Filter Array to get the client email address and set a variable to the response.

Screen Shot 2022-10-19 at 12.46.04.png

JSON schema

[
 {
 "fieldType": "Short Text",
 "otherAnswer": "",
 "questionNumber": 1,
 "questionText": "Company Name",
 "response": "ACME"
 },
 {
 "fieldType": "Short Text",
 "otherAnswer": "",
 "questionNumber": 2,
 "questionText": "Account Number",
 "response": "123"
 },
 {
 "fieldType": "Email Address",
 "otherAnswer": "",
 "questionNumber": 4,
 "questionText": "Client Email Address",
 "response": "deano@noemail.com"
 },
 {
 "fieldType": "Short Text",
 "otherAnswer": "",
 "questionNumber": 6,
 "questionText": "Client First Name",
 "response": "Dean"
 }
]

Is there a simpler way to do this other than having multiple filter array actions?

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

Expiscornovus 33,189 Most Valuable Professional on at

Like (4)

Report
Hi @Digital,

Instead of the parse json, filter array actions I would try a Select action instead.

With the key/value mapping you could change a lot. For example you could use the questionText value as a key name.

Below is an example approach.

Key

item()?['questionText']

Value

item()?['response']

Was this reply helpful? Yes No
amanna2 4 on at

Like (0)

Report

Hello @Expiscornovus ,

How can i access these items using key after the select operation?

Was this reply helpful? Yes No
Expiscornovus 33,189 Most Valuable Professional on at

Like (0)

Report
Hi @amanna2,

The Select returns an array. So, you could use an apply to each loop to loop through it and use the columns and values.

Alternatively, you can use an expression to retrieve a specific item of the array.

The first item would be:

first(body('Select'))

The company name of the first item would be

first(body('Select'))['Company Name']

You can also use an index for referencing items. The index always start counting with 0 (first item). So that would be 1 (second item), 2 (third item), 3 (fourth item), etc.

The company name of the third item would be:

body('Select')[2]['Company Name']

Was this reply helpful? Yes No
amanna2 4 on at

Like (0)

Report

Hello @Expiscornovus ,

I tried that. but first() or index is referencing to the first array element. So, i am not able to get the values using the keys.

InvalidTemplate. Unable to process template language expressions in action 'Compose_2' inputs at line '0' and column '0': 'The template language expression 'first(body('Select'))['ABC']' cannot be evaluated because property 'ABC' doesn't exist, available properties are 'XYZ'. Please see https://aka.ms/logicexpressions for usage details.'.

Here XYZ is the first key of the first element.
[
  {
    "XYZ": "492"
  },
  {
    "ABC": "477"
  }
]

i want to know the value of ABC. the array is not fixed. the key values are dynamic. so i need to fetch the data using the key.

Was this reply helpful? Yes No
Expiscornovus 33,189 Most Valuable Professional on at

Like (0)

Report
Hi @amanna2,

The first item doesn't have a ABC property, so the error is correct. In this example the expression would be referencing the second item

body('Select')[1]['ABC']

If you don't know in which item the ABC property is, you can use an apply to each for this as well and reference the ABC property with the item function:

item()?['ABC']

You can also use a Filter Array as well to find the matching item.

Btw, do you mind creating a separate thread for this. It is easier to help you out over there.

Was this reply helpful? Yes No