cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JesseW
Frequent Visitor

Filter JSON object to array

Hi guys,

 

I am having trouble converting the below JSON response to strip out the header/footer wrapped around the array. The objective is to filter lines where deductionCategoryId eq 123 or 124 to sum the amount to update a field in Salesforce.

 

So the expected result is:

 

Total Amount eq 250 (123 + 124)

 

{
"deductions": {
"1399443": [
{
"deductionCategoryId": "123",
"amount": 100,
},

{
"deductionCategoryId": "124",
"amount": 150,
},
{
"deductionCategoryId": "125",
"amount": 200,
}
]
},
"payRunId": 3953553
}

 

I've tried using the Apply to each action to then use a condition to return the required array lines. But without stripping out the header and footer the following error is returned:

 

The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@body('GetDeductions')' is of type 'Object'. The result must be a valid array.

 

Any guidance is greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
v-bacao-msft
Community Support
Community Support

Hi @JesseW,

 

I am afraid I don't understand what "1399443" means, whether it is a fixed value or a variable value.

Could you provide more details about this JSON response structure?

The provided JSON data seems to have errors. I need to delete the comma after the value of amount attribute. I guess its type should be integer.

You could consider using Parse JSON action to separate the arrays contained inside, and then use Apply to each to traverse the array to get the corresponding elements.

I would modify JSON response you provided to illustrate the use of parse JSON.

  • First generate the schema using the data you provided:

222.gif

  • Get the array contained inside, array name is 1394443, and then use Apply to each to traverse the array elements to get the element with deductionCategoryId equal to 123 or 124.

Formula reference:

@or(equals(items('Apply_to_each')['deductionCategoryId'], '123'),equals(items('Apply_to_each')['deductionCategoryId'], '124'))

9.PNG

Run history:

10.PNG

Please take a try.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-bacao-msft
Community Support
Community Support

Hi @JesseW,

 

I am afraid I don't understand what "1399443" means, whether it is a fixed value or a variable value.

Could you provide more details about this JSON response structure?

The provided JSON data seems to have errors. I need to delete the comma after the value of amount attribute. I guess its type should be integer.

You could consider using Parse JSON action to separate the arrays contained inside, and then use Apply to each to traverse the array to get the corresponding elements.

I would modify JSON response you provided to illustrate the use of parse JSON.

  • First generate the schema using the data you provided:

222.gif

  • Get the array contained inside, array name is 1394443, and then use Apply to each to traverse the array elements to get the element with deductionCategoryId equal to 123 or 124.

Formula reference:

@or(equals(items('Apply_to_each')['deductionCategoryId'], '123'),equals(items('Apply_to_each')['deductionCategoryId'], '124'))

9.PNG

Run history:

10.PNG

Please take a try.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events happening this month - don't miss out!

 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (2,602)