clintropolis commented on code in PR #12946: URL: https://github.com/apache/druid/pull/12946#discussion_r953618556
########## docs/querying/nested-columns.md: ########## @@ -0,0 +1,546 @@ +--- +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 that is subject to + ~ change or removal at any time. + --> + +> 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). Review Comment: I think this part is a bit confusing about all of the functionality.. i think it focuses too much on using the transforms as an alternative to flattenSpec, and not enough that this is its own column type which allows storing nested data directly in druid segments and the 'flattening' extraction can instead be done at query time without performance penalty. here is the set of stuff I think is worth mentioning in the lead in ```suggestion Apache Druid supports directly storing nested data structures in `COMPLEX<json>` columns. In addition to storing a copy of the structured data in JSON format, `COMPLEX<json>` columns also store 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 for fast reading and filtering of these values at speeds which are consistent with standard Druid `LONG`, `DOUBLE`, and `STRING` columns. The [Druid SQL JSON functions](./sql-json-functions.md) let you extract, transform, and create `COMPLEX<json>` values in SQL queries, taking advantage of the specialized virtual columns whenever possible. The [JSON nested columns functions](../misc/math-expr.md#nested-columns-functions), can be used 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). When used in MSQ INSERT and REPLACE statements, or native ingestion `transformSpec`, the JSON functions can serve as an alternative to using a [`flattenSpec`](../ingestion/data-formats.md#flattenspec). ``` feel free to remix however needed. if we cover this stuff here, we can dump the description https://github.com/apache/druid/pull/12946/files#diff-1509d7fece1f79b5fc731c62d0fd4f114d48e1c606b3d23a7779e408df4590deR330 and keep it focused on the example (maybe only mentioning that it is one of the functions that uses the special nested field virtual column) ########## docs/querying/nested-columns.md: ########## @@ -0,0 +1,546 @@ +--- +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 that is subject to + ~ change or removal at any time. + --> + +> 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](../misc/math-expr.md#nested-columns-functions) 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](../misc/math-expr.md#nested-columns-functions) 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](../misc/math-expr.md#nested-columns-functions) 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](../misc/math-expr.md#nested-columns-functions) for information on the functions in the examples below. + +### JSONPath syntax + +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. Review Comment: my vote is for sql-json-functions page -- 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]
