Using the UNNEST clause - Cloud

Talend Data Shaping Language Reference Guide

Version
Cloud
Language
English (United States)
Product
Talend Cloud
Module
Data Shaping Language
Content
Design and Development

The UNNEST clause is used to unnest a nested collection and join it to its parent collection.

The UNNEST keyword should be followed by the identifier of the nested collection. It must be a child of the enclosing collection specified in the parent FROM or UNNEST clause:
FROM parent_identifier UNNEST child_identifier     

You can use the AS and INDEX keywords in the UNNEST clause, and you can also have several consecutive UNNEST clauses.

When using one or more UNNEST clauses without an alias, all identifiers used in the query are treated as relative to the identifier in the last UNNEST clause or its parent clause if an identifier in the query is not part of the last clause.

For example, with the following input data:
{
   "customers":[
      {
         "name":"John Smith",
         "orders":[
            {
               "id":"abc-12345",
               "items":[
                  {
                     "id":"97516848-jiargn",
                     "quantity":2
                  }
               ]
            }
         ]
      },
      {
         "name":"Jane Doe",
         "orders":[
            {
               "id":"def-12345",
               "items":[
                  {
                     "id":"97516848-kftesn",
                     "quantity":3
                  },
                  {
                     "id":"96946848-metasb",
                     "quantity":1
                  }
               ]
            }
         ]
      }
   ]
}
You can use two UNNEST clauses to get the data in the customers, orders and items collections on the same level:
FROM customers UNNEST orders AS o UNNEST items AS i
SELECT {
	customer_name = name,
	order_id = o.id,
	item_id = i.id,
	item_quantity = quantity
}
Because the identifiers name and quantity are unique, they do not need to be prefixed with the collection alias. However, since both the orders and items collections contain an id element, you need to use the alias to specify which one should be returned. This query returns the following result:
[
   {
      "customer_name":"John Smith",
      "order_id":"abc-12345",
      "item_id":"97516848-jiargn",
      "item_quantity":2
   },
   {
      "customer_name":"Jane Doe",
      "order_id":"def-12345",
      "item_id":"97516848-kftesn",
      "item_quantity":3
   },
   {
      "customer_name":"Jane Doe",
      "order_id":"def-12345",
      "item_id":"96946848-metasb",
      "item_quantity":1
   }
]