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.
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?
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.
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.
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']
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']
WarrenBelz
146,513
Most Valuable Professional
RandyHayes
76,287
Super User 2024 Season 1
Pstork1
65,636
Most Valuable Professional