This is an automated email from the ASF dual-hosted git repository.
vogievetsky pushed a commit to branch 24.0.0
in repository https://gitbox.apache.org/repos/asf/druid.git
The following commit(s) were added to refs/heads/24.0.0 by this push:
new eba4bfeb66 Nested columns documentation (#12946) (#13035)
eba4bfeb66 is described below
commit eba4bfeb668c4efe4834c97d749030ced4b76550
Author: Victoria Lim <[email protected]>
AuthorDate: Tue Sep 6 23:24:35 2022 -0700
Nested columns documentation (#12946) (#13035)
Co-authored-by: Clint Wylie <[email protected]>
Co-authored-by: Victoria Lim <[email protected]>
Co-authored-by: Charles Smith <[email protected]>
Co-authored-by: brian.le <[email protected]>
(cherry picked from commit 1f691406237853409207d51ddde3c7546ca6fe47)
Co-authored-by: Jill Osborne <[email protected]>
---
docs/assets/nested-combined-json.png | Bin 0 -> 272858 bytes
docs/assets/nested-display-data-types.png | Bin 0 -> 254224 bytes
docs/assets/nested-examine-schema.png | Bin 0 -> 251706 bytes
docs/assets/nested-extract-as-type.png | Bin 0 -> 288918 bytes
docs/assets/nested-extract-elements.png | Bin 0 -> 317905 bytes
docs/assets/nested-group-aggregate.png | Bin 0 -> 249178 bytes
docs/assets/nested-msq-ingestion-transform.png | Bin 0 -> 370419 bytes
docs/assets/nested-msq-ingestion.png | Bin 0 -> 302033 bytes
docs/assets/nested-parse-deserialize.png | Bin 0 -> 213791 bytes
docs/assets/nested-retrieve-json.png | Bin 0 -> 295566 bytes
docs/assets/nested-return-json.png | Bin 0 -> 349144 bytes
docs/ingestion/data-formats.md | 4 +-
docs/ingestion/ingestion-spec.md | 2 +-
docs/ingestion/schema-design.md | 8 +-
docs/misc/math-expr.md | 2 +-
docs/querying/nested-columns.md | 597 +++++++++++++++++++++++++
docs/querying/sql-data-types.md | 24 +-
docs/querying/sql-json-functions.md | 5 +-
docs/querying/virtual-columns.md | 2 +-
website/sidebars.json | 2 +
20 files changed, 624 insertions(+), 22 deletions(-)
diff --git a/docs/assets/nested-combined-json.png
b/docs/assets/nested-combined-json.png
new file mode 100644
index 0000000000..f98bfcf538
Binary files /dev/null and b/docs/assets/nested-combined-json.png differ
diff --git a/docs/assets/nested-display-data-types.png
b/docs/assets/nested-display-data-types.png
new file mode 100644
index 0000000000..2776068ee4
Binary files /dev/null and b/docs/assets/nested-display-data-types.png differ
diff --git a/docs/assets/nested-examine-schema.png
b/docs/assets/nested-examine-schema.png
new file mode 100644
index 0000000000..11769a162a
Binary files /dev/null and b/docs/assets/nested-examine-schema.png differ
diff --git a/docs/assets/nested-extract-as-type.png
b/docs/assets/nested-extract-as-type.png
new file mode 100644
index 0000000000..c54a5eeb62
Binary files /dev/null and b/docs/assets/nested-extract-as-type.png differ
diff --git a/docs/assets/nested-extract-elements.png
b/docs/assets/nested-extract-elements.png
new file mode 100644
index 0000000000..9f7076b50d
Binary files /dev/null and b/docs/assets/nested-extract-elements.png differ
diff --git a/docs/assets/nested-group-aggregate.png
b/docs/assets/nested-group-aggregate.png
new file mode 100644
index 0000000000..2d1907fe64
Binary files /dev/null and b/docs/assets/nested-group-aggregate.png differ
diff --git a/docs/assets/nested-msq-ingestion-transform.png
b/docs/assets/nested-msq-ingestion-transform.png
new file mode 100644
index 0000000000..b46fde8593
Binary files /dev/null and b/docs/assets/nested-msq-ingestion-transform.png
differ
diff --git a/docs/assets/nested-msq-ingestion.png
b/docs/assets/nested-msq-ingestion.png
new file mode 100644
index 0000000000..0487ee1883
Binary files /dev/null and b/docs/assets/nested-msq-ingestion.png differ
diff --git a/docs/assets/nested-parse-deserialize.png
b/docs/assets/nested-parse-deserialize.png
new file mode 100644
index 0000000000..881a67164b
Binary files /dev/null and b/docs/assets/nested-parse-deserialize.png differ
diff --git a/docs/assets/nested-retrieve-json.png
b/docs/assets/nested-retrieve-json.png
new file mode 100644
index 0000000000..4f5fa0f969
Binary files /dev/null and b/docs/assets/nested-retrieve-json.png differ
diff --git a/docs/assets/nested-return-json.png
b/docs/assets/nested-return-json.png
new file mode 100644
index 0000000000..9a67aaa71d
Binary files /dev/null and b/docs/assets/nested-return-json.png differ
diff --git a/docs/ingestion/data-formats.md b/docs/ingestion/data-formats.md
index 751408d5a4..bb1b659c49 100644
--- a/docs/ingestion/data-formats.md
+++ b/docs/ingestion/data-formats.md
@@ -582,7 +582,9 @@ For example:
### FlattenSpec
-The `flattenSpec` object bridges the gap between potentially nested input
data, such as JSON or Avro, and Druid's flat data model. It is an object within
the `inputFormat` object.
+The `flattenSpec` object bridges the gap between potentially nested input
data, such as Avro or ORC, and Druid's flat data model. It is an object within
the `inputFormat` object.
+
+> If you have nested JSON data, you can ingest and store JSON in an Apache
Druid column as a `COMPLEX<json>` data type. See [Nested
columns](../querying/nested-columns.md) for more information.
Configure your `flattenSpec` as follows:
diff --git a/docs/ingestion/ingestion-spec.md b/docs/ingestion/ingestion-spec.md
index c87fb6141c..3684052d83 100644
--- a/docs/ingestion/ingestion-spec.md
+++ b/docs/ingestion/ingestion-spec.md
@@ -223,7 +223,7 @@ Dimension objects can have the following components:
| Field | Description | Default |
|-------|-------------|---------|
-| type | Either `string`, `long`, `float`, or `double`. | `string` |
+| type | Either `string`, `long`, `float`, `double`, or `json`. | `string` |
| name | The name of the dimension. This will be used as the field name to
read from input records, as well as the column name stored in generated
segments.<br><br>Note that you can use a [`transformSpec`](#transformspec) if
you want to rename columns during ingestion time. | none (required) |
| createBitmapIndex | For `string` typed dimensions, whether or not bitmap
indexes should be created for the column in generated segments. Creating a
bitmap index requires more storage, but speeds up certain kinds of filtering
(especially equality and prefix filtering). Only supported for `string` typed
dimensions. | `true` |
| multiValueHandling | Specify the type of handling for [multi-value
fields](../querying/multi-value-dimensions.md). Possible values are
`sorted_array`, `sorted_set`, and `array`. `sorted_array` and `sorted_set`
order the array upon ingestion. `sorted_set` removes duplicates. `array`
ingests data as-is | `sorted_array` |
diff --git a/docs/ingestion/schema-design.md b/docs/ingestion/schema-design.md
index af63fb5853..2d3cc0215b 100644
--- a/docs/ingestion/schema-design.md
+++ b/docs/ingestion/schema-design.md
@@ -201,8 +201,9 @@ like `MILLIS_TO_TIMESTAMP`, `TIME_FLOOR`, and others. If
you're using native Dru
### Nested dimensions
-At the time of this writing, Druid does not support nested dimensions. Nested
dimensions need to be flattened. For example,
-if you have data of the following form:
+You can ingest and store nested JSON in a Druid column as a `COMPLEX<json>`
data type. See [Nested columns](../querying/nested-columns.md) for more
information.
+
+If you want to ingest nested data in a format other than JSON—for
example Avro, ORC, and Parquet—you must use the `flattenSpec` object to
flatten it. For example, if you have data of the following form:
```
{"foo":{"bar": 3}}
@@ -214,8 +215,7 @@ then before indexing it, you should transform it to:
{"foo_bar": 3}
```
-Druid is capable of flattening JSON, Avro, or Parquet input data.
-Please read about [`flattenSpec`](./ingestion-spec.md#flattenspec) for more
details.
+See the [`flattenSpec`](./ingestion-spec.md#flattenspec) documentation for
more details.
<a name="counting"></a>
diff --git a/docs/misc/math-expr.md b/docs/misc/math-expr.md
index 8fe4aba1ab..1f85a59f3c 100644
--- a/docs/misc/math-expr.md
+++ b/docs/misc/math-expr.md
@@ -252,7 +252,7 @@ Druid supports a small, simplified subset of the [JSONPath
syntax](https://githu
|`['<name>']`| Child element in bracket notation. |
|`[<number>]`| Array index. |
-See [SQL JSON
documentation](../querying/sql-json-functions.md#jsonpath-syntax) for examples.
+See [SQL JSON
documentation](../querying/sql-json-functions.md#jsonpath-syntax) for examples
and [Nested columns](../querying/nested-columns.md) for more information on
ingesting and storing nested data.
## Reduction functions
diff --git a/docs/querying/nested-columns.md b/docs/querying/nested-columns.md
new file mode 100644
index 0000000000..1d9503eff5
--- /dev/null
+++ b/docs/querying/nested-columns.md
@@ -0,0 +1,597 @@
+---
+id: nested-columns
+title: "Nested columns"
+sidebar_label: Nested columns
+---
+
+<!--
+ ~ Licensed to the Apache Software Foundation (ASF) under one
+ ~ or more contributor license agreements. See the NOTICE file
+ ~ distributed with this work for additional information
+ ~ regarding copyright ownership. The ASF licenses this file
+ ~ to you under the Apache License, Version 2.0 (the
+ ~ "License"); you may not use this file except in compliance
+ ~ with the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing,
+ ~ software distributed under the License is distributed on an
+ ~ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ ~ KIND, either express or implied. See the License for the
+ ~ specific language governing permissions and limitations
+ ~ under the License.
+ -->
+
+> Nested columns is an experimental feature available starting in Apache Druid
24.0. Like most experimental features, functionality documented on this page is
subject to change in future releases. However, the COMPLEX column type includes
versioning to provide backward compatible support in future releases. We
strongly encourage you you experiment with nested columns in your development
environment to evaluate that they meet your use case. If so, you can use them
in production scenarios. [...]
+
+Apache Druid supports directly storing nested data structures in
`COMPLEX<json>` columns. `COMPLEX<json>` columns store a copy of the structured
data in JSON format and specialized internal columns and indexes for nested
literal values—STRING, LONG, and DOUBLE types. An optimized [virtual
column](./virtual-columns.md#nested-field-virtual-column) allows Druid to read
and filter these values at speeds consistent with standard Druid LONG, DOUBLE,
and STRING columns.
+
+Druid [SQL JSON functions](./sql-json-functions.md) allow you to extract,
transform, and create `COMPLEX<json>` values in SQL queries, using the
specialized virtual columns where appropriate. You can use the [JSON nested
columns functions](../misc/math-expr.md#json-functions) in [native
queries](./querying.md) using [expression virtual
columns](./virtual-columns.md#expression-virtual-column), and in native
ingestion with a
[`transformSpec`](../ingestion/ingestion-spec.md#transformspec).
+
+You can use the JSON functions in INSERT and REPLACE statements in SQL-based
ingestion, or in a `transformSpec` in native ingestion as an alternative to
using a [`flattenSpec`](../ingestion/data-formats.md#flattenspec) object to
"flatten" nested data for ingestion.
+
+## Example nested data
+
+The examples in this topic use the data in
[`nested_example_data.json`](https://static.imply.io/data/nested_example_data.json).
The file contains a simple facsimile of an order tracking and shipping table.
+
+When pretty-printed, a sample row in `nested_example_data` looks like this:
+
+```json
+{
+ "time":"2022-6-14T10:32:08Z",
+ "product":"Keyboard",
+ "department":"Computers",
+ "shipTo":{
+ "firstName": "Sandra",
+ "lastName": "Beatty",
+ "address": {
+ "street": "293 Grant Well",
+ "city": "Loischester",
+ "state": "FL",
+ "country": "TV",
+ "postalCode": "88845-0066"
+ },
+ "phoneNumbers": [
+ {"type":"primary","number":"1-788-771-7028 x8627" },
+ {"type":"secondary","number":"1-460-496-4884 x887"}
+ ]
+ },
+ "details"{"color":"plum","price":"40.00"}
+}
+```
+
+## Native batch ingestion
+
+For native batch ingestion, you can use the [JSON nested columns
functions](./sql-json-functions.md) to extract nested data as an alternative to
using the [`flattenSpec`](../ingestion/data-formats.md#flattenspec) input
format.
+
+To configure a dimension as a nested data type, specify the `json` type for
the dimension in the `dimensions` list in the `dimensionsSpec` property of your
ingestion spec.
+
+For example, the following ingestion spec instructs Druid to ingest `shipTo`
and `details` as JSON-type nested dimensions:
+
+```json
+{
+ "type": "index_parallel",
+ "spec": {
+ "ioConfig": {
+ "type": "index_parallel",
+ "inputSource": {
+ "type": "http",
+ "uris": [
+ "https://static.imply.io/data/nested_example_data.json";
+ ]
+ },
+ "inputFormat": {
+ "type": "json"
+ }
+ },
+ "dataSchema": {
+ "granularitySpec": {
+ "segmentGranularity": "day",
+ "queryGranularity": "none",
+ "rollup": false
+ },
+ "dataSource": "nested_data_example",
+ "timestampSpec": {
+ "column": "time",
+ "format": "auto"
+ },
+ "dimensionsSpec": {
+ "dimensions": [
+ "product",
+ "department",
+ {
+ "type": "json",
+ "name": "shipTo"
+ },
+ {
+ "type": "json",
+ "name": "details"
+ }
+ ]
+ },
+ "transformSpec": {}
+ },
+ "tuningConfig": {
+ "type": "index_parallel",
+ "partitionsSpec": {
+ "type": "dynamic"
+ }
+ }
+ }
+}
+```
+
+### Transform data during batch ingestion
+
+You can use the [JSON nested columns functions](./sql-json-functions.md) to
transform JSON data and reference the transformed data in your ingestion spec.
+
+To do this, define the output name and expression in the `transforms` list in
the `transformSpec` object of your ingestion spec.
+
+For example, the following ingestion spec extracts `firstName`, `lastName` and
`address` from `shipTo` and creates a composite JSON object containing
`product`, `details` and `department`.
+
+```json
+{
+ "type": "index_parallel",
+ "spec": {
+ "ioConfig": {
+ "type": "index_parallel",
+ "inputSource": {
+ "type": "http",
+ "uris": [
+ "https://static.imply.io/data/nested_example_data.json";
+ ]
+ },
+ "inputFormat": {
+ "type": "json"
+ }
+ },
+ "dataSchema": {
+ "granularitySpec": {
+ "segmentGranularity": "day",
+ "queryGranularity": "none",
+ "rollup": false
+ },
+ "dataSource": "nested_data_transform_example",
+ "timestampSpec": {
+ "column": "time",
+ "format": "auto"
+ },
+ "dimensionsSpec": {
+ "dimensions": [
+ "firstName",
+ "lastName",
+ {
+ "type": "json",
+ "name": "address"
+ },
+ {
+ "type": "json",
+ "name": "productDetails"
+ }
+ ]
+ },
+ "transformSpec": {
+ "transforms":[
+ { "type":"expression", "name":"firstName",
"expression":"json_value(shipTo, '$.firstName')"},
+ { "type":"expression", "name":"lastName",
"expression":"json_value(shipTo, '$.lastName')"},
+ { "type":"expression", "name":"address",
"expression":"json_query(shipTo, '$.address')"},
+ { "type":"expression", "name":"productDetails",
"expression":"json_object('product', product, 'details', details, 'department',
department)"}
+ ]
+ }
+ },
+ "tuningConfig": {
+ "type": "index_parallel",
+ "partitionsSpec": {
+ "type": "dynamic"
+ }
+ }
+ }
+}
+```
+
+## SQL-based ingestion
+
+To ingest nested data using multi-stage query architecture, specify
`COMPLEX<json>` as the value for `type` when you define the row
signature—`shipTo` and `details` in the following example ingestion spec:
+
+
+
+```sql
+REPLACE INTO msq_nested_data_example OVERWRITE ALL
+SELECT
+ TIME_PARSE("time") as __time,
+ product,
+ department,
+ shipTo,
+ details
+FROM (
+ SELECT * FROM
+ TABLE(
+ EXTERN(
+
'{"type":"http","uris":["https://static.imply.io/data/nested_example_data.json"]}',
+ '{"type":"json"}',
+
'[{"name":"time","type":"string"},{"name":"product","type":"string"},{"name":"department","type":"string"},{"name":"shipTo","type":"COMPLEX<json>"},{"name":"details","type":"COMPLEX<json>"}]'
+ )
+ )
+)
+PARTITIONED BY ALL
+```
+
+### Transform data during SQL-based ingestion
+
+You can use the [JSON nested columns functions](./sql-json-functions.md) to
transform JSON data in your ingestion query.
+
+For example, the following ingestion query is the SQL-based version of the
[previous batch example](#transform-data-during-batch-ingestion)—it
extracts `firstName`, `lastName`, and `address` from `shipTo` and creates a
composite JSON object containing `product`, `details`, and `department`.
+
+
+
+```sql
+REPLACE INTO msq_nested_data_transform_example OVERWRITE ALL
+SELECT
+ TIME_PARSE("time") as __time,
+ JSON_VALUE(shipTo, '$.firstName') as firstName,
+ JSON_VALUE(shipTo, '$.lastName') as lastName,
+ JSON_QUERY(shipTo, '$.address') as address,
+ JSON_OBJECT('product':product,'details':details, 'department':department) as
productDetails
+FROM (
+ SELECT * FROM
+ TABLE(
+ EXTERN(
+
'{"type":"http","uris":["https://static.imply.io/data/nested_example_data.json"]}',
+ '{"type":"json"}',
+
'[{"name":"time","type":"string"},{"name":"product","type":"string"},{"name":"department","type":"string"},{"name":"shipTo","type":"COMPLEX<json>"},{"name":"details","type":"COMPLEX<json>"}]'
+ )
+ )
+)
+PARTITIONED BY ALL
+```
+
+## Ingest a JSON string as COMPLEX\<json>
+
+If your source data uses a string representation of your JSON column, you can
still ingest the data as `COMPLEX<JSON>` as follows:
+- During native batch ingestion, call the `parse_json` function in a
`transform` object in the `transformSpec`.
+- During SQL-based ingestion, use the PARSE_JSON keyword within your SELECT
statement to transform the string values to JSON.
+- If you are concerned that your data may not contain valid JSON, you can use
`try_parse_json` for native batch or `TRY_PARSE_JSON` for SQL-based ingestion.
For cases where the column does not contain valid JSON, Druid inserts a null
value.
+
+If you are using a text input format like `tsv`, you need to use this method
to ingest data into a `COMPLEX<json>` column.
+
+For example, consider the following deserialized row of the sample data set:
+
+```
+{"time": "2022-06-13T10:10:35Z", "product": "Bike", "department":"Sports",
"shipTo":"{\"firstName\": \"Henry\",\"lastName\": \"Wuckert\",\"address\":
{\"street\": \"5643 Jan Walk\",\"city\": \"Lake Bridget\",\"state\":
\"HI\",\"country\":\"ME\",\"postalCode\": \"70204-2939\"},\"phoneNumbers\":
[{\"type\":\"primary\",\"number\":\"593.475.0449 x86733\"
},{\"type\":\"secondary\",\"number\":\"638-372-1210\"}]}",
"details":"{\"color\":\"ivory\", \"price\":955.00}"}
+```
+
+The following examples demonstrate how to ingest the `shipTo` and `details`
columns both as string type and as `COMPLEX<json>` in the `shipTo_parsed` and
`details_parsed` columns.
+
+<!--DOCUSAURUS_CODE_TABS-->
+<!--SQL-->
+```
+REPLACE INTO deserialized_example OVERWRITE ALL
+WITH source AS (SELECT * FROM TABLE(
+ EXTERN(
+ '{"type":"inline","data":"{\"time\": \"2022-06-13T10:10:35Z\",
\"product\": \"Bike\", \"department\":\"Sports\",
\"shipTo\":\"{\\\"firstName\\\": \\\"Henry\\\",\\\"lastName\\\":
\\\"Wuckert\\\",\\\"address\\\": {\\\"street\\\": \\\"5643 Jan
Walk\\\",\\\"city\\\": \\\"Lake Bridget\\\",\\\"state\\\":
\\\"HI\\\",\\\"country\\\":\\\"ME\\\",\\\"postalCode\\\":
\\\"70204-2939\\\"},\\\"phoneNumbers\\\":
[{\\\"type\\\":\\\"primary\\\",\\\"number\\\":\\\"593.475.0449 x86733\\\"
},{\\\"type\\\ [...]
+ '{"type":"json"}',
+
'[{"name":"time","type":"string"},{"name":"product","type":"string"},{"name":"department","type":"string"},{"name":"shipTo","type":"string"},{"name":"details","type":"string"},{"name":"shipTo_parsed","type":"json"},{"name":"details_parsed","type":"json"}]'
+ )
+))
+SELECT
+ TIME_PARSE("time") AS __time,
+ "product",
+ "department",
+ "shipTo",
+ "details",
+ PARSE_JSON("shipTo") as "shipTo_parsed",
+ PARSE_JSON("details") as "details_parsed"
+FROM source
+PARTITIONED BY DAY
+```
+<!--Native batch-->
+```{
+ "type": "index_parallel",
+ "spec": {
+ "ioConfig": {
+ "type": "index_parallel",
+ "inputSource": {
+ "type": "inline",
+ "data": "{\"time\": \"2022-06-13T10:10:35Z\", \"product\": \"Bike\",
\"department\":\"Sports\", \"shipTo\":\"{\\\"firstName\\\":
\\\"Henry\\\",\\\"lastName\\\": \\\"Wuckert\\\",\\\"address\\\":
{\\\"street\\\": \\\"5643 Jan Walk\\\",\\\"city\\\": \\\"Lake
Bridget\\\",\\\"state\\\":
\\\"HI\\\",\\\"country\\\":\\\"ME\\\",\\\"postalCode\\\":
\\\"70204-2939\\\"},\\\"phoneNumbers\\\":
[{\\\"type\\\":\\\"primary\\\",\\\"number\\\":\\\"593.475.0449 x86733\\\"
},{\\\"type\\\":\\\"seconda [...]
+ },
+ "inputFormat": {
+ "type": "json"
+ }
+ },
+ "tuningConfig": {
+ "type": "index_parallel",
+ "partitionsSpec": {
+ "type": "dynamic"
+ }
+ },
+ "dataSchema": {
+ "dataSource": "deserialized_example",
+ "timestampSpec": {
+ "column": "time",
+ "format": "iso"
+ },
+ "transformSpec": {
+ "transforms": [
+ {
+ "type": "expression",
+ "name": "shipTo_parsed",
+ "expression": "parse_json(shipTo)"
+ },
+ {
+ "type": "expression",
+ "name": "details_parsed",
+ "expression": "parse_json(details)"
+ }
+ ]
+ },
+ "dimensionsSpec": {
+ "dimensions": [
+ "product",
+ "department",
+ "shipTo",
+ "details",
+ "shipTo_parsed",
+ "details_parsed"
+ ]
+ },
+ "granularitySpec": {
+ "queryGranularity": "none",
+ "rollup": false,
+ "segmentGranularity": "day"
+```
+<!--END_DOCUSAURUS_CODE_TABS-->
+
+## Querying nested columns
+
+Once ingested, Druid stores the JSON-typed columns as native JSON objects and
presents them as `COMPLEX<json>`.
+
+See the [Nested columns functions reference](./sql-json-functions.md) for
information on the functions in the examples below.
+
+Druid supports a small, simplified subset of the [JSONPath
syntax](https://github.com/json-path/JsonPath/blob/master/README.md) operators,
primarily limited to extracting individual values from nested data structures.
See the [SQL JSON functions](./sql-json-functions.md#jsonpath-syntax) page for
details.
+
+### Displaying data types
+
+The following example illustrates how you can display the data types for your
columns. Note that `details` and `shipTo` display as `COMPLEX<json>`.
+
+#### Example query: Display data types
+
+
+
+```sql
+SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
+FROM INFORMATION_SCHEMA.COLUMNS
+WHERE TABLE_NAME = 'nested_data_example'
+```
+
+Example query results:
+
+```json
+[["TABLE_NAME","COLUMN_NAME","DATA_TYPE"],["STRING","STRING","STRING"],["VARCHAR","VARCHAR","VARCHAR"],["nested_data_example","__time","TIMESTAMP"],["nested_data_example","department","VARCHAR"],["nested_data_example","details","COMPLEX<json>"],["nested_data_example","product","VARCHAR"],["nested_data_example","shipTo","COMPLEX<json>"]]
+```
+
+### Retrieving JSON data
+
+You can retrieve JSON data directly from a table. Druid returns the results as
a JSON object, so you can't use grouping, aggregation, or filtering operators.
+
+#### Example query: Retrieve JSON data
+
+The following example query extracts all data from `nested_data_example`:
+
+
+
+```sql
+SELECT * FROM nested_data_example
+```
+
+Example query results:
+
+```json
+[["__time","department","details","product","shipTo"],["LONG","STRING","COMPLEX<json>","STRING","COMPLEX<json>"],["TIMESTAMP","VARCHAR","OTHER","VARCHAR","OTHER"],["2022-06-13T07:52:29.000Z","Sports","{\"color\":\"sky
blue\",\"price\":542.0}","Bike","{\"firstName\":\"Russ\",\"lastName\":\"Cole\",\"address\":{\"street\":\"77173
Rusty Station\",\"city\":\"South
Yeseniabury\",\"state\":\"WA\",\"country\":\"BL\",\"postalCode\":\"01893\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"8
[...]
+```
+
+### Extracting nested data elements
+
+The `JSON_VALUE` function is specially optimized to provide native Druid level
performance when processing nested literal values, as if they were flattened,
traditional, Druid column types. It does this by reading from the specialized
nested columns and indexes that are built and stored in JSON objects when Druid
creates segments.
+
+Some operations using `JSON_VALUE` run faster than those using native Druid
columns. For example, filtering numeric types uses the indexes built for nested
numeric columns, which are not available for Druid DOUBLE, FLOAT, or LONG
columns.
+
+`JSON_VALUE` only returns literal types. Any paths that reference JSON objects
or array types return null.
+
+> To achieve the best possible performance, use the `JSON_VALUE` function
whenever you query JSON objects.
+
+#### Example query: Extract nested data elements
+
+The following example query illustrates how to use `JSON_VALUE` to extract
specified elements from a `COMPLEX<json>` object. Note that the returned values
default to type VARCHAR.
+
+
+
+```sql
+SELECT
+ product,
+ department,
+ JSON_VALUE(shipTo, '$.address.country') as country,
+ JSON_VALUE(shipTo, '$.phoneNumbers[0].number') as primaryPhone,
+ JSON_VALUE(details, '$.price') as price
+FROM nested_data_example
+```
+
+Example query results:
+
+```json
+[["product","department","country","primaryPhone","price"],["STRING","STRING","STRING","STRING","STRING"],["VARCHAR","VARCHAR","VARCHAR","VARCHAR","VARCHAR"],["Bike","Sports","BL","891-374-6188
x74568","542.0"],["Bike","Sports","ME","593.475.0449
x86733","955.0"],["Sausages","Grocery","AD","(904) 890-0696
x581","8.0"],["Mouse","Computers","CW","(689) 766-4272
x60778","90.0"],["Keyboard","Computers","TV","1-788-771-7028 x8627","40.0"]]
+```
+
+### Extracting nested data elements as a suggested type
+
+You can use the `RETURNING` keyword to provide type hints to the `JSON_VALUE`
function. This way the SQL planner produces the correct native Druid query,
leading to expected results. This keyword allows you to specify a SQL type for
the `path` value.
+
+#### Example query: Extract nested data elements as suggested types
+
+The following example query illustrates how to use `JSON_VALUE` and the
`RETURNING` keyword to extract an element of nested data and return it as
specified types.
+
+
+
+```sql
+SELECT
+ product,
+ department,
+ JSON_VALUE(shipTo, '$.address.country') as country,
+ JSON_VALUE(details, '$.price' RETURNING BIGINT) as price_int,
+ JSON_VALUE(details, '$.price' RETURNING DECIMAL) as price_decimal,
+ JSON_VALUE(details, '$.price' RETURNING VARCHAR) as price_varchar
+FROM nested_data_example
+```
+
+Query results:
+
+```json
+[["product","department","country","price_int","price_decimal","price_varchar"],["STRING","STRING","STRING","LONG","DOUBLE","STRING"],["VARCHAR","VARCHAR","VARCHAR","BIGINT","DECIMAL","VARCHAR"],["Bike","Sports","BL",542,542.0,"542.0"],["Bike","Sports","ME",955,955.0,"955.0"],["Sausages","Grocery","AD",8,8.0,"8.0"],["Mouse","Computers","CW",90,90.0,"90.0"],["Keyboard","Computers","TV",40,40.0,"40.0"]]
+```
+
+### Grouping, aggregating, and filtering
+
+You can use `JSON_VALUE` expressions in any context where you can use
traditional Druid columns, such as grouping, aggregation, and filtering.
+
+#### Example query: Grouping and filtering
+
+The following example query illustrates how to use SUM, WHERE, GROUP BY, and
ORDER BY operators with `JSON_VALUE`.
+
+
+
+```sql
+SELECT
+ product,
+ JSON_VALUE(shipTo, '$.address.country'),
+ SUM(JSON_VALUE(details, '$.price' RETURNING BIGINT))
+FROM nested_data_example
+WHERE JSON_VALUE(shipTo, '$.address.country') in ('BL', 'CW')
+GROUP BY 1,2
+ORDER BY 3 DESC
+```
+
+Example query results:
+
+```json
+[["product","EXPR$1","EXPR$2"],["STRING","STRING","LONG"],["VARCHAR","VARCHAR","BIGINT"],["Bike","BL",542],["Mouse","CW",90]]
+```
+
+### Transforming JSON object data
+
+In addition to `JSON_VALUE`, Druid offers a number of operators that focus on
transforming JSON object data:
+
+- `JSON_QUERY`
+- `JSON_OBJECT`
+- `PARSE_JSON`
+- `TO_JSON_STRING`
+
+These functions are primarily intended for use with the multi-stage query
architecture to transform data during insert operations, but they also work in
traditional Druid SQL queries. Because most of these functions output JSON
objects, they have the same limitations when used in traditional Druid queries
as interacting with the JSON objects directly.
+
+#### Example query: Return results in a JSON object
+
+You can use the `JSON_QUERY` function to extract a partial structure from any
JSON input and return results in a JSON object. Unlike `JSON_VALUE` it can
extract objects and arrays.
+
+The following example query illustrates the differences in output between
`JSON_VALUE` and `JSON_QUERY`. The two output columns for `JSON_VALUE` contain
null values only because `JSON_VALUE` only returns literal types.
+
+
+
+```sql
+SELECT
+ JSON_VALUE(shipTo, '$.address'),
+ JSON_QUERY(shipTo, '$.address'),
+ JSON_VALUE(shipTo, '$.phoneNumbers'),
+ JSON_QUERY(shipTo, '$.phoneNumbers')
+FROM nested_data_example
+```
+
+Example query results:
+
+```json
+[["EXPR$0","EXPR$1","EXPR$2","EXPR$3"],["STRING","COMPLEX<json>","STRING","COMPLEX<json>"],["VARCHAR","OTHER","VARCHAR","OTHER"],["","{\"street\":\"77173
Rusty Station\",\"city\":\"South
Yeseniabury\",\"state\":\"WA\",\"country\":\"BL\",\"postalCode\":\"01893\"}","","[{\"type\":\"primary\",\"number\":\"891-374-6188
x74568\"},{\"type\":\"secondary\",\"number\":\"1-248-998-4426
x33037\"}]"],["","{\"street\":\"5643 Jan Walk\",\"city\":\"Lake
Bridget\",\"state\":\"HI\",\"country\":\"ME\",\"p [...]
+```
+
+#### Example query: Combine multiple JSON inputs into a single JSON object
value
+
+ The following query illustrates how to use `JSON_OBJECT` to combine nested
data elements into a new object.
+
+
+
+```sql
+SELECT
+ JSON_OBJECT(KEY 'shipTo' VALUE JSON_QUERY(shipTo, '$'), KEY 'details' VALUE
JSON_QUERY(details, '$')) as combinedJson
+FROM nested_data_example
+```
+
+Example query results:
+
+```json
+[["combinedJson"],["COMPLEX<json>"],["OTHER"],["{\"details\":{\"color\":\"sky
blue\",\"price\":542.0},\"shipTo\":{\"firstName\":\"Russ\",\"lastName\":\"Cole\",\"address\":{\"street\":\"77173
Rusty Station\",\"city\":\"South
Yeseniabury\",\"state\":\"WA\",\"country\":\"BL\",\"postalCode\":\"01893\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"891-374-6188
x74568\"},{\"type\":\"secondary\",\"number\":\"1-248-998-4426
x33037\"}]}}"],["{\"details\":{\"color\":\"ivory\",\"price\":955 [...]
+```
+
+### Using other transform functions
+
+Druid provides the following additional transform functions:
+
+- `PARSE_JSON`: Deserializes a string value into a JSON object.
+- `TO_JSON_STRING`: Performs the operation of `TO_JSON` and then serializes
the value into a string.
+
+#### Example query: Parse and deserialize data
+
+ The following query illustrates how to use the transform functions to parse
and deserialize data.
+
+
+
+```sql
+SELECT
+ PARSE_JSON('{"x":"y"}'),
+ TO_JSON_STRING('{"x":"y"}'),
+ TO_JSON_STRING(PARSE_JSON('{"x":"y"}'))
+```
+
+Example query results:
+
+```json
+[["EXPR$0","EXPR$2","EXPR$3"],["COMPLEX<json>","STRING","STRING"],["OTHER","VARCHAR","VARCHAR"],["{\"x\":\"y\"}","\"{\\\"x\\\":\\\"y\\\"}\"","{\"x\":\"y\"}"]]
+```
+
+### Using helper operators
+
+The `JSON_KEYS` and `JSON_PATHS` functions are helper operators that you can
use to examine JSON object schema. Use them to plan your queries, for example
to work out which paths to use in `JSON_VALUE`.
+
+#### Example query: Examine JSON object schema
+
+ The following query illustrates how to use the helper operators to examine a
nested data object.
+
+
+
+```sql
+SELECT
+ ARRAY_CONCAT_AGG(DISTINCT JSON_KEYS(shipTo, '$.')),
+ ARRAY_CONCAT_AGG(DISTINCT JSON_KEYS(shipTo, '$.address')),
+ ARRAY_CONCAT_AGG(DISTINCT JSON_PATHS(shipTo))
+FROM nested_data_example
+```
+
+Example query results:
+
+```json
+[["EXPR$0","EXPR$1","EXPR$2","EXPR$3"],["COMPLEX<json>","COMPLEX<json>","STRING","STRING"],["OTHER","OTHER","VARCHAR","VARCHAR"],["{\"x\":\"y\"}","\"{\\\"x\\\":\\\"y\\\"}\"","\"{\\\"x\\\":\\\"y\\\"}\"","{\"x\":\"y\"}"]]
+```
+
+## Known issues
+
+Before you start using the nested columns feature, consider the following
known issues:
+
+- Directly using `COMPLEX<json>` columns and expressions is not well
integrated into the Druid query engine. It can result in errors or undefined
behavior when grouping and filtering, and when you use `COMPLEX<json>` objects
as inputs to aggregators. As a workaround, consider using `TO_JSON_STRING` to
coerce the values to strings before you perform these operations.
+- Directly using array-typed outputs from `JSON_KEYS` and `JSON_PATHS` is
moderately supported by the Druid query engine. You can group on these outputs,
and there are a number of array expressions that can operate on these values,
such as `ARRAY_CONCAT_AGG`. However, some operations are not well defined for
use outside array-specific functions, such as filtering using `=` or `IS NULL`.
+- Input validation for JSON SQL operators is currently incomplete, which
sometimes results in undefined behavior or unhelpful error messages.
+- Ingesting JSON columns with a very complex nested structure is potentially
an expensive operation and may require you to tune ingestion tasks and/or
cluster parameters to account for increased memory usage or overall task run
time. When you tune your ingestion configuration, treat each nested literal
field inside a JSON object as a flattened top-level Druid column.
+
+## Further reading
+
+For more information, see the following pages:
+
+- [Nested columns functions reference](./sql-json-functions.md) for details of
the functions used in the examples on this page.
+- [Multi-stage query architecture overview](../multi-stage-query/index.md) for
information on how to set up and use this feature.
+- [Ingestion spec reference](../ingestion/ingestion-spec.md) for information
on native ingestion and
[`transformSpec`](../ingestion/ingestion-spec.md#transformspec).
+- [Data formats](../ingestion/data-formats.md) for information on
[`flattenSpec`](../ingestion/data-formats.md#flattenspec).
diff --git a/docs/querying/sql-data-types.md b/docs/querying/sql-data-types.md
index bd056dae02..11c8cda0fc 100644
--- a/docs/querying/sql-data-types.md
+++ b/docs/querying/sql-data-types.md
@@ -114,15 +114,15 @@ When `druid.expressions.useStrictBooleans = true`, Druid
uses three-valued logic
However, even in this mode, Druid uses two-valued logic for filter types other
than `expression`.
## Nested columns
-Druid supports storing nested data structures in segments using the native
`COMPLEX<json>` type. You can interact
-with this data using [JSON functions](sql-json-functions.md), which can
extract nested values, parse from string,
-serialize to string, and create new `COMPLEX<json>` structures.
-
-`COMPLEX` types in general currently have limited functionality outside of the
use of the specialized functions which
-understand them, and so have undefined behavior when:
-* grouping on complex values
-* filtered directly on complex values, such as `WHERE json is NULL`
-* used as inputs to aggregators without specialized handling for a specific
complex type
-
-In many cases, functions are provided to translate `COMPLEX` value types to
`STRING`, which serves as a workaround
-solution until `COMPLEX` type functionality can be improved.
\ No newline at end of file
+
+Druid supports storing nested data structures in segments using the native
`COMPLEX<json>` type. See [Nested columns](./nested-columns.md) for more
information.
+
+You can interact with nested data using [JSON
functions](./sql-json-functions.md), which can extract nested values, parse
from string, serialize to string, and create new `COMPLEX<json>` structures.
+
+`COMPLEX` types have limited functionality outside the specialized functions
that use them, so their behavior is undefined when:
+
+* Grouping on complex values.
+* Filtering directly on complex values, such as `WHERE json is NULL`.
+* Used as inputs to aggregators without specialized handling for a specific
complex type.
+
+In many cases, functions are provided to translate `COMPLEX` value types to
`STRING`, which serves as a workaround solution until `COMPLEX` type
functionality can be improved.
diff --git a/docs/querying/sql-json-functions.md
b/docs/querying/sql-json-functions.md
index ee01145988..28140da9d4 100644
--- a/docs/querying/sql-json-functions.md
+++ b/docs/querying/sql-json-functions.md
@@ -30,8 +30,9 @@ sidebar_label: "JSON functions"
patterns in this markdown file and parse it to TypeScript file for web
console
-->
-Druid supports nested columns, which provide optimized storage and indexes for
nested data structures. Use
-the following JSON functions to extract, transform, and create `COMPLEX<json>`
values.
+Druid supports nested columns, which provide optimized storage and indexes for
nested data structures. See [Nested columns](./nested-columns.md) for more
information.
+
+You can use the following JSON functions to extract, transform, and create
`COMPLEX<json>` values.
| Function | Notes |
| --- | --- |
diff --git a/docs/querying/virtual-columns.md b/docs/querying/virtual-columns.md
index d2611582e6..6229d83326 100644
--- a/docs/querying/virtual-columns.md
+++ b/docs/querying/virtual-columns.md
@@ -163,7 +163,7 @@ Specify `pathParts` as an array of objects that describe
each component of the p
|field|The name of the 'field' in a 'field' `type` path part|yes, if `type` is
'field'|
|index|The array element index if `type` is `arrayElement`|yes, if `type` is
'arrayElement'|
-
+See [Nested columns](./nested-columns.md) for more information on ingesting
and storing nested data.
### List filtered virtual column
This virtual column provides an alternative way to use
diff --git a/website/sidebars.json b/website/sidebars.json
index c69ae15e34..e946a6f3ff 100644
--- a/website/sidebars.json
+++ b/website/sidebars.json
@@ -91,6 +91,7 @@
"querying/sql-aggregations",
"querying/sql-multivalue-string-functions",
"querying/sql-functions",
+ "querying/sql-json-functions",
"querying/sql-api",
"querying/sql-jdbc",
"querying/sql-query-context",
@@ -109,6 +110,7 @@
"querying/joins",
"querying/lookups",
"querying/multi-value-dimensions",
+ "querying/nested-columns",
"querying/multitenancy",
"querying/caching",
"querying/using-caching",
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
