Skip to main content

Using the UNNEST clause

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

The UNNEST keyword should be followed by the expression returning the nested array. It must be a child of the enclosing array specified in the parent FROM or UNNEST clause:
FROM parent_expression UNNEST child_expression     

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 without an absolute path 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 arrays 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 array alias. However, since both the orders and items arrays 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
   }
]

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!