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