clintropolis commented on code in PR #12946: URL: https://github.com/apache/druid/pull/12946#discussion_r954789335
########## docs/querying/nested-columns.md: ########## @@ -0,0 +1,519 @@ +--- +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. As an experimental feature, functionality documented on this page is subject to change or removal in future releases. Review the release notes and this page to stay up to date with changes. + +You can ingest and store nested JSON in an Apache Druid column as a `COMPLEX<json>` data type. Druid indexes and optimizes the nested data. This means you can use JSON functions to extract ‘literal’ values at ingestion time using the `transformSpec` or in the SELECT clause when using multi-stage query architecture. + +Druid SQL JSON functions let you extract, transform, and create `COMPLEX<json>` values. Additionally, you can use certain `JSONPath` operators to extract values from nested data structures. + +Druid currently supports ingesting JSON-format nested columns with this feature. If you want to ingest nested data in another format, consider using the [flattenSpec object](../ingestion/data-formats.md#flattenspec). + +### 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 fascimile 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, include a `dimensions` object 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, include a `transforms` object in the `transformSpec` property 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 column `type` when you define the row signature—`shipTo` and `details` in the following example ingestion spec: + +> Note to self: add screenshot + +<!-----> + +```sql +REPLACE INTO msq_nested_data_example OVERWRITE ALL +SELECT + "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 [batch example above](#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", + 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 +``` + +## 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 + +> Note to self: add screenshot + +<!-----> + +```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`: + +> Note to self: add screenshot + +<!-----> + +```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\":\"891-374-6188 x74568\"},{\"type\":\"secondary\",\"number\":\"1-248-998-4426 x33037\"}]}"],["2022-06-13T10:10:35.000Z","Sports","{\"color\":\"ivory\",\"price\":955.0}","Bike","{\"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\"}]}"],["2022-06-13T1 3:57:38.000Z","Grocery","{\"price\":8.0}","Sausages","{\"firstName\":\"Forrest\",\"lastName\":\"Brekke\",\"address\":{\"street\":\"41548 Collier Divide\",\"city\":\"Wintheiserborough\",\"state\":\"WA\",\"country\":\"AD\",\"postalCode\":\"27577-6784\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"(904) 890-0696 x581\"},{\"type\":\"secondary\",\"number\":\"676.895.6759\"}]}"],["2022-06-13T21:37:06.000Z","Computers","{\"color\":\"olive\",\"price\":90.0}","Mouse","{\"firstName\":\"Rickey\",\"lastName\":\"Rempel\",\"address\":{\"street\":\"6232 Green Glens\",\"city\":\"New Fermin\",\"state\":\"HI\",\"country\":\"CW\",\"postalCode\":\"98912-1195\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"(689) 766-4272 x60778\"},{\"type\":\"secondary\",\"number\":\"375.662.4737 x24707\"}]}"],["2022-06-14T10:32:08.000Z","Computers","{\"color\":\"plum\",\"price\":40.0}","Keyboard","{\"firstName\":\"Sandra\",\"lastName\":\"Beatty\",\"address\":{\"street\":\"293 Grant Well\",\"city\":\"Lo ischester\",\"state\":\"FL\",\"country\":\"TV\",\"postalCode\":\"88845-0066\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"1-788-771-7028 x8627\"},{\"type\":\"secondary\",\"number\":\"1-460-496-4884 x887\"}]}"]] +``` + +### 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. + +> Note to self: add screenshot + +<!-----> + +```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. + +> Note to self: add screenshot + +<!-----> + +```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`. + +> Note to self: add screenshot + +<!-----> + +```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`. + +> Note to self: add screenshot + +<!-----> + +```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","STRING","COMPLEX"],["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\",\"postalCode\":\"70204-2939\"}","","[{\"type\":\"primary\",\"number\":\"593.475.0449 x86733\"},{\"type\":\"secondary\",\"number\":\"638-372-1210\"}]"],["","{\"street\":\"41548 Collier Divide\",\"city\":\"Wintheiserborough\",\"state\":\"WA\",\"country\":\"AD\",\"postalCode\":\"27577-6784\"}","","[{\"type\":\"primary\",\"number\":\"(904) 890-0696 x581\"},{\"type\":\"secondary\",\"number\":\"676.895.6759\"}]"],["","{\"street\":\"6232 Green Glens\",\"city\":\"New Fermin\",\"state\":\"HI\",\"country\":\"CW\",\"postalCod e\":\"98912-1195\"}","","[{\"type\":\"primary\",\"number\":\"(689) 766-4272 x60778\"},{\"type\":\"secondary\",\"number\":\"375.662.4737 x24707\"}]"],["","{\"street\":\"293 Grant Well\",\"city\":\"Loischester\",\"state\":\"FL\",\"country\":\"TV\",\"postalCode\":\"88845-0066\"}","","[{\"type\":\"primary\",\"number\":\"1-788-771-7028 x8627\"},{\"type\":\"secondary\",\"number\":\"1-460-496-4884 x887\"}]"]] Review Comment: ```suggestion [["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\",\"postalCode\":\"70204-2939\"}","","[{\"type\":\"primary\",\"number\":\"593.475.0449 x86733\"},{\"type\":\"secondary\",\"number\":\"638-372-1210\"}]"],["","{\"street\":\"41548 Collier Divide\",\"city\":\"Wintheiserborough\",\"state\":\"WA\",\"country\":\"AD\",\"postalCode\":\"27577-6784\"}","","[{\"type\":\"primary\",\"number\":\"(904) 890-0696 x581\"},{\"type\":\"secondary\",\"number\":\"676.895.6759\"}]"],["","{\"street\":\"6232 Green Glens\",\"city\":\"New Fermin\",\"state\":\"HI\",\"country\":\"CW \",\"postalCode\":\"98912-1195\"}","","[{\"type\":\"primary\",\"number\":\"(689) 766-4272 x60778\"},{\"type\":\"secondary\",\"number\":\"375.662.4737 x24707\"}]"],["","{\"street\":\"293 Grant Well\",\"city\":\"Loischester\",\"state\":\"FL\",\"country\":\"TV\",\"postalCode\":\"88845-0066\"}","","[{\"type\":\"primary\",\"number\":\"1-788-771-7028 x8627\"},{\"type\":\"secondary\",\"number\":\"1-460-496-4884 x887\"}]"]] ``` ########## docs/querying/nested-columns.md: ########## @@ -0,0 +1,519 @@ +--- +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. As an experimental feature, functionality documented on this page is subject to change or removal in future releases. Review the release notes and this page to stay up to date with changes. + +You can ingest and store nested JSON in an Apache Druid column as a `COMPLEX<json>` data type. Druid indexes and optimizes the nested data. This means you can use JSON functions to extract ‘literal’ values at ingestion time using the `transformSpec` or in the SELECT clause when using multi-stage query architecture. + +Druid SQL JSON functions let you extract, transform, and create `COMPLEX<json>` values. Additionally, you can use certain `JSONPath` operators to extract values from nested data structures. + +Druid currently supports ingesting JSON-format nested columns with this feature. If you want to ingest nested data in another format, consider using the [flattenSpec object](../ingestion/data-formats.md#flattenspec). + +### 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 fascimile 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, include a `dimensions` object 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, include a `transforms` object in the `transformSpec` property 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 column `type` when you define the row signature—`shipTo` and `details` in the following example ingestion spec: + +> Note to self: add screenshot + +<!-----> + +```sql +REPLACE INTO msq_nested_data_example OVERWRITE ALL +SELECT + "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 [batch example above](#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", + 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 +``` + +## 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 + +> Note to self: add screenshot + +<!-----> + +```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`: + +> Note to self: add screenshot + +<!-----> + +```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\":\"891-374-6188 x74568\"},{\"type\":\"secondary\",\"number\":\"1-248-998-4426 x33037\"}]}"],["2022-06-13T10:10:35.000Z","Sports","{\"color\":\"ivory\",\"price\":955.0}","Bike","{\"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\"}]}"],["2022-06-13T1 3:57:38.000Z","Grocery","{\"price\":8.0}","Sausages","{\"firstName\":\"Forrest\",\"lastName\":\"Brekke\",\"address\":{\"street\":\"41548 Collier Divide\",\"city\":\"Wintheiserborough\",\"state\":\"WA\",\"country\":\"AD\",\"postalCode\":\"27577-6784\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"(904) 890-0696 x581\"},{\"type\":\"secondary\",\"number\":\"676.895.6759\"}]}"],["2022-06-13T21:37:06.000Z","Computers","{\"color\":\"olive\",\"price\":90.0}","Mouse","{\"firstName\":\"Rickey\",\"lastName\":\"Rempel\",\"address\":{\"street\":\"6232 Green Glens\",\"city\":\"New Fermin\",\"state\":\"HI\",\"country\":\"CW\",\"postalCode\":\"98912-1195\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"(689) 766-4272 x60778\"},{\"type\":\"secondary\",\"number\":\"375.662.4737 x24707\"}]}"],["2022-06-14T10:32:08.000Z","Computers","{\"color\":\"plum\",\"price\":40.0}","Keyboard","{\"firstName\":\"Sandra\",\"lastName\":\"Beatty\",\"address\":{\"street\":\"293 Grant Well\",\"city\":\"Lo ischester\",\"state\":\"FL\",\"country\":\"TV\",\"postalCode\":\"88845-0066\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"1-788-771-7028 x8627\"},{\"type\":\"secondary\",\"number\":\"1-460-496-4884 x887\"}]}"]] +``` + +### 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. + +> Note to self: add screenshot + +<!-----> + +```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. + +> Note to self: add screenshot + +<!-----> + +```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`. + +> Note to self: add screenshot + +<!-----> + +```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`. + +> Note to self: add screenshot + +<!-----> + +```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","STRING","COMPLEX"],["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\",\"postalCode\":\"70204-2939\"}","","[{\"type\":\"primary\",\"number\":\"593.475.0449 x86733\"},{\"type\":\"secondary\",\"number\":\"638-372-1210\"}]"],["","{\"street\":\"41548 Collier Divide\",\"city\":\"Wintheiserborough\",\"state\":\"WA\",\"country\":\"AD\",\"postalCode\":\"27577-6784\"}","","[{\"type\":\"primary\",\"number\":\"(904) 890-0696 x581\"},{\"type\":\"secondary\",\"number\":\"676.895.6759\"}]"],["","{\"street\":\"6232 Green Glens\",\"city\":\"New Fermin\",\"state\":\"HI\",\"country\":\"CW\",\"postalCod e\":\"98912-1195\"}","","[{\"type\":\"primary\",\"number\":\"(689) 766-4272 x60778\"},{\"type\":\"secondary\",\"number\":\"375.662.4737 x24707\"}]"],["","{\"street\":\"293 Grant Well\",\"city\":\"Loischester\",\"state\":\"FL\",\"country\":\"TV\",\"postalCode\":\"88845-0066\"}","","[{\"type\":\"primary\",\"number\":\"1-788-771-7028 x8627\"},{\"type\":\"secondary\",\"number\":\"1-460-496-4884 x887\"}]"]] +``` + +#### 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. + + > Note to self: add screenshot + +<!-----> + +```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"],["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.0},\"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\":{\"price\":8.0},\"shipTo\":{\"firstName\":\"Forrest\",\"lastName\":\"Brekke\",\"address\":{\"street\":\"41548 Collier Divide\",\"city\":\"Wintheiserborough\",\"st ate\":\"WA\",\"country\":\"AD\",\"postalCode\":\"27577-6784\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"(904) 890-0696 x581\"},{\"type\":\"secondary\",\"number\":\"676.895.6759\"}]}}"],["{\"details\":{\"color\":\"olive\",\"price\":90.0},\"shipTo\":{\"firstName\":\"Rickey\",\"lastName\":\"Rempel\",\"address\":{\"street\":\"6232 Green Glens\",\"city\":\"New Fermin\",\"state\":\"HI\",\"country\":\"CW\",\"postalCode\":\"98912-1195\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"(689) 766-4272 x60778\"},{\"type\":\"secondary\",\"number\":\"375.662.4737 x24707\"}]}}"],["{\"details\":{\"color\":\"plum\",\"price\":40.0},\"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\"}]}}"]] +``` + +### 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. + +> Note to self: add screenshot + +<!-----> + +```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","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. + +> Note to self: add screenshot + +<!-----> + +```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","COMPLEX","STRING","STRING"],["OTHER","OTHER","VARCHAR","VARCHAR"],["{\"x\":\"y\"}","\"{\\\"x\\\":\\\"y\\\"}\"","\"{\\\"x\\\":\\\"y\\\"}\"","{\"x\":\"y\"}"]] Review Comment: ```suggestion [["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\"}"]] ``` ########## docs/querying/nested-columns.md: ########## @@ -0,0 +1,519 @@ +--- +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. As an experimental feature, functionality documented on this page is subject to change or removal in future releases. Review the release notes and this page to stay up to date with changes. + +You can ingest and store nested JSON in an Apache Druid column as a `COMPLEX<json>` data type. Druid indexes and optimizes the nested data. This means you can use JSON functions to extract ‘literal’ values at ingestion time using the `transformSpec` or in the SELECT clause when using multi-stage query architecture. + +Druid SQL JSON functions let you extract, transform, and create `COMPLEX<json>` values. Additionally, you can use certain `JSONPath` operators to extract values from nested data structures. + +Druid currently supports ingesting JSON-format nested columns with this feature. If you want to ingest nested data in another format, consider using the [flattenSpec object](../ingestion/data-formats.md#flattenspec). + +### 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 fascimile 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, include a `dimensions` object 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, include a `transforms` object in the `transformSpec` property 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 column `type` when you define the row signature—`shipTo` and `details` in the following example ingestion spec: + +> Note to self: add screenshot + +<!-----> + +```sql +REPLACE INTO msq_nested_data_example OVERWRITE ALL +SELECT + "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 [batch example above](#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", + 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 +``` + +## 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 + +> Note to self: add screenshot + +<!-----> + +```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`: + +> Note to self: add screenshot + +<!-----> + +```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\":\"891-374-6188 x74568\"},{\"type\":\"secondary\",\"number\":\"1-248-998-4426 x33037\"}]}"],["2022-06-13T10:10:35.000Z","Sports","{\"color\":\"ivory\",\"price\":955.0}","Bike","{\"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\"}]}"],["2022-06-13T1 3:57:38.000Z","Grocery","{\"price\":8.0}","Sausages","{\"firstName\":\"Forrest\",\"lastName\":\"Brekke\",\"address\":{\"street\":\"41548 Collier Divide\",\"city\":\"Wintheiserborough\",\"state\":\"WA\",\"country\":\"AD\",\"postalCode\":\"27577-6784\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"(904) 890-0696 x581\"},{\"type\":\"secondary\",\"number\":\"676.895.6759\"}]}"],["2022-06-13T21:37:06.000Z","Computers","{\"color\":\"olive\",\"price\":90.0}","Mouse","{\"firstName\":\"Rickey\",\"lastName\":\"Rempel\",\"address\":{\"street\":\"6232 Green Glens\",\"city\":\"New Fermin\",\"state\":\"HI\",\"country\":\"CW\",\"postalCode\":\"98912-1195\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"(689) 766-4272 x60778\"},{\"type\":\"secondary\",\"number\":\"375.662.4737 x24707\"}]}"],["2022-06-14T10:32:08.000Z","Computers","{\"color\":\"plum\",\"price\":40.0}","Keyboard","{\"firstName\":\"Sandra\",\"lastName\":\"Beatty\",\"address\":{\"street\":\"293 Grant Well\",\"city\":\"Lo ischester\",\"state\":\"FL\",\"country\":\"TV\",\"postalCode\":\"88845-0066\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"1-788-771-7028 x8627\"},{\"type\":\"secondary\",\"number\":\"1-460-496-4884 x887\"}]}"]] +``` + +### 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. + +> Note to self: add screenshot + +<!-----> + +```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. + +> Note to self: add screenshot + +<!-----> + +```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`. + +> Note to self: add screenshot + +<!-----> + +```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`. + +> Note to self: add screenshot + +<!-----> + +```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","STRING","COMPLEX"],["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\",\"postalCode\":\"70204-2939\"}","","[{\"type\":\"primary\",\"number\":\"593.475.0449 x86733\"},{\"type\":\"secondary\",\"number\":\"638-372-1210\"}]"],["","{\"street\":\"41548 Collier Divide\",\"city\":\"Wintheiserborough\",\"state\":\"WA\",\"country\":\"AD\",\"postalCode\":\"27577-6784\"}","","[{\"type\":\"primary\",\"number\":\"(904) 890-0696 x581\"},{\"type\":\"secondary\",\"number\":\"676.895.6759\"}]"],["","{\"street\":\"6232 Green Glens\",\"city\":\"New Fermin\",\"state\":\"HI\",\"country\":\"CW\",\"postalCod e\":\"98912-1195\"}","","[{\"type\":\"primary\",\"number\":\"(689) 766-4272 x60778\"},{\"type\":\"secondary\",\"number\":\"375.662.4737 x24707\"}]"],["","{\"street\":\"293 Grant Well\",\"city\":\"Loischester\",\"state\":\"FL\",\"country\":\"TV\",\"postalCode\":\"88845-0066\"}","","[{\"type\":\"primary\",\"number\":\"1-788-771-7028 x8627\"},{\"type\":\"secondary\",\"number\":\"1-460-496-4884 x887\"}]"]] +``` + +#### 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. + + > Note to self: add screenshot + +<!-----> + +```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"],["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.0},\"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\":{\"price\":8.0},\"shipTo\":{\"firstName\":\"Forrest\",\"lastName\":\"Brekke\",\"address\":{\"street\":\"41548 Collier Divide\",\"city\":\"Wintheiserborough\",\"st ate\":\"WA\",\"country\":\"AD\",\"postalCode\":\"27577-6784\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"(904) 890-0696 x581\"},{\"type\":\"secondary\",\"number\":\"676.895.6759\"}]}}"],["{\"details\":{\"color\":\"olive\",\"price\":90.0},\"shipTo\":{\"firstName\":\"Rickey\",\"lastName\":\"Rempel\",\"address\":{\"street\":\"6232 Green Glens\",\"city\":\"New Fermin\",\"state\":\"HI\",\"country\":\"CW\",\"postalCode\":\"98912-1195\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"(689) 766-4272 x60778\"},{\"type\":\"secondary\",\"number\":\"375.662.4737 x24707\"}]}}"],["{\"details\":{\"color\":\"plum\",\"price\":40.0},\"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\"}]}}"]] +``` + +### 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. + +> Note to self: add screenshot + +<!-----> + +```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","STRING","STRING"],["OTHER","VARCHAR","VARCHAR"],["{\"x\":\"y\"}","\"{\\\"x\\\":\\\"y\\\"}\"","{\"x\":\"y\"}"]] Review Comment: ```suggestion [["EXPR$0","EXPR$2","EXPR$3"],["COMPLEX<json>","STRING","STRING"],["OTHER","VARCHAR","VARCHAR"],["{\"x\":\"y\"}","\"{\\\"x\\\":\\\"y\\\"}\"","{\"x\":\"y\"}"]] ``` ########## docs/querying/nested-columns.md: ########## @@ -0,0 +1,519 @@ +--- +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. As an experimental feature, functionality documented on this page is subject to change or removal in future releases. Review the release notes and this page to stay up to date with changes. + +You can ingest and store nested JSON in an Apache Druid column as a `COMPLEX<json>` data type. Druid indexes and optimizes the nested data. This means you can use JSON functions to extract ‘literal’ values at ingestion time using the `transformSpec` or in the SELECT clause when using multi-stage query architecture. + +Druid SQL JSON functions let you extract, transform, and create `COMPLEX<json>` values. Additionally, you can use certain `JSONPath` operators to extract values from nested data structures. + +Druid currently supports ingesting JSON-format nested columns with this feature. If you want to ingest nested data in another format, consider using the [flattenSpec object](../ingestion/data-formats.md#flattenspec). + +### 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 fascimile 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, include a `dimensions` object 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, include a `transforms` object in the `transformSpec` property 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 column `type` when you define the row signature—`shipTo` and `details` in the following example ingestion spec: + +> Note to self: add screenshot + +<!-----> + +```sql +REPLACE INTO msq_nested_data_example OVERWRITE ALL +SELECT + "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 [batch example above](#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", + 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 +``` + +## 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 + +> Note to self: add screenshot + +<!-----> + +```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`: + +> Note to self: add screenshot + +<!-----> + +```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\":\"891-374-6188 x74568\"},{\"type\":\"secondary\",\"number\":\"1-248-998-4426 x33037\"}]}"],["2022-06-13T10:10:35.000Z","Sports","{\"color\":\"ivory\",\"price\":955.0}","Bike","{\"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\"}]}"],["2022-06-13T1 3:57:38.000Z","Grocery","{\"price\":8.0}","Sausages","{\"firstName\":\"Forrest\",\"lastName\":\"Brekke\",\"address\":{\"street\":\"41548 Collier Divide\",\"city\":\"Wintheiserborough\",\"state\":\"WA\",\"country\":\"AD\",\"postalCode\":\"27577-6784\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"(904) 890-0696 x581\"},{\"type\":\"secondary\",\"number\":\"676.895.6759\"}]}"],["2022-06-13T21:37:06.000Z","Computers","{\"color\":\"olive\",\"price\":90.0}","Mouse","{\"firstName\":\"Rickey\",\"lastName\":\"Rempel\",\"address\":{\"street\":\"6232 Green Glens\",\"city\":\"New Fermin\",\"state\":\"HI\",\"country\":\"CW\",\"postalCode\":\"98912-1195\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"(689) 766-4272 x60778\"},{\"type\":\"secondary\",\"number\":\"375.662.4737 x24707\"}]}"],["2022-06-14T10:32:08.000Z","Computers","{\"color\":\"plum\",\"price\":40.0}","Keyboard","{\"firstName\":\"Sandra\",\"lastName\":\"Beatty\",\"address\":{\"street\":\"293 Grant Well\",\"city\":\"Lo ischester\",\"state\":\"FL\",\"country\":\"TV\",\"postalCode\":\"88845-0066\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"1-788-771-7028 x8627\"},{\"type\":\"secondary\",\"number\":\"1-460-496-4884 x887\"}]}"]] +``` + +### 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. + +> Note to self: add screenshot + +<!-----> + +```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. + +> Note to self: add screenshot + +<!-----> + +```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`. + +> Note to self: add screenshot + +<!-----> + +```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`. + +> Note to self: add screenshot + +<!-----> + +```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","STRING","COMPLEX"],["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\",\"postalCode\":\"70204-2939\"}","","[{\"type\":\"primary\",\"number\":\"593.475.0449 x86733\"},{\"type\":\"secondary\",\"number\":\"638-372-1210\"}]"],["","{\"street\":\"41548 Collier Divide\",\"city\":\"Wintheiserborough\",\"state\":\"WA\",\"country\":\"AD\",\"postalCode\":\"27577-6784\"}","","[{\"type\":\"primary\",\"number\":\"(904) 890-0696 x581\"},{\"type\":\"secondary\",\"number\":\"676.895.6759\"}]"],["","{\"street\":\"6232 Green Glens\",\"city\":\"New Fermin\",\"state\":\"HI\",\"country\":\"CW\",\"postalCod e\":\"98912-1195\"}","","[{\"type\":\"primary\",\"number\":\"(689) 766-4272 x60778\"},{\"type\":\"secondary\",\"number\":\"375.662.4737 x24707\"}]"],["","{\"street\":\"293 Grant Well\",\"city\":\"Loischester\",\"state\":\"FL\",\"country\":\"TV\",\"postalCode\":\"88845-0066\"}","","[{\"type\":\"primary\",\"number\":\"1-788-771-7028 x8627\"},{\"type\":\"secondary\",\"number\":\"1-460-496-4884 x887\"}]"]] +``` + +#### 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. + + > Note to self: add screenshot + +<!-----> + +```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"],["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.0},\"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\":{\"price\":8.0},\"shipTo\":{\"firstName\":\"Forrest\",\"lastName\":\"Brekke\",\"address\":{\"street\":\"41548 Collier Divide\",\"city\":\"Wintheiserborough\",\"st ate\":\"WA\",\"country\":\"AD\",\"postalCode\":\"27577-6784\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"(904) 890-0696 x581\"},{\"type\":\"secondary\",\"number\":\"676.895.6759\"}]}}"],["{\"details\":{\"color\":\"olive\",\"price\":90.0},\"shipTo\":{\"firstName\":\"Rickey\",\"lastName\":\"Rempel\",\"address\":{\"street\":\"6232 Green Glens\",\"city\":\"New Fermin\",\"state\":\"HI\",\"country\":\"CW\",\"postalCode\":\"98912-1195\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"(689) 766-4272 x60778\"},{\"type\":\"secondary\",\"number\":\"375.662.4737 x24707\"}]}}"],["{\"details\":{\"color\":\"plum\",\"price\":40.0},\"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\"}]}}"]] Review Comment: ```suggestion [["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.0},\"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\":{\"price\":8.0},\"shipTo\":{\"firstName\":\"Forrest\",\"lastName\":\"Brekke\",\"address\":{\"street\":\"41548 Collier Divide\",\"city\":\"Wintheiserboroug h\",\"state\":\"WA\",\"country\":\"AD\",\"postalCode\":\"27577-6784\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"(904) 890-0696 x581\"},{\"type\":\"secondary\",\"number\":\"676.895.6759\"}]}}"],["{\"details\":{\"color\":\"olive\",\"price\":90.0},\"shipTo\":{\"firstName\":\"Rickey\",\"lastName\":\"Rempel\",\"address\":{\"street\":\"6232 Green Glens\",\"city\":\"New Fermin\",\"state\":\"HI\",\"country\":\"CW\",\"postalCode\":\"98912-1195\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"(689) 766-4272 x60778\"},{\"type\":\"secondary\",\"number\":\"375.662.4737 x24707\"}]}}"],["{\"details\":{\"color\":\"plum\",\"price\":40.0},\"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\"}]}}"]] ``` -- 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]
