vtlim commented on code in PR #13736: URL: https://github.com/apache/druid/pull/13736#discussion_r1097954178
########## docs/querying/sql-functions.md: ########## @@ -1357,6 +1357,13 @@ Truncates a numerical expression to a specific number of decimal digits. Parses `expr` into a `COMPLEX<json>` object. This operator deserializes JSON values when processing them, translating stringified JSON into a nested structure. If the input is not a `VARCHAR` or it is invalid JSON, this function will result in a `NULL` value. +## UNNEST + +`UNNEST(source)) as UNNESTED (target)` + +Unnests a source column that includes arrays into a target column. + +For more information, see [UNNEST](./sql.md#unnest) Review Comment: ```suggestion For more information, see [UNNEST](./sql.md#unnest). ``` ########## docs/querying/sql.md: ########## @@ -82,6 +83,27 @@ FROM clause, metadata tables are not considered datasources. They exist only in For more information about table, lookup, query, and join datasources, refer to the [Datasources](datasource.md) documentation. +## UNNEST + +The UNNEST clause unnests values arrays. It's the SQL equivalent to the [unnest datasource](./datasource.md#unnest). The source for UNNEST can be an array or an input that's been transformed into an array, such as with helper functions like MV_TO_ARRAY or ARRAY. Review Comment: Do you want to remove the note that unnest is native only in querying/datasource.md?  ########## docs/querying/sql.md: ########## @@ -82,6 +83,27 @@ FROM clause, metadata tables are not considered datasources. They exist only in For more information about table, lookup, query, and join datasources, refer to the [Datasources](datasource.md) documentation. +## UNNEST + +The UNNEST clause unnests values arrays. It's the SQL equivalent to the [unnest datasource](./datasource.md#unnest). The source for UNNEST can be an array or an input that's been transformed into an array, such as with helper functions like MV_TO_ARRAY or ARRAY. Review Comment: Should "values arrays" be "values' arrays" or "array values"? ########## docs/querying/sql.md: ########## @@ -82,6 +83,27 @@ FROM clause, metadata tables are not considered datasources. They exist only in For more information about table, lookup, query, and join datasources, refer to the [Datasources](datasource.md) documentation. +## UNNEST + +The UNNEST clause unnests values arrays. It's the SQL equivalent to the [unnest datasource](./datasource.md#unnest). The source for UNNEST can be an array or an input that's been transformed into an array, such as with helper functions like MV_TO_ARRAY or ARRAY. + +The following is the general syntax for UNNEST, specifically a query that returns the column that gets unnested: + +```sql +SELECT target_column FROM datasource, UNNEST(source_expression) AS table_alias_name(column_alias_name) +``` + +* The `datasource` for UNNEST can be any Druid datasource, such as the following: + * A table, such as `FROM a_table` Review Comment: ```suggestion * A table, such as `FROM a_table`. ``` ########## docs/querying/sql.md: ########## @@ -82,6 +83,27 @@ FROM clause, metadata tables are not considered datasources. They exist only in For more information about table, lookup, query, and join datasources, refer to the [Datasources](datasource.md) documentation. +## UNNEST + +The UNNEST clause unnests values arrays. It's the SQL equivalent to the [unnest datasource](./datasource.md#unnest). The source for UNNEST can be an array or an input that's been transformed into an array, such as with helper functions like MV_TO_ARRAY or ARRAY. + +The following is the general syntax for UNNEST, specifically a query that returns the column that gets unnested: + +```sql +SELECT target_column FROM datasource, UNNEST(source_expression) AS table_alias_name(column_alias_name) +``` + +* The `datasource` for UNNEST can be any Druid datasource, such as the following: + * A table, such as `FROM a_table` + * A subset of a table based on a query, such as `FROM (SELECT columnA,columnB,columnC from a_table)`, a filter, or a JOIN. Review Comment: Suggest moving the example after the list to not interrupt the flow. ```suggestion * A subset of a table based on a query, a filter, or a JOIN. For example, `FROM (SELECT columnA, columnB, columnC from a_table)`. ``` ########## docs/querying/sql.md: ########## @@ -82,6 +83,27 @@ FROM clause, metadata tables are not considered datasources. They exist only in For more information about table, lookup, query, and join datasources, refer to the [Datasources](datasource.md) documentation. +## UNNEST + +The UNNEST clause unnests values arrays. It's the SQL equivalent to the [unnest datasource](./datasource.md#unnest). The source for UNNEST can be an array or an input that's been transformed into an array, such as with helper functions like MV_TO_ARRAY or ARRAY. + +The following is the general syntax for UNNEST, specifically a query that returns the column that gets unnested: + +```sql +SELECT target_column FROM datasource, UNNEST(source_expression) AS table_alias_name(column_alias_name) +``` + +* The `datasource` for UNNEST can be any Druid datasource, such as the following: + * A table, such as `FROM a_table` + * A subset of a table based on a query, such as `FROM (SELECT columnA,columnB,columnC from a_table)`, a filter, or a JOIN. + * An inline array, which is treated as the `datasource` and the `source`, such as `FROM UNNEST(ARRAY[1,2,3])` Review Comment: ```suggestion * An inline array, which is treated as the `datasource` and the `source`, such as `FROM UNNEST(ARRAY[1,2,3])`. ``` ########## docs/querying/sql.md: ########## @@ -82,6 +83,27 @@ FROM clause, metadata tables are not considered datasources. They exist only in For more information about table, lookup, query, and join datasources, refer to the [Datasources](datasource.md) documentation. +## UNNEST + +The UNNEST clause unnests values arrays. It's the SQL equivalent to the [unnest datasource](./datasource.md#unnest). The source for UNNEST can be an array or an input that's been transformed into an array, such as with helper functions like MV_TO_ARRAY or ARRAY. + +The following is the general syntax for UNNEST, specifically a query that returns the column that gets unnested: + +```sql +SELECT target_column FROM datasource, UNNEST(source_expression) AS table_alias_name(column_alias_name) +``` + +* The `datasource` for UNNEST can be any Druid datasource, such as the following: + * A table, such as `FROM a_table` + * A subset of a table based on a query, such as `FROM (SELECT columnA,columnB,columnC from a_table)`, a filter, or a JOIN. + * An inline array, which is treated as the `datasource` and the `source`, such as `FROM UNNEST(ARRAY[1,2,3])` +* The `source_expression` for the UNNEST function must be an array and can come from any expression. If the dimension you are unnesting is a multi-value dimension, you have to specify `MV_TO_ARRAY(dimension)` to convert it to an implicit ARRAY<> type. You can also specify any expression that has an SQL array datatype. For example, use `ARRAY[dim1,dim2]` if you want to make an array out of 2 dimensions or `ARRAY_CONCAT(dim1,dim2)` if you have to concatenate two multi-value dimensions. +* The `AS table_alias_name(column_alias_name)` clause is not required but is highly recommended. Use it to specify the output, which can be an existing column or a new one. If you don't provide this, Druid uses an nondescriptive name, such as `EXPR$0`. Review Comment: ```suggestion * The `AS table_alias_name(column_alias_name)` clause is not required but is highly recommended. Use it to specify the output, which can be an existing column or a new one. If you don't provide this, Druid uses a nondescriptive name, such as `EXPR$0`. ``` ########## docs/querying/sql.md: ########## @@ -82,6 +83,27 @@ FROM clause, metadata tables are not considered datasources. They exist only in For more information about table, lookup, query, and join datasources, refer to the [Datasources](datasource.md) documentation. +## UNNEST + +The UNNEST clause unnests values arrays. It's the SQL equivalent to the [unnest datasource](./datasource.md#unnest). The source for UNNEST can be an array or an input that's been transformed into an array, such as with helper functions like MV_TO_ARRAY or ARRAY. + +The following is the general syntax for UNNEST, specifically a query that returns the column that gets unnested: + +```sql +SELECT target_column FROM datasource, UNNEST(source_expression) AS table_alias_name(column_alias_name) +``` + +* The `datasource` for UNNEST can be any Druid datasource, such as the following: + * A table, such as `FROM a_table` + * A subset of a table based on a query, such as `FROM (SELECT columnA,columnB,columnC from a_table)`, a filter, or a JOIN. + * An inline array, which is treated as the `datasource` and the `source`, such as `FROM UNNEST(ARRAY[1,2,3])` +* The `source_expression` for the UNNEST function must be an array and can come from any expression. If the dimension you are unnesting is a multi-value dimension, you have to specify `MV_TO_ARRAY(dimension)` to convert it to an implicit ARRAY<> type. You can also specify any expression that has an SQL array datatype. For example, use `ARRAY[dim1,dim2]` if you want to make an array out of 2 dimensions or `ARRAY_CONCAT(dim1,dim2)` if you have to concatenate two multi-value dimensions. Review Comment: Wonder if this might be easier to follow with the examples as sub-bullets? ```suggestion * The `source_expression` for the UNNEST function must be an array and can come from any expression. If the dimension you are unnesting is a multi-value dimension, you have to specify `MV_TO_ARRAY(dimension)` to convert it to an implicit ARRAY<> type. You can also specify any expression that has an SQL array datatype. For example, you can call UNNEST on the following expressions: * `ARRAY[dim1,dim2]` to make an array out of two dimensions. * `ARRAY_CONCAT(dim1,dim2)` to concatenate two multi-value dimensions. ``` ########## docs/tutorials/tutorial-unnest-arrays.md: ########## @@ -25,9 +25,7 @@ title: "Tutorial: Unnest data in a column" > If you're looking for information about how to unnest `COMPLEX<json>` > columns, see [Nested columns](../querying/nested-columns.md). -> The unnest datasource is currently only available as part of a native query. - -This tutorial demonstrates how to use the unnest datasource to unnest a column that has data stored in arrays. For example, if you have a column named `dim3` with values like `[a,b]` or `[c,d,f]`, the unnest datasource can output the data to a new column with individual rows that contain single values like `a` and `b`. When doing this, be mindful of the following: +This tutorial demonstrates how to use the UNNEST function (SQL) or the unnest datasource (native) to unnest multi-value dimensions, data stored in an array. For example, if you have a column named `dim3` with values like `[a,b]` or `[c,d,f]`, you can unnest this data and use it in a subsequent query or output the data to a new column. This new column would have individual rows that contain single values like `a` and `b`. When doing this, be mindful of the following: Review Comment: ```suggestion This tutorial demonstrates how to use the UNNEST function (SQL) or the unnest datasource (native) to unnest multi-value dimensions and data stored in an array. For example, if you have a column named `dim3` with values like `[a,b]` or `[c,d,f]`, you can unnest this data and use it in a subsequent query or output the data to a new column. This new column would have individual rows that contain single values like `a` and `b`. When doing this, be mindful of the following: ``` ########## docs/tutorials/tutorial-unnest-arrays.md: ########## @@ -36,14 +34,14 @@ You can use the Druid console or API to unnest data. To start though, you may w ## Prerequisites -You need a Druid cluster, such as the [micro-quickstart](./index.md). The cluster does not need any existing datasources. You'll load a basic one as part of this tutorial. +You need a Druid cluster, such as the [quickstart](./index.md). The cluster does not need any existing datasources. You'll load a basic one as part of this tutorial. ## Load data with nested values The data you're ingesting contains a handful of rows that resemble the following: ``` -t:2000-01-01, m1:1.0, m2:1.0, dim1:, dim2:[a], dim3:[a,b] +t:2000-01-01, m1:1.0, m2:1.0, dim1:, dim2:[a], dim3:[a,b], dim4[x,y] Review Comment: ```suggestion t:2000-01-01, m1:1.0, m2:1.0, dim1:, dim2:[a], dim3:[a,b], dim4:[x,y], dim5:[a,b] ``` ########## docs/querying/sql.md: ########## @@ -82,6 +83,27 @@ FROM clause, metadata tables are not considered datasources. They exist only in For more information about table, lookup, query, and join datasources, refer to the [Datasources](datasource.md) documentation. +## UNNEST + +The UNNEST clause unnests values arrays. It's the SQL equivalent to the [unnest datasource](./datasource.md#unnest). The source for UNNEST can be an array or an input that's been transformed into an array, such as with helper functions like MV_TO_ARRAY or ARRAY. + +The following is the general syntax for UNNEST, specifically a query that returns the column that gets unnested: + +```sql +SELECT target_column FROM datasource, UNNEST(source_expression) AS table_alias_name(column_alias_name) +``` + +* The `datasource` for UNNEST can be any Druid datasource, such as the following: + * A table, such as `FROM a_table` + * A subset of a table based on a query, such as `FROM (SELECT columnA,columnB,columnC from a_table)`, a filter, or a JOIN. + * An inline array, which is treated as the `datasource` and the `source`, such as `FROM UNNEST(ARRAY[1,2,3])` Review Comment: Not sure if missing something but what does the `source` refer to? ########## docs/tutorials/tutorial-unnest-arrays.md: ########## @@ -140,9 +142,92 @@ Now that the data is loaded, run the following query: SELECT * FROM nested_data ``` -In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries, such as Scan. +In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries against the unnested records. Depending on the type of queries you write, see either [Unnest using SQL queries](#unnest-using-sql-queries) or [Unnest using native queries](#unnest-using-native-queries). + +## Unnest using SQL queries + +The following is the general syntax for UNNEST: + +```sql +SELECT target_column FROM datasource, UNNEST(source) AS table_alias_name(column_alias_name) +``` + +For more information about the syntax, see [UNNEST](../querying/sql.md#unnest). + +### Unnest inline array + +The following query returns a column that unnests the array `[1,2,3]` that is provided inline: + +```sql +SELECT * FROM UNNEST(ARRAY[1,2,3]) +``` + +If you unnest that same inline array while using a table as the datasource, Druid treats this as a JOIN between a left datasource and a constant datasource. For example: -## Unnest a single column +```sql +SELECT longs FROM nested_data, UNNEST(ARRAY[1,2,3]) as UNNESTED (longs)" Review Comment: UNNESTED looks like a function; suggest using something like `table_alias_name(longs)` or `example_table(longs)` to show that this is just a variable table name -- applies to seven examples in this doc ########## docs/querying/sql.md: ########## @@ -82,6 +83,27 @@ FROM clause, metadata tables are not considered datasources. They exist only in For more information about table, lookup, query, and join datasources, refer to the [Datasources](datasource.md) documentation. +## UNNEST + +The UNNEST clause unnests values arrays. It's the SQL equivalent to the [unnest datasource](./datasource.md#unnest). The source for UNNEST can be an array or an input that's been transformed into an array, such as with helper functions like MV_TO_ARRAY or ARRAY. + +The following is the general syntax for UNNEST, specifically a query that returns the column that gets unnested: + +```sql +SELECT target_column FROM datasource, UNNEST(source_expression) AS table_alias_name(column_alias_name) +``` + +* The `datasource` for UNNEST can be any Druid datasource, such as the following: + * A table, such as `FROM a_table` + * A subset of a table based on a query, such as `FROM (SELECT columnA,columnB,columnC from a_table)`, a filter, or a JOIN. + * An inline array, which is treated as the `datasource` and the `source`, such as `FROM UNNEST(ARRAY[1,2,3])` +* The `source_expression` for the UNNEST function must be an array and can come from any expression. If the dimension you are unnesting is a multi-value dimension, you have to specify `MV_TO_ARRAY(dimension)` to convert it to an implicit ARRAY<> type. You can also specify any expression that has an SQL array datatype. For example, use `ARRAY[dim1,dim2]` if you want to make an array out of 2 dimensions or `ARRAY_CONCAT(dim1,dim2)` if you have to concatenate two multi-value dimensions. +* The `AS table_alias_name(column_alias_name)` clause is not required but is highly recommended. Use it to specify the output, which can be an existing column or a new one. If you don't provide this, Druid uses an nondescriptive name, such as `EXPR$0`. Review Comment: Maybe clarify that these are placeholders, and that you don't have to literally include this `AS` statement as is. For example, something like the following: Replace `table_alias_name` and `column_alias_name` with your choice of table and column name. To return only the unnested column, call `SELECT column_alias_name` or `SELECT table_alias_name.column_alias_name`. ########## docs/tutorials/tutorial-unnest-arrays.md: ########## @@ -140,9 +142,92 @@ Now that the data is loaded, run the following query: SELECT * FROM nested_data ``` -In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries, such as Scan. +In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries against the unnested records. Depending on the type of queries you write, see either [Unnest using SQL queries](#unnest-using-sql-queries) or [Unnest using native queries](#unnest-using-native-queries). + +## Unnest using SQL queries + +The following is the general syntax for UNNEST: + +```sql +SELECT target_column FROM datasource, UNNEST(source) AS table_alias_name(column_alias_name) +``` + +For more information about the syntax, see [UNNEST](../querying/sql.md#unnest). + +### Unnest inline array + +The following query returns a column that unnests the array `[1,2,3]` that is provided inline: + +```sql +SELECT * FROM UNNEST(ARRAY[1,2,3]) +``` + +If you unnest that same inline array while using a table as the datasource, Druid treats this as a JOIN between a left datasource and a constant datasource. For example: -## Unnest a single column +```sql +SELECT longs FROM nested_data, UNNEST(ARRAY[1,2,3]) as UNNESTED (longs)" Review Comment: ```suggestion SELECT longs FROM nested_data, UNNEST(ARRAY[1,2,3]) AS UNNESTED (longs) ``` ########## docs/tutorials/tutorial-unnest-arrays.md: ########## @@ -61,13 +59,15 @@ SELECT dim1, dim2, dim3, + dim4, + dim5, m1, m2 FROM TABLE( EXTERN( - '{"type":"inline","data":"{\"t\":\"2000-01-01\",\"m1\":\"1.0\",\"m2\":\"1.0\",\"dim1\":\"\",\"dim2\":[\"a\"],\"dim3\":[\"a\",\"b\"]},\n{\"t\":\"2000-01-02\",\"m1\":\"2.0\",\"m2\":\"2.0\",\"dim1\":\"10.1\",\"dim2\":[],\"dim3\":[\"c\",\"d\"]},\n{\"t\":\"2000-01-03\",\"m1\":\"3.0\",\"m2\":\"3.0\",\"dim1\":\"2\",\"dim2\":[\"\"],\"dim3\":[\"e\",\"f\"]},\n{\"t\":\"2001-01-01\",\"m1\":\"4.0\",\"m2\":\"4.0\",\"dim1\":\"1\",\"dim2\":[\"a\"],\"dim3\":[\"g\",\"h\"]},\n{\"t\":\"2001-01-02\",\"m1\":\"5.0\",\"m2\":\"5.0\",\"dim1\":\"def\",\"dim2\":[\"abc\"],\"dim3\":[\"i\",\"j\"]},\n{\"t\":\"2001-01-03\",\"m1\":\"6.0\",\"m2\":\"6.0\",\"dim1\":\"abc\",\"dim2\":[\"a\"],\"dim3\":[\"k\",\"l\"]},\n{\"t\":\"2001-01-02\",\"m1\":\"5.0\",\"m2\":\"5.0\",\"dim1\":\"def\",\"dim2\":[\"abc\"],\"dim3\":[\"m\",\"n\"]}"}', + '{"type":"inline","data":"{\"t\":\"2000-01-01\",\"m1\":\"1.0\",\"m2\":\"1.0\",\"dim1\":\"\",\"dim2\":[\"a\"],\"dim3\":[\"a\",\"b\"],\"dim4\":[\"x\",\"y\"],\"dim5\":[\"a\",\"b\"]},\n{\"t\":\"2000-01-02\",\"m1\":\"2.0\",\"m2\":\"2.0\",\"dim1\":\"10.1\",\"dim2\":[],\"dim3\":[\"c\",\"d\"],\"dim4\":[\"e\",\"f\"],\"dim5\":[\"a\",\"b\",\"c\",\"d\"]},\n{\"t\":\"2001-01-03\",\"m1\":\"6.0\",\"m2\":\"6.0\",\"dim1\":\"abc\",\"dim2\":[\"a\"],\"dim3\":[\"k\",\"l\"]},\n{\"t\":\"2001-01-01\",\"m1\":\"4.0\",\"m2\":\"4.0\",\"dim1\":\"1\",\"dim2\":[\"a\"],\"dim3\":[\"g\",\"h\"]},\n{\"t\":\"2001-01-02\",\"m1\":\"5.0\",\"m2\":\"5.0\",\"dim1\":\"def\",\"dim2\":[\"abc\"],\"dim3\":[\"i\",\"j\"]},\n{\"t\":\"2001-01-03\",\"m1\":\"6.0\",\"m2\":\"6.0\",\"dim1\":\"abc\",\"dim2\":[\"a\"],\"dim3\":[\"k\",\"l\"]},\n{\"t\":\"2001-01-02\",\"m1\":\"5.0\",\"m2\":\"5.0\",\"dim1\":\"def\",\"dim2\":[\"abc\"],\"dim3\":[\"m\",\"n\"]}"}', '{"type":"json"}', - '[{"name":"t","type":"string"},{"name":"dim1","type":"string"},{"name":"dim2","type":"string"},{"name":"dim3","type":"string"},{"name":"m1","type":"float"},{"name":"m2","type":"double"}]' + '[{"name":"t","type":"string"},{"name":"dim1","type":"string"},{"name":"dim2","type":"string"},{"name":"dim3","type":"string"},{"name":"dim4","type":"string"},{"name":"m1","type":"float"},{"name":"m2","type":"double"}]' Review Comment: ```suggestion '[{"name":"t","type":"string"},{"name":"dim1","type":"string"},{"name":"dim2","type":"string"},{"name":"dim3","type":"string"},{"name":"dim4","type":"string"},{"name":"dim5","type":"string"},{"name":"m1","type":"float"},{"name":"m2","type":"double"}]' ``` ########## docs/tutorials/tutorial-unnest-arrays.md: ########## @@ -140,9 +142,92 @@ Now that the data is loaded, run the following query: SELECT * FROM nested_data ``` -In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries, such as Scan. +In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries against the unnested records. Depending on the type of queries you write, see either [Unnest using SQL queries](#unnest-using-sql-queries) or [Unnest using native queries](#unnest-using-native-queries). + +## Unnest using SQL queries + +The following is the general syntax for UNNEST: + +```sql +SELECT target_column FROM datasource, UNNEST(source) AS table_alias_name(column_alias_name) +``` + +For more information about the syntax, see [UNNEST](../querying/sql.md#unnest). + +### Unnest inline array + +The following query returns a column that unnests the array `[1,2,3]` that is provided inline: + +```sql +SELECT * FROM UNNEST(ARRAY[1,2,3]) +``` + +If you unnest that same inline array while using a table as the datasource, Druid treats this as a JOIN between a left datasource and a constant datasource. For example: -## Unnest a single column +```sql +SELECT longs FROM nested_data, UNNEST(ARRAY[1,2,3]) as UNNESTED (longs)" +``` + +### Unnest a single column in a table + +The following query returns a column called `d3` from the table `nested_data`. `d3` contains the unnested values from the source column `dim3`: + +```sql +SELECT d3 FROM "nested_data", UNNEST(MV_TO_ARRAY(dim3)) as UNNESTED (d3) +``` + +Notice the `MV_TO_ARRAY` helper function, which converts the multi-value records in `dim3` to arrays. It is required since `dim3` is a multi-value string dimension. + +If the column you are unnesting is not a string dimension, then you do not need to use the MV_TO_ARRAY helper function. + +### Unnest a virtual column + +You can unnest into a virtual column (multiple columns treated as one). The following query returns the two source columns and a third virtual column containing the unnested data: + +```sql +SELECT dim4,dim5,d45 FROM nested_data, UNNEST(ARRAY[dim4,dim5]) as UNNESTED (d45) +``` + +This virtual column is the product of the two source columns. Notice how the total number of rows has grown. The table `nested_data` had only 7 rows originally. + +Another way to unnest a virtual column is to concatenate them with ARRAY_CONCAT: + +```sql +select d45 from nested_data, UNNEST(ARRAY_CONCAT(dim4,dim5)) AS UNNESTED (d45) +``` + +Decide which method to use based on what your goals are. + +### Unnest a column from a subset of a table + +The following query uses only 3 columns from the `nested_data` table as the datasource. From that subset, it unnests the column `dim3` into `d3` and returns `d3`. + +```sql +SELECT d3 FROM (select dim1, dim2, dim3 from "nested_data"), UNNEST(MV_TO_ARRAY(dim3)) as UNNESTED (d3) +``` + +### Unnest with a filter + +You can specify which rows to unnest by including a filter in your query. The following query: +* Unnests the records in `dim3` into `d3` +* Filters based on `dim2` +* Returns the records for the unnested `d3` that have a `dim2` record that matches the filter + +```sql +SELECT d3 FROM (select * from nested_data WHERE dim2 IN ('abc')), UNNEST(MV_TO_ARRAY(dim3)) as UNNESTED (d3) Review Comment: ```suggestion SELECT d3 FROM (SELECT * FROM nested_data WHERE dim2 IN ('abc')), UNNEST(MV_TO_ARRAY(dim3)) AS UNNESTED (d3) ``` ########## docs/tutorials/tutorial-unnest-arrays.md: ########## @@ -140,9 +142,92 @@ Now that the data is loaded, run the following query: SELECT * FROM nested_data ``` -In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries, such as Scan. +In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries against the unnested records. Depending on the type of queries you write, see either [Unnest using SQL queries](#unnest-using-sql-queries) or [Unnest using native queries](#unnest-using-native-queries). + +## Unnest using SQL queries + +The following is the general syntax for UNNEST: + +```sql +SELECT target_column FROM datasource, UNNEST(source) AS table_alias_name(column_alias_name) +``` + +For more information about the syntax, see [UNNEST](../querying/sql.md#unnest). + +### Unnest inline array + +The following query returns a column that unnests the array `[1,2,3]` that is provided inline: + +```sql +SELECT * FROM UNNEST(ARRAY[1,2,3]) +``` + +If you unnest that same inline array while using a table as the datasource, Druid treats this as a JOIN between a left datasource and a constant datasource. For example: -## Unnest a single column +```sql +SELECT longs FROM nested_data, UNNEST(ARRAY[1,2,3]) as UNNESTED (longs)" +``` + +### Unnest a single column in a table + +The following query returns a column called `d3` from the table `nested_data`. `d3` contains the unnested values from the source column `dim3`: + +```sql +SELECT d3 FROM "nested_data", UNNEST(MV_TO_ARRAY(dim3)) as UNNESTED (d3) +``` + +Notice the `MV_TO_ARRAY` helper function, which converts the multi-value records in `dim3` to arrays. It is required since `dim3` is a multi-value string dimension. + +If the column you are unnesting is not a string dimension, then you do not need to use the MV_TO_ARRAY helper function. + +### Unnest a virtual column + +You can unnest into a virtual column (multiple columns treated as one). The following query returns the two source columns and a third virtual column containing the unnested data: + +```sql +SELECT dim4,dim5,d45 FROM nested_data, UNNEST(ARRAY[dim4,dim5]) as UNNESTED (d45) +``` + +This virtual column is the product of the two source columns. Notice how the total number of rows has grown. The table `nested_data` had only 7 rows originally. Review Comment: ```suggestion This virtual column is the product of the two source columns. Notice how the total number of rows has grown. The table `nested_data` had only seven rows originally. ``` Following https://developers.google.com/style/numbers ########## docs/querying/sql.md: ########## @@ -82,6 +83,27 @@ FROM clause, metadata tables are not considered datasources. They exist only in For more information about table, lookup, query, and join datasources, refer to the [Datasources](datasource.md) documentation. +## UNNEST + +The UNNEST clause unnests values arrays. It's the SQL equivalent to the [unnest datasource](./datasource.md#unnest). The source for UNNEST can be an array or an input that's been transformed into an array, such as with helper functions like MV_TO_ARRAY or ARRAY. + +The following is the general syntax for UNNEST, specifically a query that returns the column that gets unnested: + +```sql +SELECT target_column FROM datasource, UNNEST(source_expression) AS table_alias_name(column_alias_name) +``` + +* The `datasource` for UNNEST can be any Druid datasource, such as the following: + * A table, such as `FROM a_table` + * A subset of a table based on a query, such as `FROM (SELECT columnA,columnB,columnC from a_table)`, a filter, or a JOIN. + * An inline array, which is treated as the `datasource` and the `source`, such as `FROM UNNEST(ARRAY[1,2,3])` +* The `source_expression` for the UNNEST function must be an array and can come from any expression. If the dimension you are unnesting is a multi-value dimension, you have to specify `MV_TO_ARRAY(dimension)` to convert it to an implicit ARRAY<> type. You can also specify any expression that has an SQL array datatype. For example, use `ARRAY[dim1,dim2]` if you want to make an array out of 2 dimensions or `ARRAY_CONCAT(dim1,dim2)` if you have to concatenate two multi-value dimensions. Review Comment: ```suggestion * The `source_expression` for the UNNEST function must be an array and can come from any expression. If the dimension you are unnesting is a multi-value dimension, you have to specify `MV_TO_ARRAY(dimension)` to convert it to an implicit ARRAY<> type. You can also specify any expression that has an SQL array datatype. For example, use `ARRAY[dim1,dim2]` if you want to make an array out of two dimensions or `ARRAY_CONCAT(dim1,dim2)` if you have to concatenate two multi-value dimensions. ``` ########## docs/querying/sql.md: ########## @@ -82,6 +83,27 @@ FROM clause, metadata tables are not considered datasources. They exist only in For more information about table, lookup, query, and join datasources, refer to the [Datasources](datasource.md) documentation. +## UNNEST + +The UNNEST clause unnests values arrays. It's the SQL equivalent to the [unnest datasource](./datasource.md#unnest). The source for UNNEST can be an array or an input that's been transformed into an array, such as with helper functions like MV_TO_ARRAY or ARRAY. + +The following is the general syntax for UNNEST, specifically a query that returns the column that gets unnested: + +```sql +SELECT target_column FROM datasource, UNNEST(source_expression) AS table_alias_name(column_alias_name) +``` + +* The `datasource` for UNNEST can be any Druid datasource, such as the following: + * A table, such as `FROM a_table` + * A subset of a table based on a query, such as `FROM (SELECT columnA,columnB,columnC from a_table)`, a filter, or a JOIN. + * An inline array, which is treated as the `datasource` and the `source`, such as `FROM UNNEST(ARRAY[1,2,3])` +* The `source_expression` for the UNNEST function must be an array and can come from any expression. If the dimension you are unnesting is a multi-value dimension, you have to specify `MV_TO_ARRAY(dimension)` to convert it to an implicit ARRAY<> type. You can also specify any expression that has an SQL array datatype. For example, use `ARRAY[dim1,dim2]` if you want to make an array out of 2 dimensions or `ARRAY_CONCAT(dim1,dim2)` if you have to concatenate two multi-value dimensions. Review Comment: Are the angle brackets needed in `ARRAY<>`? ########## docs/tutorials/tutorial-unnest-arrays.md: ########## @@ -140,9 +142,92 @@ Now that the data is loaded, run the following query: SELECT * FROM nested_data ``` -In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries, such as Scan. +In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries against the unnested records. Depending on the type of queries you write, see either [Unnest using SQL queries](#unnest-using-sql-queries) or [Unnest using native queries](#unnest-using-native-queries). + +## Unnest using SQL queries + +The following is the general syntax for UNNEST: + +```sql +SELECT target_column FROM datasource, UNNEST(source) AS table_alias_name(column_alias_name) +``` + +For more information about the syntax, see [UNNEST](../querying/sql.md#unnest). + +### Unnest inline array + +The following query returns a column that unnests the array `[1,2,3]` that is provided inline: + +```sql +SELECT * FROM UNNEST(ARRAY[1,2,3]) +``` + +If you unnest that same inline array while using a table as the datasource, Druid treats this as a JOIN between a left datasource and a constant datasource. For example: -## Unnest a single column +```sql +SELECT longs FROM nested_data, UNNEST(ARRAY[1,2,3]) as UNNESTED (longs)" +``` + +### Unnest a single column in a table + +The following query returns a column called `d3` from the table `nested_data`. `d3` contains the unnested values from the source column `dim3`: + +```sql +SELECT d3 FROM "nested_data", UNNEST(MV_TO_ARRAY(dim3)) as UNNESTED (d3) Review Comment: ```suggestion SELECT d3 FROM "nested_data", UNNEST(MV_TO_ARRAY(dim3)) AS UNNESTED (d3) ``` ########## docs/tutorials/tutorial-unnest-arrays.md: ########## @@ -1,7 +1,7 @@ --- -id: tutorial-unnest-datasource -sidebar_label: "Using the unnest datasource" -title: "Tutorial: Unnest data in a column" +id: tutorial-unnest-arrays Review Comment: Also need to update the tutorial link in the native query doc ########## docs/querying/sql.md: ########## @@ -82,6 +83,27 @@ FROM clause, metadata tables are not considered datasources. They exist only in For more information about table, lookup, query, and join datasources, refer to the [Datasources](datasource.md) documentation. +## UNNEST + +The UNNEST clause unnests values arrays. It's the SQL equivalent to the [unnest datasource](./datasource.md#unnest). The source for UNNEST can be an array or an input that's been transformed into an array, such as with helper functions like MV_TO_ARRAY or ARRAY. + +The following is the general syntax for UNNEST, specifically a query that returns the column that gets unnested: + +```sql +SELECT target_column FROM datasource, UNNEST(source_expression) AS table_alias_name(column_alias_name) Review Comment: Does `target_column` need to be `column_alias_name`? I.e., should the column names match in the SELECT and after the AS? ########## docs/tutorials/tutorial-unnest-arrays.md: ########## @@ -140,9 +142,92 @@ Now that the data is loaded, run the following query: SELECT * FROM nested_data ``` -In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries, such as Scan. +In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries against the unnested records. Depending on the type of queries you write, see either [Unnest using SQL queries](#unnest-using-sql-queries) or [Unnest using native queries](#unnest-using-native-queries). + +## Unnest using SQL queries + +The following is the general syntax for UNNEST: + +```sql +SELECT target_column FROM datasource, UNNEST(source) AS table_alias_name(column_alias_name) +``` + +For more information about the syntax, see [UNNEST](../querying/sql.md#unnest). + +### Unnest inline array + +The following query returns a column that unnests the array `[1,2,3]` that is provided inline: + +```sql +SELECT * FROM UNNEST(ARRAY[1,2,3]) +``` + +If you unnest that same inline array while using a table as the datasource, Druid treats this as a JOIN between a left datasource and a constant datasource. For example: -## Unnest a single column +```sql +SELECT longs FROM nested_data, UNNEST(ARRAY[1,2,3]) as UNNESTED (longs)" +``` + +### Unnest a single column in a table + +The following query returns a column called `d3` from the table `nested_data`. `d3` contains the unnested values from the source column `dim3`: + +```sql +SELECT d3 FROM "nested_data", UNNEST(MV_TO_ARRAY(dim3)) as UNNESTED (d3) +``` + +Notice the `MV_TO_ARRAY` helper function, which converts the multi-value records in `dim3` to arrays. It is required since `dim3` is a multi-value string dimension. + +If the column you are unnesting is not a string dimension, then you do not need to use the MV_TO_ARRAY helper function. + +### Unnest a virtual column + +You can unnest into a virtual column (multiple columns treated as one). The following query returns the two source columns and a third virtual column containing the unnested data: + +```sql +SELECT dim4,dim5,d45 FROM nested_data, UNNEST(ARRAY[dim4,dim5]) as UNNESTED (d45) Review Comment: ```suggestion SELECT dim4, dim5, d45 FROM nested_data, UNNEST(ARRAY[dim4,dim5]) AS UNNESTED (d45) ``` ########## docs/tutorials/tutorial-unnest-arrays.md: ########## @@ -140,9 +142,92 @@ Now that the data is loaded, run the following query: SELECT * FROM nested_data ``` -In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries, such as Scan. +In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries against the unnested records. Depending on the type of queries you write, see either [Unnest using SQL queries](#unnest-using-sql-queries) or [Unnest using native queries](#unnest-using-native-queries). + +## Unnest using SQL queries + +The following is the general syntax for UNNEST: + +```sql +SELECT target_column FROM datasource, UNNEST(source) AS table_alias_name(column_alias_name) +``` + +For more information about the syntax, see [UNNEST](../querying/sql.md#unnest). + +### Unnest inline array + +The following query returns a column that unnests the array `[1,2,3]` that is provided inline: + +```sql +SELECT * FROM UNNEST(ARRAY[1,2,3]) +``` + +If you unnest that same inline array while using a table as the datasource, Druid treats this as a JOIN between a left datasource and a constant datasource. For example: -## Unnest a single column +```sql +SELECT longs FROM nested_data, UNNEST(ARRAY[1,2,3]) as UNNESTED (longs)" +``` + +### Unnest a single column in a table + +The following query returns a column called `d3` from the table `nested_data`. `d3` contains the unnested values from the source column `dim3`: + +```sql +SELECT d3 FROM "nested_data", UNNEST(MV_TO_ARRAY(dim3)) as UNNESTED (d3) +``` + +Notice the `MV_TO_ARRAY` helper function, which converts the multi-value records in `dim3` to arrays. It is required since `dim3` is a multi-value string dimension. + +If the column you are unnesting is not a string dimension, then you do not need to use the MV_TO_ARRAY helper function. + +### Unnest a virtual column + +You can unnest into a virtual column (multiple columns treated as one). The following query returns the two source columns and a third virtual column containing the unnested data: + +```sql +SELECT dim4,dim5,d45 FROM nested_data, UNNEST(ARRAY[dim4,dim5]) as UNNESTED (d45) +``` + +This virtual column is the product of the two source columns. Notice how the total number of rows has grown. The table `nested_data` had only 7 rows originally. + +Another way to unnest a virtual column is to concatenate them with ARRAY_CONCAT: + +```sql +select d45 from nested_data, UNNEST(ARRAY_CONCAT(dim4,dim5)) AS UNNESTED (d45) +``` + +Decide which method to use based on what your goals are. + +### Unnest a column from a subset of a table + +The following query uses only 3 columns from the `nested_data` table as the datasource. From that subset, it unnests the column `dim3` into `d3` and returns `d3`. + +```sql +SELECT d3 FROM (select dim1, dim2, dim3 from "nested_data"), UNNEST(MV_TO_ARRAY(dim3)) as UNNESTED (d3) Review Comment: ```suggestion SELECT d3 FROM (SELECT dim1, dim2, dim3 FROM "nested_data"), UNNEST(MV_TO_ARRAY(dim3)) AS UNNESTED (d3) ``` ########## docs/tutorials/tutorial-unnest-arrays.md: ########## @@ -140,9 +142,92 @@ Now that the data is loaded, run the following query: SELECT * FROM nested_data ``` -In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries, such as Scan. +In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries against the unnested records. Depending on the type of queries you write, see either [Unnest using SQL queries](#unnest-using-sql-queries) or [Unnest using native queries](#unnest-using-native-queries). + +## Unnest using SQL queries + +The following is the general syntax for UNNEST: + +```sql +SELECT target_column FROM datasource, UNNEST(source) AS table_alias_name(column_alias_name) +``` + +For more information about the syntax, see [UNNEST](../querying/sql.md#unnest). + +### Unnest inline array + +The following query returns a column that unnests the array `[1,2,3]` that is provided inline: + +```sql +SELECT * FROM UNNEST(ARRAY[1,2,3]) +``` + +If you unnest that same inline array while using a table as the datasource, Druid treats this as a JOIN between a left datasource and a constant datasource. For example: -## Unnest a single column +```sql +SELECT longs FROM nested_data, UNNEST(ARRAY[1,2,3]) as UNNESTED (longs)" +``` + +### Unnest a single column in a table + +The following query returns a column called `d3` from the table `nested_data`. `d3` contains the unnested values from the source column `dim3`: + +```sql +SELECT d3 FROM "nested_data", UNNEST(MV_TO_ARRAY(dim3)) as UNNESTED (d3) +``` + +Notice the `MV_TO_ARRAY` helper function, which converts the multi-value records in `dim3` to arrays. It is required since `dim3` is a multi-value string dimension. + +If the column you are unnesting is not a string dimension, then you do not need to use the MV_TO_ARRAY helper function. + +### Unnest a virtual column + +You can unnest into a virtual column (multiple columns treated as one). The following query returns the two source columns and a third virtual column containing the unnested data: + +```sql +SELECT dim4,dim5,d45 FROM nested_data, UNNEST(ARRAY[dim4,dim5]) as UNNESTED (d45) +``` + +This virtual column is the product of the two source columns. Notice how the total number of rows has grown. The table `nested_data` had only 7 rows originally. + +Another way to unnest a virtual column is to concatenate them with ARRAY_CONCAT: + +```sql +select d45 from nested_data, UNNEST(ARRAY_CONCAT(dim4,dim5)) AS UNNESTED (d45) Review Comment: ```suggestion SELECT d45 FROM nested_data, UNNEST(ARRAY_CONCAT(dim4,dim5)) AS UNNESTED (d45) ``` ########## docs/tutorials/tutorial-unnest-arrays.md: ########## @@ -140,9 +142,92 @@ Now that the data is loaded, run the following query: SELECT * FROM nested_data ``` -In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries, such as Scan. +In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries against the unnested records. Depending on the type of queries you write, see either [Unnest using SQL queries](#unnest-using-sql-queries) or [Unnest using native queries](#unnest-using-native-queries). + +## Unnest using SQL queries + +The following is the general syntax for UNNEST: + +```sql +SELECT target_column FROM datasource, UNNEST(source) AS table_alias_name(column_alias_name) +``` + +For more information about the syntax, see [UNNEST](../querying/sql.md#unnest). + +### Unnest inline array + +The following query returns a column that unnests the array `[1,2,3]` that is provided inline: + +```sql +SELECT * FROM UNNEST(ARRAY[1,2,3]) +``` + +If you unnest that same inline array while using a table as the datasource, Druid treats this as a JOIN between a left datasource and a constant datasource. For example: -## Unnest a single column +```sql +SELECT longs FROM nested_data, UNNEST(ARRAY[1,2,3]) as UNNESTED (longs)" +``` + +### Unnest a single column in a table + +The following query returns a column called `d3` from the table `nested_data`. `d3` contains the unnested values from the source column `dim3`: + +```sql +SELECT d3 FROM "nested_data", UNNEST(MV_TO_ARRAY(dim3)) as UNNESTED (d3) +``` + +Notice the `MV_TO_ARRAY` helper function, which converts the multi-value records in `dim3` to arrays. It is required since `dim3` is a multi-value string dimension. + +If the column you are unnesting is not a string dimension, then you do not need to use the MV_TO_ARRAY helper function. Review Comment: ```suggestion If the column you are unnesting is not a string dimension, then you do not need to use the `MV_TO_ARRAY` helper function. ``` Either code format this instance or un-code format the function name in L179 ########## docs/tutorials/tutorial-unnest-arrays.md: ########## @@ -140,9 +142,92 @@ Now that the data is loaded, run the following query: SELECT * FROM nested_data ``` -In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries, such as Scan. +In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries against the unnested records. Depending on the type of queries you write, see either [Unnest using SQL queries](#unnest-using-sql-queries) or [Unnest using native queries](#unnest-using-native-queries). + +## Unnest using SQL queries + +The following is the general syntax for UNNEST: + +```sql +SELECT target_column FROM datasource, UNNEST(source) AS table_alias_name(column_alias_name) +``` + +For more information about the syntax, see [UNNEST](../querying/sql.md#unnest). + +### Unnest inline array + +The following query returns a column that unnests the array `[1,2,3]` that is provided inline: + +```sql +SELECT * FROM UNNEST(ARRAY[1,2,3]) +``` + +If you unnest that same inline array while using a table as the datasource, Druid treats this as a JOIN between a left datasource and a constant datasource. For example: -## Unnest a single column +```sql +SELECT longs FROM nested_data, UNNEST(ARRAY[1,2,3]) as UNNESTED (longs)" +``` + +### Unnest a single column in a table + +The following query returns a column called `d3` from the table `nested_data`. `d3` contains the unnested values from the source column `dim3`: + +```sql +SELECT d3 FROM "nested_data", UNNEST(MV_TO_ARRAY(dim3)) as UNNESTED (d3) +``` + +Notice the `MV_TO_ARRAY` helper function, which converts the multi-value records in `dim3` to arrays. It is required since `dim3` is a multi-value string dimension. + +If the column you are unnesting is not a string dimension, then you do not need to use the MV_TO_ARRAY helper function. + +### Unnest a virtual column + +You can unnest into a virtual column (multiple columns treated as one). The following query returns the two source columns and a third virtual column containing the unnested data: + +```sql +SELECT dim4,dim5,d45 FROM nested_data, UNNEST(ARRAY[dim4,dim5]) as UNNESTED (d45) +``` + +This virtual column is the product of the two source columns. Notice how the total number of rows has grown. The table `nested_data` had only 7 rows originally. + +Another way to unnest a virtual column is to concatenate them with ARRAY_CONCAT: + +```sql +select d45 from nested_data, UNNEST(ARRAY_CONCAT(dim4,dim5)) AS UNNESTED (d45) +``` + +Decide which method to use based on what your goals are. + +### Unnest a column from a subset of a table + +The following query uses only 3 columns from the `nested_data` table as the datasource. From that subset, it unnests the column `dim3` into `d3` and returns `d3`. + +```sql +SELECT d3 FROM (select dim1, dim2, dim3 from "nested_data"), UNNEST(MV_TO_ARRAY(dim3)) as UNNESTED (d3) +``` + +### Unnest with a filter + +You can specify which rows to unnest by including a filter in your query. The following query: +* Unnests the records in `dim3` into `d3` +* Filters based on `dim2` +* Returns the records for the unnested `d3` that have a `dim2` record that matches the filter + +```sql +SELECT d3 FROM (select * from nested_data WHERE dim2 IN ('abc')), UNNEST(MV_TO_ARRAY(dim3)) as UNNESTED (d3) +``` + +### Unnest and then GROUP BY + +The following query unnests `dim3` and then performs a GROUP BY on the output `d3`. + +```sql +SELECT d3 FROM nested_data, UNNEST(MV_TO_ARRAY(dim3)) as UNNESTED (d3) GROUP BY d3 Review Comment: ```suggestion SELECT d3 FROM nested_data, UNNEST(MV_TO_ARRAY(dim3)) AS UNNESTED (d3) GROUP BY d3 ``` ########## docs/tutorials/tutorial-unnest-arrays.md: ########## @@ -140,9 +142,92 @@ Now that the data is loaded, run the following query: SELECT * FROM nested_data ``` -In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries, such as Scan. +In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries against the unnested records. Depending on the type of queries you write, see either [Unnest using SQL queries](#unnest-using-sql-queries) or [Unnest using native queries](#unnest-using-native-queries). + +## Unnest using SQL queries + +The following is the general syntax for UNNEST: + +```sql +SELECT target_column FROM datasource, UNNEST(source) AS table_alias_name(column_alias_name) +``` + +For more information about the syntax, see [UNNEST](../querying/sql.md#unnest). + +### Unnest inline array + +The following query returns a column that unnests the array `[1,2,3]` that is provided inline: + +```sql +SELECT * FROM UNNEST(ARRAY[1,2,3]) +``` + +If you unnest that same inline array while using a table as the datasource, Druid treats this as a JOIN between a left datasource and a constant datasource. For example: -## Unnest a single column +```sql +SELECT longs FROM nested_data, UNNEST(ARRAY[1,2,3]) as UNNESTED (longs)" +``` + +### Unnest a single column in a table + +The following query returns a column called `d3` from the table `nested_data`. `d3` contains the unnested values from the source column `dim3`: + +```sql +SELECT d3 FROM "nested_data", UNNEST(MV_TO_ARRAY(dim3)) as UNNESTED (d3) +``` + +Notice the `MV_TO_ARRAY` helper function, which converts the multi-value records in `dim3` to arrays. It is required since `dim3` is a multi-value string dimension. + +If the column you are unnesting is not a string dimension, then you do not need to use the MV_TO_ARRAY helper function. + +### Unnest a virtual column + +You can unnest into a virtual column (multiple columns treated as one). The following query returns the two source columns and a third virtual column containing the unnested data: + +```sql +SELECT dim4,dim5,d45 FROM nested_data, UNNEST(ARRAY[dim4,dim5]) as UNNESTED (d45) +``` + +This virtual column is the product of the two source columns. Notice how the total number of rows has grown. The table `nested_data` had only 7 rows originally. + +Another way to unnest a virtual column is to concatenate them with ARRAY_CONCAT: + +```sql +select d45 from nested_data, UNNEST(ARRAY_CONCAT(dim4,dim5)) AS UNNESTED (d45) +``` + +Decide which method to use based on what your goals are. + +### Unnest a column from a subset of a table + +The following query uses only 3 columns from the `nested_data` table as the datasource. From that subset, it unnests the column `dim3` into `d3` and returns `d3`. + +```sql +SELECT d3 FROM (select dim1, dim2, dim3 from "nested_data"), UNNEST(MV_TO_ARRAY(dim3)) as UNNESTED (d3) +``` + +### Unnest with a filter + +You can specify which rows to unnest by including a filter in your query. The following query: +* Unnests the records in `dim3` into `d3` +* Filters based on `dim2` Review Comment: Should filters go above unnests, since filters is in the SELECT source? ########## docs/tutorials/tutorial-unnest-arrays.md: ########## @@ -140,9 +142,92 @@ Now that the data is loaded, run the following query: SELECT * FROM nested_data ``` -In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries, such as Scan. +In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries against the unnested records. Depending on the type of queries you write, see either [Unnest using SQL queries](#unnest-using-sql-queries) or [Unnest using native queries](#unnest-using-native-queries). + +## Unnest using SQL queries + +The following is the general syntax for UNNEST: + +```sql +SELECT target_column FROM datasource, UNNEST(source) AS table_alias_name(column_alias_name) +``` + +For more information about the syntax, see [UNNEST](../querying/sql.md#unnest). + +### Unnest inline array + +The following query returns a column that unnests the array `[1,2,3]` that is provided inline: + +```sql +SELECT * FROM UNNEST(ARRAY[1,2,3]) +``` + +If you unnest that same inline array while using a table as the datasource, Druid treats this as a JOIN between a left datasource and a constant datasource. For example: -## Unnest a single column +```sql +SELECT longs FROM nested_data, UNNEST(ARRAY[1,2,3]) as UNNESTED (longs)" +``` + +### Unnest a single column in a table + +The following query returns a column called `d3` from the table `nested_data`. `d3` contains the unnested values from the source column `dim3`: + +```sql +SELECT d3 FROM "nested_data", UNNEST(MV_TO_ARRAY(dim3)) as UNNESTED (d3) +``` + +Notice the `MV_TO_ARRAY` helper function, which converts the multi-value records in `dim3` to arrays. It is required since `dim3` is a multi-value string dimension. + +If the column you are unnesting is not a string dimension, then you do not need to use the MV_TO_ARRAY helper function. + +### Unnest a virtual column + +You can unnest into a virtual column (multiple columns treated as one). The following query returns the two source columns and a third virtual column containing the unnested data: + +```sql +SELECT dim4,dim5,d45 FROM nested_data, UNNEST(ARRAY[dim4,dim5]) as UNNESTED (d45) +``` + +This virtual column is the product of the two source columns. Notice how the total number of rows has grown. The table `nested_data` had only 7 rows originally. + +Another way to unnest a virtual column is to concatenate them with ARRAY_CONCAT: + +```sql +select d45 from nested_data, UNNEST(ARRAY_CONCAT(dim4,dim5)) AS UNNESTED (d45) +``` + +Decide which method to use based on what your goals are. Review Comment: This seems kind of vague. Expand on it? Or does it need to be added at all? ########## docs/tutorials/tutorial-unnest-arrays.md: ########## @@ -140,9 +142,92 @@ Now that the data is loaded, run the following query: SELECT * FROM nested_data ``` -In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries, such as Scan. +In the results, notice that the column named `dim3` has nested values like `["a","b"]`. The example queries that follow unnest `dim3` and run queries against the unnested records. Depending on the type of queries you write, see either [Unnest using SQL queries](#unnest-using-sql-queries) or [Unnest using native queries](#unnest-using-native-queries). + +## Unnest using SQL queries + +The following is the general syntax for UNNEST: + +```sql +SELECT target_column FROM datasource, UNNEST(source) AS table_alias_name(column_alias_name) +``` + +For more information about the syntax, see [UNNEST](../querying/sql.md#unnest). + +### Unnest inline array + +The following query returns a column that unnests the array `[1,2,3]` that is provided inline: + +```sql +SELECT * FROM UNNEST(ARRAY[1,2,3]) +``` + +If you unnest that same inline array while using a table as the datasource, Druid treats this as a JOIN between a left datasource and a constant datasource. For example: -## Unnest a single column +```sql +SELECT longs FROM nested_data, UNNEST(ARRAY[1,2,3]) as UNNESTED (longs)" +``` + +### Unnest a single column in a table + +The following query returns a column called `d3` from the table `nested_data`. `d3` contains the unnested values from the source column `dim3`: + +```sql +SELECT d3 FROM "nested_data", UNNEST(MV_TO_ARRAY(dim3)) as UNNESTED (d3) +``` + +Notice the `MV_TO_ARRAY` helper function, which converts the multi-value records in `dim3` to arrays. It is required since `dim3` is a multi-value string dimension. + +If the column you are unnesting is not a string dimension, then you do not need to use the MV_TO_ARRAY helper function. + +### Unnest a virtual column + +You can unnest into a virtual column (multiple columns treated as one). The following query returns the two source columns and a third virtual column containing the unnested data: + +```sql +SELECT dim4,dim5,d45 FROM nested_data, UNNEST(ARRAY[dim4,dim5]) as UNNESTED (d45) +``` + +This virtual column is the product of the two source columns. Notice how the total number of rows has grown. The table `nested_data` had only 7 rows originally. + +Another way to unnest a virtual column is to concatenate them with ARRAY_CONCAT: + +```sql +select d45 from nested_data, UNNEST(ARRAY_CONCAT(dim4,dim5)) AS UNNESTED (d45) +``` + +Decide which method to use based on what your goals are. + +### Unnest a column from a subset of a table + +The following query uses only 3 columns from the `nested_data` table as the datasource. From that subset, it unnests the column `dim3` into `d3` and returns `d3`. Review Comment: ```suggestion The following query uses only three columns from the `nested_data` table as the datasource. From that subset, it unnests the column `dim3` into `d3` and returns `d3`. ``` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
