cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amit_meld
Regular Visitor

Issues extracting values from an Array

Hi,

 

I am trying to extract values from an array to push them to powerBI to create dashboards, but Power Automate is asking me to use 'apply to each' which is adding 'Score' value which should be always distinct. Below is the example JSON I am calling from an api - 

 

{
"data": [
{
"timestamp": "2021-11-23T02:38:18.000Z",
"score": 78.0,
"sensors": [
{
"comp": "pm25",
"value": 3.0
},
{
"comp": "co2",
"value": 749.0
},
{
"comp": "temp",
"value": 24.940000534057617
},
{
"comp": "humid",
"value": 60.5
},
{
"comp": "voc",
"value": 1932.0
}
],
"indices": [
{
"comp": "co2",
"value": 1.0
},
{
"comp": "temp",
"value": 0.0
},
{
"comp": "pm25",
"value": 0.0
},
{
"comp": "humid",
"value": 1.0
},
{
"comp": "voc",
"value": 2.0
}
]
}
]
}
 
Here, 'sensors' and 'indices' have 'comp' and 'value' as keys but I want to push the values 'temp' : '24.940000534057617'. 
But, while extracting this info using initialise, compose and parse JSON actions I am able to extract only 'Timestamp' and 'Score'. And if I try pushing 'sensor' array info 'comp' and 'value' to powerbi, my http push action is getting iterated through 'apply to each' and 'score' value is adding up in itself.
 
what I want to push - 
 
[
{
"timestamp": 
"2021-11-23T02:38:18.000Z",
"score": 78.0,
"temp": 24.940000534057617,
"humid": 60.5,
"co2": 749.0,
"voc": 1932.0
"pm25": 3.0
}
]
 
How do I do this with Power Automate?
 
Kind regards,
Amit

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
tom_riha
Super User
Super User

Hello @amit_meld ,

Power Automate forces you to use 'Apply to each' as you're trying to access data that's stored in an array in the JSON. If you need only one specific value then you can use expression to navigate the JSON as I wrote in a blog post: https://tomriha.com/how-to-get-a-specific-value-from-a-json-object-in-power-automate/.



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

View solution in original post

Hello @amit_meld ,

you could define the format by just adding the brackets and keys in the action:

[
  {
    "timestamp": outputs(...),
    "score": outputs(...),
    "temp": outputs(...),
    "humid": outputs(...),
    "co2": outputs(...),
    "voc": outputs(...)
    "pm25": outputs(...)
  }
]

 or if the order is not fixed you can use outputs(...) on both sides:

[
  {
    "outputs(...)": outputs(...),
    "outputs(...)": outputs(...),
    "outputs(...)": outputs(...),
    "outputs(...)": outputs(...),
    "outputs(...)": outputs(...),
    "outputs(...)": outputs(...)
    "outputs(...)": outputs(...)
  }
]

 



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

View solution in original post

3 REPLIES 3
tom_riha
Super User
Super User

Hello @amit_meld ,

Power Automate forces you to use 'Apply to each' as you're trying to access data that's stored in an array in the JSON. If you need only one specific value then you can use expression to navigate the JSON as I wrote in a blog post: https://tomriha.com/how-to-get-a-specific-value-from-a-json-object-in-power-automate/.



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

View solution in original post

Hello @tom_riha,

 

Your blog post helped me extract the values I wanted. Thank you so much !

 

Below is the implementation : -

Screen Shot 2021-12-02 at 9.41.25 am.pngScreen Shot 2021-12-02 at 9.42.07 am.pngScreen Shot 2021-12-02 at 9.42.54 am.png

The implementation extracted values separately.

I would like to push this data in following format -

[
{
"timestamp": 
"2021-11-23T02:38:18.000Z",
"score": 78.0,
"temp": 24.940000534057617,
"humid": 60.5,
"co2": 749.0,
"voc": 1932.0
"pm25": 3.0
}
]
 
Do you have a solution on your blogpost regarding how I can combine this extracted data in above given JSON format or any other alternative method to accomplish this? 
 
Kind regards,
Amit
 
 

Hello @amit_meld ,

you could define the format by just adding the brackets and keys in the action:

[
  {
    "timestamp": outputs(...),
    "score": outputs(...),
    "temp": outputs(...),
    "humid": outputs(...),
    "co2": outputs(...),
    "voc": outputs(...)
    "pm25": outputs(...)
  }
]

 or if the order is not fixed you can use outputs(...) on both sides:

[
  {
    "outputs(...)": outputs(...),
    "outputs(...)": outputs(...),
    "outputs(...)": outputs(...),
    "outputs(...)": outputs(...),
    "outputs(...)": outputs(...),
    "outputs(...)": outputs(...)
    "outputs(...)": outputs(...)
  }
]

 



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

View solution in original post

Helpful resources

Announcements
Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,388)