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

[mlamotte]

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.

[gary1]

@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
}

[andreasFe]

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.

[gary1]

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.

[andreasFe]

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-f...
If you gave it a look, too that would be brilliant 🙂

[gary1]

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":