topic Re: Transform and Filter Json data in Workato Pros Discussion Board

Transform and Filter Json data

bala-s — Mon, 26 Apr 2021 22:14:19 GMT

Hi Team,

I am having below payload as input and in the output im expecting only the Sales data of (Target >= 20)

Input Payload:

===========

{

"EmployeeDetails": [{

"EmpID": 123,

"Records": [{

"id": "S1",

"Department": "Sales",

"Target": "20"

{

"id": "S3",

"Department": "Sales",

"Target": "20"

{

"id": "S4",

"Department": "Sales",

"Target": "10"

{

"id": "L1",

"Department": "Leaves",

"Targets": "1"

}

]

{

"EmpID": 124,

"Records": [{

"id": "S2",

"Department": "Sales",

"Target": "25"

{

"id": "L1",

"Department": "Leaves",

"Targets": "3"

}

]

}

]

}

Expected Output: ("Records.Department == Sales" && "Records.Target >= 20")

==============

{

"EmployeeDetails": [{

"EmpID": 123,

"Records": [{

"id": "S1",

"Department": "Sales",

"Target": "20"

{

"id": "S3",

"Department": "Sales",

"Target": "20"

{

"id": "S4",

"Department": "Sales",

"Target": "10"

}

]

{

"EmpID": 124,

"Records": [{

"id": "S2",

"Department": "Sales",

"Target": "25"

}

]

}

]

}

Recipe: https://app.workato.com/recipes/1521392?st=5d5a86

Is anyone gone through similar kind of use case. please provide your solutions on this.

Re: Transform and Filter Json data

deepak23shastri — Mon, 26 Apr 2021 22:35:20 GMT

Hi, you can create a collection off your input and write a simple select query to extract the data. its pretty simple setup. You can find all the info about creating and querying in collection here

Re: Transform and Filter Json data

vijay-narsimha — Mon, 26 Apr 2021 22:50:02 GMT

Hi Bala,

Can you try this logic this might help you.

Re: Transform and Filter Json data

bala-s — Mon, 26 Apr 2021 23:12:28 GMT

Hi Deepak,

Thanks for your response, as the payload having nested elements.

if we create a collection on EmployeeDetails, we store only array of EmpID

Similary for Records, we store only record data

Here the relation is missing.

Otherwise we can directly go for for each and achieve this

Re: Transform and Filter Json data

bala-s — Mon, 26 Apr 2021 23:13:46 GMT

Hi Vijay,

Thanks for your response.

Could you please provide the complete solution.

Re: Transform and Filter Json data

vijay-narsimha — Mon, 26 Apr 2021 23:24:19 GMT

Hi Bala, Can you share the common data model as well. I will share the complete recipe link

Re: Transform and Filter Json data

bala-s — Mon, 26 Apr 2021 23:26:00 GMT

you can generate with payload data

Re: Transform and Filter Json data

vijay-narsimha — Tue, 27 Apr 2021 00:28:34 GMT

Please find the link with the complete solution below

https://app.workato.com/recipes/1521543?st=a927cd

Re: Transform and Filter Json data

bala-s — Tue, 27 Apr 2021 00:44:16 GMT

Thanks for your solution. Its working

Just made a change in this (removed step7 (For-each))

https://app.workato.com/recipes/1521682?st=8482cd

Re: Transform and Filter Json data

dianaa — Tue, 27 Apr 2021 12:06:51 GMT

Hi,

Send the request jeson to a callable recipe.

Make manipulation with collection on the data and return the response.

Recipes working with jeson object so you will get back json from the recipe.

Build the request and the response in the same structure as your json.

good luck,

Diana

Re: Transform and Filter Json data

bala-s — Tue, 27 Apr 2021 17:38:16 GMT

Solution with RubyScript:

https://app.workato.com/recipes/1521392?st=5d5a86