topic Handling Arbitrary Object Names in JSON response in Workato Pros Discussion Board

Handling Arbitrary Object Names in JSON response

jblanchett — Thu, 29 Apr 2021 02:49:22 GMT

Hi All - has anyone found a way to handle arbitrary object names in JSON responses? (see format 1 below)

I'm working with a custom app internally that breaks out transaction amounts by period for a given transaction. Each period is returned as a JSON object with periods as keys (format YYYYMM), which can be arbitrary depending on what periods the transaction contains.

Format 1: JSON object with arbitrary object names. As far as I know this won't work with Workato unless each unique period is defined in the JSON response schema:

{

"summary": {

"202011": {

"period": "202011",

"finance_total": 0,

"order_payments_total": 458,

"seller_fees_total": -45.8,

"seller_fee_discounts_total": 0,

"total": 412.2,

"count": 2

}

"meta": {

"status": 200

}

Format 2: changing format 1 to a format that I know is compatible with Workato (JSON array w/o named keys).

{

"meta": {

"status": 200

"category_by_period": [

{

"period": "202011",

"finance_total": 0,

"order_payments_total": 458,

"seller_fees_total": -45.8,

"seller_fee_discounts_total": 0,

"total": 412.2,

"count": 2

}

]

}

Re: Handling Arbitrary Object Names in JSON response

asiya — Thu, 29 Apr 2021 04:16:47 GMT

I have used the Ruby connector in the past to transform JSON objects into something that works better with Workato, so you could write a snippet that would take the objects in format 1 and turn the arbitrarily named objects into an array - you wouldn't lose data here as long as the name of the object matches what's in the period value. You could also just use the Ruby connector to turn format 1 into format 2 and that saves you redesigning your custom app. I can send you an example snippet if you like.

I've also had success in the past with just accessing JSON values directly in formula mode - so even if you don't have a datapill for the finance_total in format 1, you should still be able to access it by using <summary datapill>['202011']['finance_total'] in formula mode.

Re: Handling Arbitrary Object Names in JSON response

jeff-sutton — Thu, 29 Apr 2021 04:54:21 GMT

What Asiya said 🙂

Re: Handling Arbitrary Object Names in JSON response

mroldanvega — Thu, 29 Apr 2021 07:29:46 GMT

I have experience with a similar approach to what Asiya Kadri is suggesting.

In my case, I used a Ruby step to only extract the keys and put them into a list. Then I used this list in a '.pluck' formula to get the data/value from the original object. I guess the approach will depend of the level and complexity of the JSON.

Re: Handling Arbitrary Object Names in JSON response

jblanchett — Sat, 01 May 2021 02:16:48 GMT

This is great, thanks everyone! @asiya

if you have a ruby snippet you could share would be great.