Transform and Filter Json data

[bala-s]

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.

[deepak23shastri]

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

[bala-s]

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

[vijay-narsimha]

Hi Bala,

Can you try this logic this might help you.

[bala-s]

Hi Vijay,

Thanks for your response.

Could you please provide the complete solution.