[GitHub] [druid] somu-imply commented on a diff in pull request #13736: docs: sql unnest and cleanup unnest datasource

["somu-imply (via GitHub)" <gi...@apache.org>]

somu-imply commented on code in PR #13736:
URL: https://github.com/apache/druid/pull/13736#discussion_r1093848267


##########
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 stored in arrays within a column. It's the SQL equivalent to the [unnest datasource](./datasource.md#unnest).
+
+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) as UNNESTED(target_column)

Review Comment:
   source_expression might be better



##########
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 stored in arrays within a column. It's the SQL equivalent to the [unnest datasource](./datasource.md#unnest).
+
+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) as UNNESTED(target_column)
+```
+
+* The `datasource` for UNNEST can be any of 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)` or a filter.

Review Comment:
   Can also be a query or a join data source. Basically the data source can be any data source in Druid.



##########
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

Review Comment:
   Should we be constant and call it Unnest or Unnesting at all places ? `multi-value-dimensions.md` calls this Unnesting



##########
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 stored in arrays within a column. It's the SQL equivalent to the [unnest datasource](./datasource.md#unnest).
+
+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) as UNNESTED(target_column)
+```
+
+* The `datasource` for UNNEST can be any of 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)` or a filter.
+  * An inline array, which is treated as the `datasource` and the `source`, such as `FROM UNNEST(ARRAY[1,2,3])`
+* The `source` for the UNNEST function must be an array that exists in the `datasource`. Depending on how the `source` column is formatted, you may need to use helper functions. For example, if your column includes multi-dimension strings, you'll need to use MV_TO_ARRAY. Or if you're trying to join 2 columns with arrays, you'd need to use `ARRAY_CONCAT(column1,column2)` as the source..

Review Comment:
   I would reformat slightly differently.
   
   If the dimension you are unnesting is a MVD you have to specify MV_TO_ARRAY(dimension) to convert to an implicit ARRAY<> type. You can also specify any expression that has an sql array datatype. For example ARRAY[dim1,dim2] if you want to make an array out of 2 dimensions or ARRAY_CONCAT(dim1,dim2) if you have to concat two MVDs. The goal is to pass Unnest an ARRAY<Object>. The array can come from any expression



-- 
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: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org