SQL JSON functions

Druid supports nested columns, which provide optimized storage and indexes for nested data structures. See Nested columns for more information.

You can use the following JSON functions to extract, transform, and create COMPLEX<json> values.

Function	Notes
`JSON_KEYS(expr, path)`	Returns an array of field names from `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. The `VALUE` expressions can be composed of any input type, including other `COMPLEX<json>` values. `JSON_OBJECT` can accept colon-separated key-value pairs. The following syntax is equivalent: `JSON_OBJECT(expr1:expr2[, expr3:expr4, ...])`.
`JSON_MERGE(expr1, expr2[, expr3 ...])`	Merges two or more JSON `STRING` or `COMPLEX<json>` into one. Preserves the rightmost value when there are key overlaps. Returning always a `COMPLEX<json>` type.
`JSON_PATHS(expr)`	Returns an array of all paths which refer to literal values in `expr` in JSONPath format.
`JSON_QUERY(expr, path)`	Extracts a `COMPLEX<json>` value from `expr`, at the specified `path`.
`JSON_QUERY_ARRAY(expr, path)`	Extracts an `ARRAY<COMPLEX<json>>` value from `expr` at the specified `path`. If value is not an `ARRAY`, it gets translated into a single element `ARRAY` containing the value at `path`. The primary use of this function is to extract arrays of objects to use as inputs to other array functions.
`JSON_VALUE(expr, path [RETURNING sqlType])`	Extracts a literal value from `expr` at the specified `path`. If you specify `RETURNING` and an SQL type name (such as `VARCHAR`, `BIGINT`, `DOUBLE`, etc) 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 `expr` into a `COMPLEX<json>` object. This operator deserializes JSON values when processing them, translating stringified JSON into a nested structure. If the input is not a `VARCHAR` or it is invalid JSON, this function will result in an error.
`TRY_PARSE_JSON(expr)`	Parses `expr` into a `COMPLEX<json>` object. This operator deserializes JSON values when processing them, translating stringified JSON into a nested structure. If the input is not a `VARCHAR` or it is invalid JSON, this function will result in a `NULL` value.
`TO_JSON_STRING(expr)`	Serializes `expr` into a JSON string.

JSONPath syntax

Druid supports a subset of the JSONPath syntax 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:

{"x":1, "y":[1, 2, 3]}

To return the entire JSON object:
$ -> {"x":1, "y":[1, 2, 3]}
To return the value of the key "x":
$.x -> 1
For a key that contains an array, to return the entire array:
$['y'] -> [1, 2, 3]
For a key that contains an array, to return an item in the array:
$.y[1] -> 2

JSONPath syntax​

JSONPath syntax