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 · Accepted solution]

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

[mlamotte]

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!

[mlamotte]

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