topic Re: Retrieve a specific row from JSON to create a new row in Workato Pros Discussion Board

Retrieve a specific row from JSON to create a new row

tshoemaker — Thu, 27 Jan 2022 01:45:57 GMT

I did a search of a database that returns about 10 rows of data into JSON... It seems that the only way that I can now search inside that dataset is to do a loop and an IF inside that loop to find the record I want.

What I WANT to do is to select one of the rows (create a new json with one row?) so that I can use the datapills in following steps.

Re: Retrieve a specific row from JSON to create a new row

jeff-allen — Thu, 27 Jan 2022 01:54:18 GMT

It sound like you may want to look at the .WHERE and .PLUCK options available in formulas. This can be used to select specific rows from a list.

Re: Retrieve a specific row from JSON to create a new row

tshoemaker — Thu, 27 Jan 2022 02:53:59 GMT

Thank you Jeff Allen. This really helped. I was able to use the .where.pluck to grab the item I wanted and set a variable.

Re: Retrieve a specific row from JSON to create a new row

rituraj — Thu, 27 Jan 2022 08:21:52 GMT

another option might be to look at collections. Collections act like in memory DB's where you can perform set operations and most normal sql operations

Re: Retrieve a specific row from JSON to create a new row

gwilkinson — Thu, 27 Jan 2022 20:45:22 GMT

I love Collections and the capabilities it affords. I'm very much looking forward to the persistence mechanism that is an enhancement on the Workato roadmap, which will allow Collections to persist and be available across recipes/Jobs and not just within a single Job.

Re: Retrieve a specific row from JSON to create a new row

jeff-allen — Thu, 27 Jan 2022 23:26:52 GMT

Any chance they mentioned increasing the row limits in Collections on the roadmap you are referring to? They are great when the dataset is smaller.

Re: Retrieve a specific row from JSON to create a new row

gwilkinson — Fri, 28 Jan 2022 00:36:44 GMT

I believe Workato is increasing the row limits in Collections as part of the persistence enhancement. If I am not mistaken, even the row limit for the current (non-persistent) Collections was increased. Perhaps someone from Workato Product can comment/clarify (@Deven Maru).