> For the complete documentation index, see [llms.txt](https://upsolver.gitbook.io/content/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://upsolver.gitbook.io/content/reference-1/sql-commands/jobs/create-job/transformation/insert/unnest.md).
# UNNEST
The `UNNEST` operator is useful for converting nested objects with arrays to flat tables. With Upsolver, `UNNEST` allows you to flatten arrays based on a full `SELECT` statement.
In certain cases, using `UNNEST` may produce a Cartesian product of the column's array values in your result. This means that the values in the flattened arrays will appear in every possible combination within your result.
For example. given the arrays `[ 1, 2, 3 ]` and `[ 4, 5 ]`, their Cartesian product would be `[ (1 , 4), (1, 5), (2, 4), (2, 5), (3, 4), (3, 5) ]`.
If this is the desired result, enable the job option `ALLOW_CARTESIAN_PRODUCTS` to allow the flattening of the arrays.
## Example 1
#### Sample data
```json
{
"values": [ 1, 2, 3 ],
"name": "Oleg",
"id": 123
}
```
#### Sample query
```sql
UNNEST(SELECT data.values[] AS value,
data.name AS name,
data.id AS id
FROM my_data_source)
```
#### Result
Since the data was flattened based on the `values[]` array that contained three values, the result contains three rows from one source event.
| value | name | id |
| ----- | ---- | --- |
| 1 | Oleg | 123 |
| 2 | Oleg | 123 |
| 3 | Oleg | 123 |
## **Example 2**
#### **Sample data**
```json
{
"values": ["apple", "NY"],
"type": ["fruit", "city"]
}
```
#### Sample query with Cartesian product
```sql
UNNEST(SELECT data.values[] AS value,
data.type[] AS type
FROM my_data_source)
```
#### Result
Since the selected arrays are independent without any shared context, the result contains a Cartesian product.
| value | type |
| ----- | ----- |
| apple | fruit |
| NY | fruit |
| apple | city |
| NY | city |
In this case, we can see that it doesn't make sense to pair `NY` with `fruit` and `apple` with `city`, so the `ZIP` function should be used to first combine the arrays into a single context.
#### Sample query with `ZIP`
```sql
UNNEST(SELECT zipped[].value AS value
zipped[].type AS type
FROM my_data_source
LET zipped = ZIP('type,value', data.type[], data.values[]))
```
#### Result
Now we have achieved our desired output for our data.
| value | type |
| ----- | ----- |
| apple | fruit |
| NY | city |
## Example 3
#### Sample data
```json
{
"orders": [{ "order_lines": [ 1, 2, 3 ], "name": "a" },
{ "order_lines": [ 4, 5, 6 ], "name": "b" }]
"refunds": [ 1, 2 ]
}
```
#### Sample query without Cartesian product
```sql
UNNEST(SELECT orders[].name AS name,
orders[].order_lines[] AS line
FROM my_data_source)
```
#### Result
Since there is a natural pairing between `orders[].name` and `orders[].order_lines[]` where each `name` has a corresponding `order_lines` array, using `UNNEST` on this query does not result in a Cartesian product.
#### Sample query with Cartesian product
```sql
UNNEST(SELECT orders[].name AS name,
orders[].order_lines[] AS line,
refunds[] AS refund
FROM my_data_source)
```
#### Result
Since there is no relationship between `orders[]` and `refunds[]` in our data, using `UNNEST` on this query will result in a Cartesian product between the result of pairing `orders[]` and `refunds[]`.
| name | line | refund |
|---|
| a | 1 | 1 |
| a | 2 | 1 |
| a | 3 | 1 |
| b | 4 | 1 |
| b | 5 | 1 |
| b | 6 | 1 |
| a | 1 | 2 |
| a | 2 | 2 |
| a | 3 | 2 |
| b | 4 | 2 |
| b | 5 | 2 |
| b | 6 | 2 |
## Example 4
#### Sample data
```json
{
"orders": [{ "order_lines": [ 1, 2, 3 ],
"name": "a",
"order_date": [ "07/30/2021", "11/27/2021" ]},
{ "order_lines": [ 4, 5, 6 ],
"name": "b",
"order_date": [ "03/21/2021", "09/13/2021" ]}]
}
```
#### Sample query without Cartesian product
```sql
UNNEST(SELECT orders[].name AS name,
orders[].order_lines[] AS line
FROM my_data_source)
```
#### Result
Since there is a natural pairing between `orders[].name` and `orders[].order_lines[]` where each `name` has a corresponding `order_lines` array, using `UNNEST` on this query does not result in a Cartesian product.
#### Sample query with Cartesian product
```sql
UNNEST(SELECT orders[].name AS name,
orders[].order_lines[] AS line,
orders[].order_date[] AS order_date
FROM my_data_source)
```
#### Result
While `orders[].order_lines[]` and `orders[].order_date[]` are both part of `orders[]`, there is no natural pairing between the values in the two respective arrays. As such, using `UNNEST` on this query will result in a Cartesian product.
| name | line | order\_date |
| ---- | ---- | ----------- |
| 1 | a | 07/30/2021 |
| 2 | a | 07/30/2021 |
| 3 | a | 07/30/2021 |
| 1 | a | 11/27/2021 |
| 2 | a | 11/27/2021 |
| 3 | a | 11/27/2021 |
| 4 | b | 03/21/2021 |
| 5 | b | 3/21/2021 |
| 6 | b | 3/21/2021 |
| 4 | b | 09/13/2021 |
| 5 | b | 09/13/2021 |
| 6 | b | 09/13/2021 |
{% hint style="info" %}
To run an `UNNEST` query that produces a Cartesian Product you must set `SKIP_VALIDATIONS = ('ALLOW_CARTESIAN_PRODUCT')`.
{% endhint %}
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:
```
GET https://upsolver.gitbook.io/content/reference-1/sql-commands/jobs/create-job/transformation/insert/unnest.md?ask=&goal=
```
`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.