vtlim commented on code in PR #12922:
URL: https://github.com/apache/druid/pull/12922#discussion_r949686393
##########
docs/querying/sql-data-types.md:
##########
@@ -112,3 +112,10 @@ When `druid.expressions.useStrictBooleans = false` (the
default mode), Druid use
When `druid.expressions.useStrictBooleans = true`, Druid uses three-valued
logic for
[expressions](../misc/math-expr.md) evaluation, such as `expression` virtual
columns or `expression` filters.
However, even in this mode, Druid uses two-valued logic for filter types other
than `expression`.
+
+## Nested columns
+Druid `COMPLEX<json>` types can be interacted with using [JSON
functions](sql-json-functions.md), which can perform
+nested value extraction, transforms, and create new `COMPLEX<json>`
structures. `COMPLEX` types currently have
Review Comment:
```suggestion
Druid `COMPLEX<json>` types interact with [JSON
functions](sql-json-functions.md), which can extract nested values,
parse and deserialize data, and create new `COMPLEX<json>` structures.
`COMPLEX` types currently have
```
##########
docs/querying/sql-functions.md:
##########
@@ -647,6 +647,46 @@ Parses `address` into an IPv4 address stored as an integer.
Converts `address` into an IPv4 address in dot-decimal notation.
+## JSON_KEYS
+
+**Function type:** [JSON](sql-json-functions.md)
+
+`JSON_KEYS(expr, path)`
+
+Returns an array of field names in a `COMPLEX<json>` typed `expr`, at the
specified `path`.
+
+## JSON_OBJECT
+
+**Function type:** [JSON](sql-json-functions.md)
+
+`JSON_OBJECT(KEY expr1 VALUE expr2[, KEY expr3 VALUE expr4, ...])`
+
+Constructs a new `COMPLEX<json>` object. The `KEY` expressions must evaluate
to string types, but the `VALUE` expressions can be composed of any input type,
including other `COMPLEX<json>` values.
+
+## JSON_PATHS
+
+**Function type:** [JSON](sql-json-functions.md)
+
+`JSON_PATHS(expr)`
+
+Returns an array of all paths which refer to literal values in a
`COMPLEX<json>` typed `expr`, in JSONPath format.
Review Comment:
```suggestion
Returns an array of all paths which refer to literal values in a
`COMPLEX<json>`-typed `expr`, in JSONPath format.
```
##########
web-console/script/create-sql-docs.js:
##########
@@ -63,6 +63,7 @@ const readDoc = async () => {
await fs.readFile('../docs/querying/sql-scalar.md', 'utf-8'),
await fs.readFile('../docs/querying/sql-aggregations.md', 'utf-8'),
await fs.readFile('../docs/querying/sql-multivalue-string-functions.md',
'utf-8'),
+ await fs.readFile('../docs/querying/sql-json-functions.md', 'utf-8'),
Review Comment:
Not sure if `MINIMUM_EXPECTED_NUMBER_OF_FUNCTIONS` should be updated as
well? (L26)
##########
docs/querying/virtual-columns.md:
##########
@@ -80,4 +82,106 @@ The expression virtual column has the following syntax:
|--------|-----------|---------|
|name|The name of the virtual column.|yes|
|expression|An [expression](../misc/math-expr.md) that takes a row as input
and outputs a value for the virtual column.|yes|
-|outputType|The expression's output will be coerced to this type. Can be LONG,
FLOAT, DOUBLE, or STRING.|no, default is FLOAT|
+|outputType|The expression's output will be coerced to this type. Can be LONG,
FLOAT, DOUBLE, STRING, ARRAY types, or COMPLEX types.|no, default is FLOAT|
+
+
+### Nested field virtual column
+
+The nested field virtual column is an optimized virtual column that can
provide direct access into various paths of
+a `COMPLEX<json>` column, including using their indexes.
+
+Syntax (all 3 of these virtual columns produce the same output):
+```json
+ {
+ "type": "nested-field",
+ "columnName": "shipTo",
+ "outputName": "v0",
+ "expectedType": "STRING",
+ "path": "$.phoneNumbers[1].number"
+ }
+```
+```json
+ {
+ "type": "nested-field",
+ "columnName": "shipTo",
+ "outputName": "v1",
+ "expectedType": "STRING",
+ "path": ".phoneNumbers[1].number",
+ "useJqSyntax": true
+ }
+```
+
+```json
+ {
+ "type": "nested-field",
+ "columnName": "shipTo",
+ "outputName": "v2",
+ "expectedType": "STRING",
+ "pathParts": [
+ {
+ "type": "field",
+ "field": "phoneNumbers"
+ },
+ {
+ "type": "arrayElement",
+ "index": 1
+ },
+ {
+ "type": "field",
+ "field": "number"
+ }
+ ]
+ }
+```
+
+|property|description|required?|
+|--------|-----------|---------|
+|columnName|The name of the virtual column.|yes|
+|outputName|The name of the virtual column.|yes|
+|expectedType|The name of the virtual column.|yes|
+|pathParts|The name of the virtual column.|yes|
+|processFromRaw|If set to true, the virtual column will process the "raw" JSON
data to extract values rather than using an optimized "literal" value selector.
This option allows extracting non-literal values (such as nested JSON objects
or arrays) as a `COMPLEX<json>` at the cost of much slower performance.|No,
default false|
+|path|'JSONPath' or 'jq' syntax path. One of `path` or `pathParts` must be
set|no, if `pathParts` is defined|
+|useJqSyntax||no, default is false|
+
+#### Nested path part
+|property|description|required?|
+|--------|-----------|---------|
+|type|Must be 'field' or 'arrayElement'|yes|
+|field|The name of the 'field' in a 'field' `type` path part|yes, if `type` is
'field'|
+|index|The array element index if `type` is `arrayElement`|yes, if `type` is
'arrayElement'|
+
+This virtual column is used for the SQL operators `JSON_VALUE` (if
`processFromRaw` is set to false) or `JSON_QUERY`
+(if it is true), and accepts 'JSONPath' or 'jq' syntax string representations
of paths, or a parsed
+list of "path parts" in order to determine what should be selected from the
column.
+
+Type information for nested fields is absent at higher levels (it is contained
within the segment, but not to segment
+metadata queries or the SQL planner), so `expectedType` provides the context
for how something is being used, e.g. an
+aggregators default type or an explicit cast, or, if using the 'RETURNING'
syntax which explicitly specifies type.
Review Comment:
```suggestion
aggregators default type or an explicit cast, or, if using the 'RETURNING'
syntax in `JSON_VALUE` to explicitly specify type.
```
##########
docs/querying/sql-functions.md:
##########
@@ -647,6 +647,46 @@ Parses `address` into an IPv4 address stored as an integer.
Converts `address` into an IPv4 address in dot-decimal notation.
+## JSON_KEYS
+
+**Function type:** [JSON](sql-json-functions.md)
+
+`JSON_KEYS(expr, path)`
+
+Returns an array of field names in a `COMPLEX<json>` typed `expr`, at the
specified `path`.
Review Comment:
```suggestion
Returns an array of field names from a `COMPLEX<json>`-typed `expr` at the
specified `path`.
```
##########
docs/querying/sql-data-types.md:
##########
@@ -33,7 +33,7 @@ Columns in Druid are associated with a specific data type.
This topic describes
Druid natively supports five basic column types: "long" (64 bit signed int),
"float" (32 bit float), "double" (64 bit
float) "string" (UTF-8 encoded strings and string arrays), and "complex"
(catch-all for more exotic data types like
-hyperUnique and approxHistogram columns).
+json, hyperUnique, and approxHistogram columns).
Review Comment:
Should this include sketches too? (I realize the current phrasing does not
provide a complete list but it would be nice to have that at some point)
##########
docs/misc/math-expr.md:
##########
@@ -227,6 +226,34 @@ map((x) -> x + 1, x)
```
in this case, the `x` when evaluating `x + 1` is the lambda argument, thus an
element of the multi-valued column `x`, rather than the column `x` itself.
+
+## JSON functions
+JSON functions provide facilities to extract, transform, and create
`COMPLEX<json>` values.
+
+| function | description |
+|---|---|
+| json_value(expr, path) | Extract a Druid literal (`STRING`, `LONG`,
`DOUBLE`) value from a `COMPLEX<json>` column or input `expr` using JSONPath
syntax of `path` |
+| json_query(expr, path) | Extract a `COMPLEX<json>` value from a
`COMPLEX<json>` column or input `expr` using JSONPath syntax of `path` |
+| json_object(expr1, expr2[, expr3, expr4 ...]) | Construct a `COMPLEX<json>`
with alternating 'key' and 'value' arguments|
+| parse_json(expr) | Deserialize a JSON `STRING` into a `COMPLEX<json>` to be
used with expressions which operate on `COMPLEX<json>` inputs. Non-`STRING`
input or invalid JSON will result in an error. |
+| try_parse_json(expr) | Deserialize a JSON `STRING` into a `COMPLEX<json>` to
be used with expressions which operate on `COMPLEX<json>` inputs. Non-`STRING`
input or invalid JSON will result in a `NULL` value. |
+| to_json_string(expr) | Convert a `COMPLEX<json>` input into a JSON `STRING`
value |
+| json_keys(expr, path) | get array of field names in `expr` at the specified
JSONPath `path`, or null if the data does not exist or have any fields |
+| json_paths(expr) | get array of all JSONPath paths available in `expr` |
+
+### 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:
```suggestion
Druid supports a 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.
```
##########
docs/querying/sql-functions.md:
##########
@@ -647,6 +647,46 @@ Parses `address` into an IPv4 address stored as an integer.
Converts `address` into an IPv4 address in dot-decimal notation.
+## JSON_KEYS
+
+**Function type:** [JSON](sql-json-functions.md)
+
+`JSON_KEYS(expr, path)`
+
+Returns an array of field names in a `COMPLEX<json>` typed `expr`, at the
specified `path`.
+
+## JSON_OBJECT
+
+**Function type:** [JSON](sql-json-functions.md)
+
+`JSON_OBJECT(KEY expr1 VALUE expr2[, KEY expr3 VALUE expr4, ...])`
+
+Constructs a new `COMPLEX<json>` object. The `KEY` expressions must evaluate
to string types, but the `VALUE` expressions can be composed of any input type,
including other `COMPLEX<json>` values.
+
+## JSON_PATHS
+
+**Function type:** [JSON](sql-json-functions.md)
+
+`JSON_PATHS(expr)`
+
+Returns an array of all paths which refer to literal values in a
`COMPLEX<json>` typed `expr`, in JSONPath format.
+
+## JSON_QUERY
+
+**Function type:** [JSON](sql-json-functions.md)
+
+`JSON_QUERY(expr, path)`
+
+Extracts a `COMPLEX<json>` value from a `COMPLEX<json>` typed `expr`, at the
specified `path`.
+
+## JSON_VALUE
+
+**Function type:** [JSON](sql-json-functions.md)
+
+`JSON_VALUE(expr, path [RETURNING sqlType])`
+
+Extracts a literal value from a `COMPLEX<json>` typed `expr`, at the specified
`path`. If you specify `RETURNING` and an SQL type name (such as varchar,
bigint, decimal, or double) the function plans the query using the suggested
type. Otherwise it attempts to infer the type based on the context. If it can't
infer the type, it defaults to varchar.
Review Comment:
```suggestion
Extracts a literal value from a `COMPLEX<json>`-typed `expr`, at the
specified `path`. If you specify `RETURNING` and an [SQL data
type](sql-data-types.md) (such as VARCHAR, BIGINT, DECIMAL, or DOUBLE) the
function plans the query using the suggested type. Otherwise it attempts to
infer the type based on the context. The default return type is VARCHAR.
```
##########
docs/querying/sql-functions.md:
##########
@@ -647,6 +647,46 @@ Parses `address` into an IPv4 address stored as an integer.
Converts `address` into an IPv4 address in dot-decimal notation.
+## JSON_KEYS
+
+**Function type:** [JSON](sql-json-functions.md)
+
+`JSON_KEYS(expr, path)`
+
+Returns an array of field names in a `COMPLEX<json>` typed `expr`, at the
specified `path`.
+
+## JSON_OBJECT
+
+**Function type:** [JSON](sql-json-functions.md)
+
+`JSON_OBJECT(KEY expr1 VALUE expr2[, KEY expr3 VALUE expr4, ...])`
+
+Constructs a new `COMPLEX<json>` object. The `KEY` expressions must evaluate
to string types, but the `VALUE` expressions can be composed of any input type,
including other `COMPLEX<json>` values.
Review Comment:
```suggestion
Constructs a new `COMPLEX<json>` object. The `KEY` expressions must evaluate
to string types. The `VALUE` expressions can be composed of any input type,
including other `COMPLEX<json>` values.
```
##########
docs/querying/sql-functions.md:
##########
@@ -647,6 +647,46 @@ Parses `address` into an IPv4 address stored as an integer.
Converts `address` into an IPv4 address in dot-decimal notation.
+## JSON_KEYS
+
+**Function type:** [JSON](sql-json-functions.md)
+
+`JSON_KEYS(expr, path)`
+
+Returns an array of field names in a `COMPLEX<json>` typed `expr`, at the
specified `path`.
+
+## JSON_OBJECT
+
+**Function type:** [JSON](sql-json-functions.md)
+
+`JSON_OBJECT(KEY expr1 VALUE expr2[, KEY expr3 VALUE expr4, ...])`
+
+Constructs a new `COMPLEX<json>` object. The `KEY` expressions must evaluate
to string types, but the `VALUE` expressions can be composed of any input type,
including other `COMPLEX<json>` values.
+
+## JSON_PATHS
+
+**Function type:** [JSON](sql-json-functions.md)
+
+`JSON_PATHS(expr)`
+
+Returns an array of all paths which refer to literal values in a
`COMPLEX<json>` typed `expr`, in JSONPath format.
+
+## JSON_QUERY
+
+**Function type:** [JSON](sql-json-functions.md)
+
+`JSON_QUERY(expr, path)`
+
+Extracts a `COMPLEX<json>` value from a `COMPLEX<json>` typed `expr`, at the
specified `path`.
Review Comment:
```suggestion
Extracts a `COMPLEX<json>` value from a `COMPLEX<json>`-typed `expr`, at the
specified `path`.
```
##########
docs/querying/sql-json-functions.md:
##########
@@ -0,0 +1,71 @@
+---
+id: sql-json-functions
+title: "SQL JSON functions"
+sidebar_label: "JSON functions"
+---
+
+<!--
+ ~ 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.
+ -->
+
+<!--
+ The format of the tables that describe the functions and operators
+ should not be changed without updating the script create-sql-docs
+ in web-console/script/create-sql-docs, because the script detects
+ patterns in this markdown file and parse it to TypeScript file for web
console
+-->
+
+Druid supports nested columns, which provide optimized storage and indexes for
nested data structures. These JSON
+functions provide facilities to extract, transform, and create `COMPLEX<json>`
values.
+
+| function | notes |
Review Comment:
```suggestion
| Function | Notes |
```
Capitalization throughout the docs is not standardized but default towards
sentence case in table headings
##########
docs/querying/sql-json-functions.md:
##########
@@ -0,0 +1,71 @@
+---
+id: sql-json-functions
+title: "SQL JSON functions"
+sidebar_label: "JSON functions"
+---
+
+<!--
+ ~ 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.
+ -->
+
+<!--
+ The format of the tables that describe the functions and operators
+ should not be changed without updating the script create-sql-docs
+ in web-console/script/create-sql-docs, because the script detects
+ patterns in this markdown file and parse it to TypeScript file for web
console
+-->
+
+Druid supports nested columns, which provide optimized storage and indexes for
nested data structures. These JSON
+functions provide facilities to extract, transform, and create `COMPLEX<json>`
values.
+
+| function | notes |
+| --- | --- |
+|`JSON_KEYS(expr, path)`| Returns an array of field names in a `COMPLEX<json>`
typed `expr`, at the specified `path`.|
+|`JSON_OBJECT(KEY expr1 VALUE expr2[, KEY expr3 VALUE expr4, ...])` |
Constructs a new `COMPLEX<json>` object. The `KEY` expressions must evaluate to
string types, but the `VALUE` expressions can be composed of any input type,
including other `COMPLEX<json>` values.|
+|`JSON_PATHS(expr)`| Returns an array of all paths which refer to literal
values in a `COMPLEX<json>` typed `expr`, in JSONPath format. |
+|`JSON_QUERY(expr, path)`| Extracts a `COMPLEX<json>` value from a
`COMPLEX<json>` typed `expr`, at the specified `path`. |
+|`JSON_VALUE(expr, path [RETURNING sqlType])`| Extracts a literal value from a
`COMPLEX<json>` typed `expr`, at the specified `path`. If you specify
`RETURNING` and an SQL type name (such as varchar, bigint, decimal, or double)
the function plans the query using the suggested type. Otherwise it attempts to
infer the type based on the context. If it can't infer the type, it defaults to
varchar.|
+|`PARSE_JSON(expr)`|Parses a string type `expr` into a `COMPLEX<json>` object.
This operator deserializes JSON values when processing them, translating
stringified JSON into a nested structure. Non-`STRING` input or invalid JSON
will result in an error.|
+|`TRY_PARSE_JSON(expr)`|Parses a string type `expr` into a `COMPLEX<json>`
object. This operator deserializes JSON values when processing them,
translating stringified JSON into a nested structure. Non-`STRING` input or
invalid JSON will result in a `NULL` value.|
+|`TO_JSON_STRING(expr)`|Casts an `expr` of any type into a `COMPLEX<json>`
object, then serializes the value into a JSON string.|
+
+### 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.
+
+|Operator|Description|
+| --- | --- |
+|`$`| Root element. All JSONPath expressions start with this operator. |
+|`.<name>`| Child element in dot notation. |
+|`['<name>']`| Child element in bracket notation. |
+|`[<number>]`| Array index. |
+
+Consider the following example input JSON:
+
+```json
+{"x":1, "y":[1, 2, 3]}
+```
+
+- To return the JSON object:<br>
+ `$` -> `{"x":1, "y":[1, 2, 3]}`
+- To return the value of a key "x":<br>
Review Comment:
```suggestion
- To return the value of the key "x":<br>
```
##########
docs/querying/virtual-columns.md:
##########
@@ -80,4 +82,106 @@ The expression virtual column has the following syntax:
|--------|-----------|---------|
|name|The name of the virtual column.|yes|
|expression|An [expression](../misc/math-expr.md) that takes a row as input
and outputs a value for the virtual column.|yes|
-|outputType|The expression's output will be coerced to this type. Can be LONG,
FLOAT, DOUBLE, or STRING.|no, default is FLOAT|
+|outputType|The expression's output will be coerced to this type. Can be LONG,
FLOAT, DOUBLE, STRING, ARRAY types, or COMPLEX types.|no, default is FLOAT|
+
+
+### Nested field virtual column
+
+The nested field virtual column is an optimized virtual column that can
provide direct access into various paths of
+a `COMPLEX<json>` column, including using their indexes.
+
+Syntax (all 3 of these virtual columns produce the same output):
+```json
+ {
+ "type": "nested-field",
+ "columnName": "shipTo",
+ "outputName": "v0",
+ "expectedType": "STRING",
+ "path": "$.phoneNumbers[1].number"
+ }
+```
+```json
+ {
+ "type": "nested-field",
+ "columnName": "shipTo",
+ "outputName": "v1",
+ "expectedType": "STRING",
+ "path": ".phoneNumbers[1].number",
+ "useJqSyntax": true
+ }
+```
+
+```json
+ {
+ "type": "nested-field",
+ "columnName": "shipTo",
+ "outputName": "v2",
+ "expectedType": "STRING",
+ "pathParts": [
+ {
+ "type": "field",
+ "field": "phoneNumbers"
+ },
+ {
+ "type": "arrayElement",
+ "index": 1
+ },
+ {
+ "type": "field",
+ "field": "number"
+ }
+ ]
+ }
+```
+
+|property|description|required?|
+|--------|-----------|---------|
+|columnName|The name of the virtual column.|yes|
+|outputName|The name of the virtual column.|yes|
+|expectedType|The name of the virtual column.|yes|
+|pathParts|The name of the virtual column.|yes|
+|processFromRaw|If set to true, the virtual column will process the "raw" JSON
data to extract values rather than using an optimized "literal" value selector.
This option allows extracting non-literal values (such as nested JSON objects
or arrays) as a `COMPLEX<json>` at the cost of much slower performance.|No,
default false|
+|path|'JSONPath' or 'jq' syntax path. One of `path` or `pathParts` must be
set|no, if `pathParts` is defined|
+|useJqSyntax||no, default is false|
Review Comment:
Missing description?
##########
docs/querying/sql-json-functions.md:
##########
@@ -0,0 +1,71 @@
+---
+id: sql-json-functions
+title: "SQL JSON functions"
+sidebar_label: "JSON functions"
+---
+
+<!--
+ ~ 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.
+ -->
+
+<!--
+ The format of the tables that describe the functions and operators
+ should not be changed without updating the script create-sql-docs
+ in web-console/script/create-sql-docs, because the script detects
+ patterns in this markdown file and parse it to TypeScript file for web
console
+-->
+
+Druid supports nested columns, which provide optimized storage and indexes for
nested data structures. These JSON
+functions provide facilities to extract, transform, and create `COMPLEX<json>`
values.
+
+| function | notes |
+| --- | --- |
+|`JSON_KEYS(expr, path)`| Returns an array of field names in a `COMPLEX<json>`
typed `expr`, at the specified `path`.|
+|`JSON_OBJECT(KEY expr1 VALUE expr2[, KEY expr3 VALUE expr4, ...])` |
Constructs a new `COMPLEX<json>` object. The `KEY` expressions must evaluate to
string types, but the `VALUE` expressions can be composed of any input type,
including other `COMPLEX<json>` values.|
+|`JSON_PATHS(expr)`| Returns an array of all paths which refer to literal
values in a `COMPLEX<json>` typed `expr`, in JSONPath format. |
+|`JSON_QUERY(expr, path)`| Extracts a `COMPLEX<json>` value from a
`COMPLEX<json>` typed `expr`, at the specified `path`. |
+|`JSON_VALUE(expr, path [RETURNING sqlType])`| Extracts a literal value from a
`COMPLEX<json>` typed `expr`, at the specified `path`. If you specify
`RETURNING` and an SQL type name (such as varchar, bigint, decimal, or double)
the function plans the query using the suggested type. Otherwise it attempts to
infer the type based on the context. If it can't infer the type, it defaults to
varchar.|
+|`PARSE_JSON(expr)`|Parses a string type `expr` into a `COMPLEX<json>` object.
This operator deserializes JSON values when processing them, translating
stringified JSON into a nested structure. Non-`STRING` input or invalid JSON
will result in an error.|
+|`TRY_PARSE_JSON(expr)`|Parses a string type `expr` into a `COMPLEX<json>`
object. This operator deserializes JSON values when processing them,
translating stringified JSON into a nested structure. Non-`STRING` input or
invalid JSON will result in a `NULL` value.|
+|`TO_JSON_STRING(expr)`|Casts an `expr` of any type into a `COMPLEX<json>`
object, then serializes the value into a JSON string.|
+
+### 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.
+
+|Operator|Description|
+| --- | --- |
+|`$`| Root element. All JSONPath expressions start with this operator. |
+|`.<name>`| Child element in dot notation. |
+|`['<name>']`| Child element in bracket notation. |
+|`[<number>]`| Array index. |
+
+Consider the following example input JSON:
+
+```json
+{"x":1, "y":[1, 2, 3]}
+```
+
+- To return the JSON object:<br>
Review Comment:
```suggestion
- To return the entire JSON object:<br>
```
##########
docs/querying/sql-json-functions.md:
##########
@@ -0,0 +1,71 @@
+---
+id: sql-json-functions
+title: "SQL JSON functions"
+sidebar_label: "JSON functions"
+---
+
+<!--
+ ~ 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.
+ -->
+
+<!--
+ The format of the tables that describe the functions and operators
+ should not be changed without updating the script create-sql-docs
+ in web-console/script/create-sql-docs, because the script detects
+ patterns in this markdown file and parse it to TypeScript file for web
console
+-->
+
+Druid supports nested columns, which provide optimized storage and indexes for
nested data structures. These JSON
+functions provide facilities to extract, transform, and create `COMPLEX<json>`
values.
Review Comment:
```suggestion
Druid supports nested columns, which provide optimized storage and indexes
for nested data structures. Use
the following JSON functions to extract, transform, and create
[`COMPLEX<json>` values](sql-data-types.md#nested-columns).
```
##########
docs/querying/virtual-columns.md:
##########
@@ -80,4 +82,106 @@ The expression virtual column has the following syntax:
|--------|-----------|---------|
|name|The name of the virtual column.|yes|
|expression|An [expression](../misc/math-expr.md) that takes a row as input
and outputs a value for the virtual column.|yes|
-|outputType|The expression's output will be coerced to this type. Can be LONG,
FLOAT, DOUBLE, or STRING.|no, default is FLOAT|
+|outputType|The expression's output will be coerced to this type. Can be LONG,
FLOAT, DOUBLE, STRING, ARRAY types, or COMPLEX types.|no, default is FLOAT|
+
+
+### Nested field virtual column
+
+The nested field virtual column is an optimized virtual column that can
provide direct access into various paths of
+a `COMPLEX<json>` column, including using their indexes.
+
+Syntax (all 3 of these virtual columns produce the same output):
Review Comment:
```suggestion
You can define a nested field virtual column with any of the following
syntaxes.
The examples differ in how to access a specific value from the
`COMPLEX<json>`.
All examples produce the same output value.
Reference a virtual column by its output name, such as `v0`, `v1`, or `v2`.
```
##########
docs/querying/virtual-columns.md:
##########
@@ -80,4 +82,106 @@ The expression virtual column has the following syntax:
|--------|-----------|---------|
|name|The name of the virtual column.|yes|
|expression|An [expression](../misc/math-expr.md) that takes a row as input
and outputs a value for the virtual column.|yes|
-|outputType|The expression's output will be coerced to this type. Can be LONG,
FLOAT, DOUBLE, or STRING.|no, default is FLOAT|
+|outputType|The expression's output will be coerced to this type. Can be LONG,
FLOAT, DOUBLE, STRING, ARRAY types, or COMPLEX types.|no, default is FLOAT|
+
+
+### Nested field virtual column
+
+The nested field virtual column is an optimized virtual column that can
provide direct access into various paths of
+a `COMPLEX<json>` column, including using their indexes.
+
+Syntax (all 3 of these virtual columns produce the same output):
+```json
+ {
+ "type": "nested-field",
+ "columnName": "shipTo",
+ "outputName": "v0",
+ "expectedType": "STRING",
+ "path": "$.phoneNumbers[1].number"
+ }
+```
+```json
+ {
+ "type": "nested-field",
+ "columnName": "shipTo",
+ "outputName": "v1",
+ "expectedType": "STRING",
+ "path": ".phoneNumbers[1].number",
+ "useJqSyntax": true
+ }
+```
+
+```json
+ {
+ "type": "nested-field",
+ "columnName": "shipTo",
+ "outputName": "v2",
+ "expectedType": "STRING",
+ "pathParts": [
+ {
+ "type": "field",
+ "field": "phoneNumbers"
+ },
+ {
+ "type": "arrayElement",
+ "index": 1
+ },
+ {
+ "type": "field",
+ "field": "number"
+ }
+ ]
+ }
+```
+
+|property|description|required?|
+|--------|-----------|---------|
+|columnName|The name of the virtual column.|yes|
+|outputName|The name of the virtual column.|yes|
+|expectedType|The name of the virtual column.|yes|
+|pathParts|The name of the virtual column.|yes|
+|processFromRaw|If set to true, the virtual column will process the "raw" JSON
data to extract values rather than using an optimized "literal" value selector.
This option allows extracting non-literal values (such as nested JSON objects
or arrays) as a `COMPLEX<json>` at the cost of much slower performance.|No,
default false|
+|path|'JSONPath' or 'jq' syntax path. One of `path` or `pathParts` must be
set|no, if `pathParts` is defined|
+|useJqSyntax||no, default is false|
+
+#### Nested path part
+|property|description|required?|
+|--------|-----------|---------|
+|type|Must be 'field' or 'arrayElement'|yes|
+|field|The name of the 'field' in a 'field' `type` path part|yes, if `type` is
'field'|
+|index|The array element index if `type` is `arrayElement`|yes, if `type` is
'arrayElement'|
+
+This virtual column is used for the SQL operators `JSON_VALUE` (if
`processFromRaw` is set to false) or `JSON_QUERY`
+(if it is true), and accepts 'JSONPath' or 'jq' syntax string representations
of paths, or a parsed
Review Comment:
```suggestion
(if `processFromRaw` is true), and accepts 'JSONPath' or 'jq' syntax string
representations of paths, or a parsed
```
##########
docs/querying/sql-json-functions.md:
##########
@@ -0,0 +1,71 @@
+---
+id: sql-json-functions
+title: "SQL JSON functions"
+sidebar_label: "JSON functions"
+---
+
+<!--
+ ~ 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.
+ -->
+
+<!--
+ The format of the tables that describe the functions and operators
+ should not be changed without updating the script create-sql-docs
+ in web-console/script/create-sql-docs, because the script detects
+ patterns in this markdown file and parse it to TypeScript file for web
console
+-->
+
+Druid supports nested columns, which provide optimized storage and indexes for
nested data structures. These JSON
+functions provide facilities to extract, transform, and create `COMPLEX<json>`
values.
+
+| function | notes |
+| --- | --- |
+|`JSON_KEYS(expr, path)`| Returns an array of field names in a `COMPLEX<json>`
typed `expr`, at the specified `path`.|
+|`JSON_OBJECT(KEY expr1 VALUE expr2[, KEY expr3 VALUE expr4, ...])` |
Constructs a new `COMPLEX<json>` object. The `KEY` expressions must evaluate to
string types, but the `VALUE` expressions can be composed of any input type,
including other `COMPLEX<json>` values.|
+|`JSON_PATHS(expr)`| Returns an array of all paths which refer to literal
values in a `COMPLEX<json>` typed `expr`, in JSONPath format. |
+|`JSON_QUERY(expr, path)`| Extracts a `COMPLEX<json>` value from a
`COMPLEX<json>` typed `expr`, at the specified `path`. |
+|`JSON_VALUE(expr, path [RETURNING sqlType])`| Extracts a literal value from a
`COMPLEX<json>` typed `expr`, at the specified `path`. If you specify
`RETURNING` and an SQL type name (such as varchar, bigint, decimal, or double)
the function plans the query using the suggested type. Otherwise it attempts to
infer the type based on the context. If it can't infer the type, it defaults to
varchar.|
+|`PARSE_JSON(expr)`|Parses a string type `expr` into a `COMPLEX<json>` object.
This operator deserializes JSON values when processing them, translating
stringified JSON into a nested structure. Non-`STRING` input or invalid JSON
will result in an error.|
+|`TRY_PARSE_JSON(expr)`|Parses a string type `expr` into a `COMPLEX<json>`
object. This operator deserializes JSON values when processing them,
translating stringified JSON into a nested structure. Non-`STRING` input or
invalid JSON will result in a `NULL` value.|
+|`TO_JSON_STRING(expr)`|Casts an `expr` of any type into a `COMPLEX<json>`
object, then serializes the value into a JSON string.|
+
+### 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:
```suggestion
Druid supports a 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.
```
##########
docs/querying/virtual-columns.md:
##########
@@ -80,4 +82,106 @@ The expression virtual column has the following syntax:
|--------|-----------|---------|
|name|The name of the virtual column.|yes|
|expression|An [expression](../misc/math-expr.md) that takes a row as input
and outputs a value for the virtual column.|yes|
-|outputType|The expression's output will be coerced to this type. Can be LONG,
FLOAT, DOUBLE, or STRING.|no, default is FLOAT|
+|outputType|The expression's output will be coerced to this type. Can be LONG,
FLOAT, DOUBLE, STRING, ARRAY types, or COMPLEX types.|no, default is FLOAT|
+
+
+### Nested field virtual column
+
+The nested field virtual column is an optimized virtual column that can
provide direct access into various paths of
+a `COMPLEX<json>` column, including using their indexes.
+
+Syntax (all 3 of these virtual columns produce the same output):
+```json
+ {
+ "type": "nested-field",
+ "columnName": "shipTo",
+ "outputName": "v0",
+ "expectedType": "STRING",
+ "path": "$.phoneNumbers[1].number"
+ }
+```
+```json
+ {
+ "type": "nested-field",
+ "columnName": "shipTo",
+ "outputName": "v1",
+ "expectedType": "STRING",
+ "path": ".phoneNumbers[1].number",
+ "useJqSyntax": true
+ }
+```
+
+```json
+ {
+ "type": "nested-field",
+ "columnName": "shipTo",
+ "outputName": "v2",
+ "expectedType": "STRING",
+ "pathParts": [
+ {
+ "type": "field",
+ "field": "phoneNumbers"
+ },
+ {
+ "type": "arrayElement",
+ "index": 1
+ },
+ {
+ "type": "field",
+ "field": "number"
+ }
+ ]
+ }
+```
+
+|property|description|required?|
+|--------|-----------|---------|
+|columnName|The name of the virtual column.|yes|
+|outputName|The name of the virtual column.|yes|
+|expectedType|The name of the virtual column.|yes|
+|pathParts|The name of the virtual column.|yes|
+|processFromRaw|If set to true, the virtual column will process the "raw" JSON
data to extract values rather than using an optimized "literal" value selector.
This option allows extracting non-literal values (such as nested JSON objects
or arrays) as a `COMPLEX<json>` at the cost of much slower performance.|No,
default false|
+|path|'JSONPath' or 'jq' syntax path. One of `path` or `pathParts` must be
set|no, if `pathParts` is defined|
+|useJqSyntax||no, default is false|
+
+#### Nested path part
+|property|description|required?|
+|--------|-----------|---------|
+|type|Must be 'field' or 'arrayElement'|yes|
Review Comment:
```suggestion
|type|Must be `field` or `arrayElement`. Use `field` when accessing a
specific field in a nested structure. Use `arrayElement` when accessing a
specific integer position of an array.|yes|
```
Array position starts with 0?
##########
docs/querying/virtual-columns.md:
##########
@@ -80,4 +82,106 @@ The expression virtual column has the following syntax:
|--------|-----------|---------|
|name|The name of the virtual column.|yes|
Review Comment:
This table should include the `type` property as required. Description can
be something like "Type of the virtual column. Set to `expression` for an
expression virtual column."
##########
docs/querying/virtual-columns.md:
##########
@@ -80,4 +82,106 @@ The expression virtual column has the following syntax:
|--------|-----------|---------|
|name|The name of the virtual column.|yes|
|expression|An [expression](../misc/math-expr.md) that takes a row as input
and outputs a value for the virtual column.|yes|
-|outputType|The expression's output will be coerced to this type. Can be LONG,
FLOAT, DOUBLE, or STRING.|no, default is FLOAT|
+|outputType|The expression's output will be coerced to this type. Can be LONG,
FLOAT, DOUBLE, STRING, ARRAY types, or COMPLEX types.|no, default is FLOAT|
+
+
+### Nested field virtual column
+
+The nested field virtual column is an optimized virtual column that can
provide direct access into various paths of
+a `COMPLEX<json>` column, including using their indexes.
+
+Syntax (all 3 of these virtual columns produce the same output):
+```json
+ {
+ "type": "nested-field",
+ "columnName": "shipTo",
+ "outputName": "v0",
+ "expectedType": "STRING",
+ "path": "$.phoneNumbers[1].number"
+ }
+```
+```json
+ {
+ "type": "nested-field",
+ "columnName": "shipTo",
+ "outputName": "v1",
+ "expectedType": "STRING",
+ "path": ".phoneNumbers[1].number",
+ "useJqSyntax": true
+ }
+```
+
+```json
+ {
+ "type": "nested-field",
+ "columnName": "shipTo",
+ "outputName": "v2",
+ "expectedType": "STRING",
+ "pathParts": [
+ {
+ "type": "field",
+ "field": "phoneNumbers"
+ },
+ {
+ "type": "arrayElement",
+ "index": 1
+ },
+ {
+ "type": "field",
+ "field": "number"
+ }
+ ]
+ }
+```
+
+|property|description|required?|
+|--------|-----------|---------|
+|columnName|The name of the virtual column.|yes|
+|outputName|The name of the virtual column.|yes|
+|expectedType|The name of the virtual column.|yes|
+|pathParts|The name of the virtual column.|yes|
Review Comment:
Update description?
##########
docs/querying/virtual-columns.md:
##########
@@ -80,4 +82,106 @@ The expression virtual column has the following syntax:
|--------|-----------|---------|
|name|The name of the virtual column.|yes|
|expression|An [expression](../misc/math-expr.md) that takes a row as input
and outputs a value for the virtual column.|yes|
-|outputType|The expression's output will be coerced to this type. Can be LONG,
FLOAT, DOUBLE, or STRING.|no, default is FLOAT|
+|outputType|The expression's output will be coerced to this type. Can be LONG,
FLOAT, DOUBLE, STRING, ARRAY types, or COMPLEX types.|no, default is FLOAT|
+
+
+### Nested field virtual column
+
+The nested field virtual column is an optimized virtual column that can
provide direct access into various paths of
+a `COMPLEX<json>` column, including using their indexes.
+
+Syntax (all 3 of these virtual columns produce the same output):
+```json
+ {
+ "type": "nested-field",
+ "columnName": "shipTo",
+ "outputName": "v0",
+ "expectedType": "STRING",
+ "path": "$.phoneNumbers[1].number"
+ }
+```
+```json
+ {
+ "type": "nested-field",
+ "columnName": "shipTo",
+ "outputName": "v1",
+ "expectedType": "STRING",
+ "path": ".phoneNumbers[1].number",
+ "useJqSyntax": true
+ }
+```
+
+```json
+ {
+ "type": "nested-field",
+ "columnName": "shipTo",
+ "outputName": "v2",
+ "expectedType": "STRING",
+ "pathParts": [
+ {
+ "type": "field",
+ "field": "phoneNumbers"
+ },
+ {
+ "type": "arrayElement",
+ "index": 1
+ },
+ {
+ "type": "field",
+ "field": "number"
+ }
+ ]
+ }
+```
+
+|property|description|required?|
+|--------|-----------|---------|
+|columnName|The name of the virtual column.|yes|
+|outputName|The name of the virtual column.|yes|
+|expectedType|The name of the virtual column.|yes|
+|pathParts|The name of the virtual column.|yes|
+|processFromRaw|If set to true, the virtual column will process the "raw" JSON
data to extract values rather than using an optimized "literal" value selector.
This option allows extracting non-literal values (such as nested JSON objects
or arrays) as a `COMPLEX<json>` at the cost of much slower performance.|No,
default false|
+|path|'JSONPath' or 'jq' syntax path. One of `path` or `pathParts` must be
set|no, if `pathParts` is defined|
+|useJqSyntax||no, default is false|
+
+#### Nested path part
+|property|description|required?|
+|--------|-----------|---------|
+|type|Must be 'field' or 'arrayElement'|yes|
+|field|The name of the 'field' in a 'field' `type` path part|yes, if `type` is
'field'|
+|index|The array element index if `type` is `arrayElement`|yes, if `type` is
'arrayElement'|
+
+This virtual column is used for the SQL operators `JSON_VALUE` (if
`processFromRaw` is set to false) or `JSON_QUERY`
Review Comment:
This paragraph might be more useful at the beginning of the section (before
the syntax code blocks)
##########
docs/querying/virtual-columns.md:
##########
@@ -80,4 +82,106 @@ The expression virtual column has the following syntax:
|--------|-----------|---------|
|name|The name of the virtual column.|yes|
Review Comment:
Ditto for the `nested-field` virtual column and `mv-filtered` virtual column
##########
docs/querying/virtual-columns.md:
##########
@@ -80,4 +82,106 @@ The expression virtual column has the following syntax:
|--------|-----------|---------|
|name|The name of the virtual column.|yes|
|expression|An [expression](../misc/math-expr.md) that takes a row as input
and outputs a value for the virtual column.|yes|
-|outputType|The expression's output will be coerced to this type. Can be LONG,
FLOAT, DOUBLE, or STRING.|no, default is FLOAT|
+|outputType|The expression's output will be coerced to this type. Can be LONG,
FLOAT, DOUBLE, STRING, ARRAY types, or COMPLEX types.|no, default is FLOAT|
+
+
+### Nested field virtual column
+
+The nested field virtual column is an optimized virtual column that can
provide direct access into various paths of
+a `COMPLEX<json>` column, including using their indexes.
+
+Syntax (all 3 of these virtual columns produce the same output):
+```json
+ {
+ "type": "nested-field",
+ "columnName": "shipTo",
+ "outputName": "v0",
+ "expectedType": "STRING",
+ "path": "$.phoneNumbers[1].number"
+ }
+```
+```json
+ {
+ "type": "nested-field",
+ "columnName": "shipTo",
+ "outputName": "v1",
+ "expectedType": "STRING",
+ "path": ".phoneNumbers[1].number",
+ "useJqSyntax": true
+ }
+```
+
+```json
+ {
+ "type": "nested-field",
+ "columnName": "shipTo",
+ "outputName": "v2",
+ "expectedType": "STRING",
+ "pathParts": [
+ {
+ "type": "field",
+ "field": "phoneNumbers"
+ },
+ {
+ "type": "arrayElement",
+ "index": 1
+ },
+ {
+ "type": "field",
+ "field": "number"
+ }
+ ]
+ }
+```
+
+|property|description|required?|
+|--------|-----------|---------|
+|columnName|The name of the virtual column.|yes|
+|outputName|The name of the virtual column.|yes|
+|expectedType|The name of the virtual column.|yes|
+|pathParts|The name of the virtual column.|yes|
+|processFromRaw|If set to true, the virtual column will process the "raw" JSON
data to extract values rather than using an optimized "literal" value selector.
This option allows extracting non-literal values (such as nested JSON objects
or arrays) as a `COMPLEX<json>` at the cost of much slower performance.|No,
default false|
+|path|'JSONPath' or 'jq' syntax path. One of `path` or `pathParts` must be
set|no, if `pathParts` is defined|
+|useJqSyntax||no, default is false|
+
+#### Nested path part
Review Comment:
```suggestion
#### Nested path part
Specify `pathParts` as an array of objects that describe each component of
the path to traverse. Each object can take the following properties:
```
##########
docs/querying/sql-json-functions.md:
##########
@@ -0,0 +1,71 @@
+---
+id: sql-json-functions
+title: "SQL JSON functions"
+sidebar_label: "JSON functions"
+---
+
+<!--
+ ~ 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.
+ -->
+
+<!--
+ The format of the tables that describe the functions and operators
+ should not be changed without updating the script create-sql-docs
+ in web-console/script/create-sql-docs, because the script detects
+ patterns in this markdown file and parse it to TypeScript file for web
console
+-->
+
+Druid supports nested columns, which provide optimized storage and indexes for
nested data structures. These JSON
+functions provide facilities to extract, transform, and create `COMPLEX<json>`
values.
+
+| function | notes |
+| --- | --- |
+|`JSON_KEYS(expr, path)`| Returns an array of field names in a `COMPLEX<json>`
typed `expr`, at the specified `path`.|
+|`JSON_OBJECT(KEY expr1 VALUE expr2[, KEY expr3 VALUE expr4, ...])` |
Constructs a new `COMPLEX<json>` object. The `KEY` expressions must evaluate to
string types, but the `VALUE` expressions can be composed of any input type,
including other `COMPLEX<json>` values.|
+|`JSON_PATHS(expr)`| Returns an array of all paths which refer to literal
values in a `COMPLEX<json>` typed `expr`, in JSONPath format. |
+|`JSON_QUERY(expr, path)`| Extracts a `COMPLEX<json>` value from a
`COMPLEX<json>` typed `expr`, at the specified `path`. |
+|`JSON_VALUE(expr, path [RETURNING sqlType])`| Extracts a literal value from a
`COMPLEX<json>` typed `expr`, at the specified `path`. If you specify
`RETURNING` and an SQL type name (such as varchar, bigint, decimal, or double)
the function plans the query using the suggested type. Otherwise it attempts to
infer the type based on the context. If it can't infer the type, it defaults to
varchar.|
+|`PARSE_JSON(expr)`|Parses a string type `expr` into a `COMPLEX<json>` object.
This operator deserializes JSON values when processing them, translating
stringified JSON into a nested structure. Non-`STRING` input or invalid JSON
will result in an error.|
+|`TRY_PARSE_JSON(expr)`|Parses a string type `expr` into a `COMPLEX<json>`
object. This operator deserializes JSON values when processing them,
translating stringified JSON into a nested structure. Non-`STRING` input or
invalid JSON will result in a `NULL` value.|
+|`TO_JSON_STRING(expr)`|Casts an `expr` of any type into a `COMPLEX<json>`
object, then serializes the value into a JSON string.|
+
+### 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.
+
+|Operator|Description|
+| --- | --- |
+|`$`| Root element. All JSONPath expressions start with this operator. |
+|`.<name>`| Child element in dot notation. |
+|`['<name>']`| Child element in bracket notation. |
+|`[<number>]`| Array index. |
+
+Consider the following example input JSON:
+
+```json
+{"x":1, "y":[1, 2, 3]}
+```
+
+- To return the JSON object:<br>
+ `$` -> `{"x":1, "y":[1, 2, 3]}`
+- To return the value of a key "x":<br>
Review Comment:
nice examples!
--
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]
