Skip to main content

Notifications

Community site session details

Community site session details

Session Id : 6ZBXiC7RhzeUKCVYt1cgmJ
Power Automate - Building Flows
Answered

Easier way to select a specific value from a JSON array

Like (0) ShareShare
ReportReport
Posted on 18 Oct 2022 23:55:59 by 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?

  • Expiscornovus Profile Picture
    31,444 Most Valuable Professional on 30 Jun 2024 at 10:56:13
    Re: Easier way to select a specific value from a JSON array

    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.

  • amanna2 Profile Picture
    4 on 30 Jun 2024 at 10:46:08
    Re: Easier way to select a specific value from a JSON array

    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.
  • Expiscornovus Profile Picture
    31,444 Most Valuable Professional on 30 Jun 2024 at 10:31:23
    Re: Easier way to select a specific value from a JSON array

    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']

     

  • amanna2 Profile Picture
    4 on 30 Jun 2024 at 05:55:19
    Re: Easier way to select a specific value from a JSON array

    Hello @Expiscornovus , 

     

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

     

  • Verified answer
    Expiscornovus Profile Picture
    31,444 Most Valuable Professional on 19 Oct 2022 at 09:40:04
    Re: Easier way to select a specific value from a JSON array

    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']

     

    keyvalue_questiontextvalue.png

     

    companyname.png

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

🌸 Community Spring Festival 2025 Challenge 🌸

WIN Power Platform Community Conference 2025 tickets!

Kudos to the March Top 10 Community Stars!

Thanks for all your good work in the Community

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,513 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 65,636 Most Valuable Professional

Leaderboard
Loading started