topic Re: Transform query result to nested list / nested json with multiple levels in Workato Pros Discussion Board

Transform query result to nested list / nested json with multiple levels

mlamotte — Tue, 28 May 2024 09:27:32 GMT

Hi,
I need to create a nested json based on a SQL query.

Sample dataset:

Transportorder TO_param1 TO_param2 Ordernumber Order_param1 Order_param2 Orderitem Orderitem_param1

OR-444450	x	y	CO-12345	x	y	CO-12345-1	x
OR-444450	x	y	CO-12345	x	y	CO-12345-2	x
OR-444450	x	y	CO-12346	x	y	CO-12346-1	x
OR-444451	x	y	CO-12400	x	y	CO-12400-1	x
OR-444451	x	y	CO-12400	x	y	CO-12400-2	x
OR-444452	x	y	CO-12405	x	y	CO-12405-1	x

A transportOrder might either contain 1 or more Ordernumbers.
Each Ordernumber might container 1 or more OrderItems.

I need to nest them into a list and use this list for a HTTP POST request in json.

The output should look a bit like this:

[ { "transportorder": "OR-444450", "to_params": { "TO_param1": "x", "TO_param2": "y" }, "orders": [ { "ordernumber": "CO-12345", "order_params": { "Order_param1": "x", "Order_param2": "y" }, "orderitems": [ { "Orderitem": "CO-12345-1", "Orderitem_param1": "x", "Orderitem_param2": "y" }, { "Orderitem": "CO-12345-2", "Orderitem_param1": "x", "Orderitem_param2": "y" } ] }, { "ordernumber": "CO-12346", "order_params": { "Order_param1": "x", "Order_param2": "y" }, "orderitems": [ { "Orderitem": "CO-12346-1", "Orderitem_param1": "x", "Orderitem_param2": "y" } ] } ] }, { "transportorder": "OR-444451", "to_params": { "TO_param1": "x", "TO_param2": "y" }, "orders": [ { "ordernumber": "CO-12400", "order_params": { "Order_param1": "x", "Order_param2": "y" }, "orderitems": [ { "Orderitem": "CO-12400-1", "Orderitem_param1": "x", "Orderitem_param2": "y" }, { "Orderitem": "CO-12400-2", "Orderitem_param1": "x", "Orderitem_param2": "y" } ] } ] }, { "transportorder": "OR-444452", "to_params": { "TO_param1": "x", "TO_param2": "y" }, "orders": [ { "ordernumber": "CO-12405", "order_params": { "Order_param1": "x", "Order_param2": "y" }, "orderitems": [ { "Orderitem": "CO-12405-1", "Orderitem_param1": "x", "Orderitem_param2": "y" } ] } ] } ]

I have tried using 1 list where I enter the formatting using a json sample. But the issue stays the same, that I have to be able to loop over each TransportOrder, get the necessary information added to the list, then loop over the Ordernumbers, get the relevant information into the list, and then go deeper to OrderItems and get the relevant information.

The first issue with a for each is that there might be multiple lines with 1 unique Transportnumber. So I can't use this entire dataset as my source for the For Each. This would mean that I would need to do at least 3 SQL queries. But I just can't seem to fix it.

In python it's much easier, as I am able to use a Dict, check if the TransportNumber is already in the dict, if not append.

Re: Transform query result to nested list / nested json with multiple levels

gary1 — Tue, 28 May 2024 17:32:50 GMT

I would stick with Python or Ruby. Doing this with loops could require a lot of tasks depending on the size of input.

Re: Transform query result to nested list / nested json with multiple levels

mlamotte — Wed, 29 May 2024 06:10:33 GMT

That's what I was afraid of.

Well, for me it doesn't matter, it's that I'm the only one in my team with adequate knowledge of python.

Thanks for the feedback!

Re: Transform query result to nested list / nested json with multiple levels

mlamotte — Wed, 29 May 2024 06:39:11 GMT

I'm having some difficulties with the Python snippet in Workato.
How could I pass the entire query result as an array into Python?

Re: Transform query result to nested list / nested json with multiple levels

andreasFe — Thu, 10 Apr 2025 15:45:48 GMT

I have the very same question. Passing an entire query result as input to Python/Ruby doesn't seem possible out of the box?
@gary1- any idea how that could be solved?

Re: Transform query result to nested list / nested json with multiple levels

gary1 — Thu, 10 Apr 2025 21:25:58 GMT

@mlamotte this is an old post but I figured I would give it a shot anyways. You've probably already solved this a long time ago, but here is the Ruby script to merge your data. The casing might be a bit off, but it works.

@andreasFe I'm not exactly sure what you mean, but if you need to pass a list/array or object into an Ruby/JS/Python action, you can turn your input field to formula mode and simply drop in the data pill.

## THE MERGINATOR ## transportOrders = input["orders"] to_group = transportOrders.group_by{ | t | t["transportOrder"]}.map{ | to_key, to_array | ## beginning of object { "transportorder": to_key, "to_params": { "TO_param1": to_array[0]["to_param1"], ## this is lazy but works "TO_param2": to_array[0]["to_param2"] ## also lazy }, "orders": to_array.group_by{|to|to["orderNumber"]}.map{ | order_key, order_array | { "ordernumber": order_key, "order_params": { "Order_param1": order_array.find{|order| order["orderNumber"] == order_key}&.dig("order_param1"), "Order_param2": order_array.find{|order| order["orderNumber"] == order_key}&.dig("order_param2") }, "orderitems": order_array.group_by{|order| order["orderItem"]}.map{ | item_key, item_array | { "Orderitem": item_key, "Orderitem_param1": item_array.find{|item| item["orderItem"] == item_key}&.dig("orderItem_param1"), "Orderitem_param2": item_array.find{|item| item["orderItem"] == item_key}&.dig("orderItem_param2") } } } } } ## end of object } { to_group: to_group }

Re: Transform query result to nested list / nested json with multiple levels

andreasFe — Fri, 11 Apr 2025 08:23:53 GMT

Our question is why it's not possible to pass the "entire query result" / the rows of an SQL query into a python/ruby action.

neither for list nor object inputs the "rows" pill is selectable.

Re: Transform query result to nested list / nested json with multiple levels

gary1 — Fri, 11 Apr 2025 15:47:56 GMT

Ah, this is a silly Workato thing. Define your input field as a string and set it to formula mode. This will allow you to pass an object or array data pill. In your example, you have your inputs defined as a list and object, and while that may seem intuitive it will not work the way you expect it to.

Re: Transform query result to nested list / nested json with multiple levels

andreasFe — Fri, 11 Apr 2025 16:01:54 GMT

Thanks for the reply!
I tried List/Object as String doesn't work either.
I (just like mlamotte) am not trying to pass a single value (equipment_id, article_number, ..) - but the whole rows-result-set. I don't think i can define that its a single "string" of rows coming back from the databse.. or am I missing something?

In general I'm trying to work around another issue that I posted here: https://systematic.workato.com/t5/workato-pros-discussion-board/pass-through-json-quot-string-quot-from-database-query/m-p/9615#M3916.
If you gave it a look, too that would be brilliant 🙂

Re: Transform query result to nested list / nested json with multiple levels

gary1 — Fri, 11 Apr 2025 16:19:43 GMT

I know exactly what you mean, but maybe my explanation wasn't clear.

The "a_string_field" field is a string field set to formula mode. Once it's in formula mode, I'm able to pass the "blah blah blah" list as input.

And just to really drive this home, here are the settings for "a_string_field":

Re: Transform query result to nested list / nested json with multiple levels

andreasFe — Fri, 11 Apr 2025 18:10:05 GMT

Yep - thanks that was the simple catch.