<a id="flink-sql-json-functions"></a>

# JSON Functions in Confluent Cloud for Apache Flink

Confluent Cloud for Apache Flink® provides these built-in functions to help with JSON in SQL
queries:

| [IS JSON](#flink-sql-is-json-function)           | [JSON_ARRAY](#flink-sql-json-array-function)   | [JSON_ARRAYAGG](#flink-sql-json-arrayagg-function)   |
|--------------------------------------------------|------------------------------------------------|------------------------------------------------------|
| [JSON_EXISTS](#flink-sql-json-exists-function)   | [JSON_OBJECT](#flink-sql-json-object-function) | [JSON_OBJECTAGG](#flink-sql-json-objectagg-function) |
| [JSON_QUERY](#flink-sql-json-query-function)     | [JSON_QUOTE](#flink-sql-json-quote-function)   | [JSON_STRING](#flink-sql-json-string-function)       |
| [JSON_UNQUOTE](#flink-sql-json-unquote-function) | [JSON_VALUE](#flink-sql-json-value-function)   |                                                      |

JSON functions make use of JSON path expressions as described in [ISO/IEC
TR 19075-6](https://www.iso.org/standard/78937.html) of the SQL standard.
Their syntax is inspired by and adopts many features of ECMAScript, but is
neither a subset nor superset of the standard.

Path expressions come in two flavors, lax and strict. When omitted, it
defaults to the strict mode. Strict mode is intended to examine data
from a schema perspective and throws errors whenever data does not
adhere to the path expression. However, functions such as `JSON_VALUE`
allow defining fallback behavior if an error is encountered. Lax mode,
on the other hand, is more forgiving and converts errors to empty
sequences.

The special character `$` denotes the root node in a JSON path. Paths
can access properties (`$.a`), array elements (`$.a[0].b`), or
branch over all elements in an array (`$.a[*].b`).

Known Limitations:

- Not all features of Lax mode are currently supported.
  This is an upstream bug
  ([CALCITE-4717](https://issues.apache.org/jira/browse/CALCITE-4717)).
- Non-standard behavior is not guaranteed.

<a id="flink-sql-is-json-function"></a>

## IS JSON

Checks whether a string is valid JSON.

Syntax
: ```sql
  IS JSON [ { VALUE | SCALAR | ARRAY | OBJECT } ]
  ```

Description
: The `IS JSON` function determines whether the specified string is valid JSON.
  <br/>
  Providing the optional type argument constrains the type of JSON object to
  check for validity. The default is `VALUE`. If the string is valid JSON
  but not the provided type, `IS JSON` returns FALSE.

Examples
: The following SELECT statements return TRUE.
  <br/>
  ```sql
  -- The following statements return TRUE.
  SELECT '1' IS JSON;
  SELECT '[]' IS JSON;
  SELECT '{}' IS JSON;
  SELECT '"abc"' IS JSON;
  SELECT '1' IS JSON SCALAR;
  SELECT '{}' IS JSON OBJECT;
  ```
  <br/>
  The following SELECT statements return FALSE.
  <br/>
  ```sql
  -- The following statements return FALSE.
  SELECT 'abc' IS JSON;
  SELECT '1' IS JSON ARRAY;
  SELECT '1' IS JSON OBJECT;
  SELECT '{}' IS JSON SCALAR;
  SELECT '{}' IS JSON ARRAY;
  ```

<a id="flink-sql-json-array-function"></a>

## JSON_ARRAY

Creates a JSON array string from a list of values.

Syntax
: ```sql
  JSON_ARRAY([value]* [ { NULL | ABSENT } ON NULL ])
  ```

Description
: The `JSON_ARRAY` function returns a JSON string from the specified list of
  values. The values can be arbitrary expressions.
  <br/>
  The `ON NULL` behavior defines how to handle NULL values. If omitted,
  `ABSENT ON NULL` is the default.
  <br/>
  Elements that are created from other JSON construction function calls are
  inserted directly, rather than as a string. This enables building nested JSON
  structures by using the `JSON_OBJECT` and `JSON_ARRAY` construction
  functions.

Examples
: The following SELECT statements return the values indicated in the comment
  lines.
  <br/>
  ```sql
  -- returns '[]'
  SELECT JSON_ARRAY();
  <br/>
  -- returns '[1,"2"]'
  SELECT JSON_ARRAY(1, '2');
  <br/>
  -- Use an expression as a value.
  SELECT JSON_ARRAY(orders.orderId);
  <br/>
  -- ON NULL
  -- returns '[null]'
  SELECT JSON_ARRAY(CAST(NULL AS STRING) NULL ON NULL);
  <br/>
  -- ON NULL
  -- returns '[]'
  SELECT JSON_ARRAY(CAST(NULL AS STRING) ABSENT ON NULL);
  <br/>
  -- returns '[[1]]'
  SELECT JSON_ARRAY(JSON_ARRAY(1));
  <br/>
  -- returns '[{"nested_json":{"value":42}}]'
  SELECT JSON_ARRAY(JSON('{"nested_json": {"value": 42}}'));
  ```

<a id="flink-sql-json-arrayagg-function"></a>

## JSON_ARRAYAGG

Aggregates items into a JSON array string.

Syntax
: ```sql
  JSON_ARRAYAGG(items [ { NULL | ABSENT } ON NULL ])
  ```

Description
: The `JSON_ARRAYAGG` function creates a JSON object string by aggregating the
  specified items into an array.
  <br/>
  The item expressions can be arbitrary, including other JSON functions.
  <br/>
  If a value is NULL, the `ON NULL` behavior defines what to do. If omitted,
  `ABSENT ON NULL` is the default.
  <br/>
  The `JSON_ARRAYAGG` function isn’t supported in `OVER` windows, unbounded
  session windows, or `HOP` windows.

Example
: ```sql
  -- returns '["Apple","Banana","Orange"]'
  SELECT JSON_ARRAYAGG(product)
  FROM (VALUES ('Apple'), ('Banana'), ('Orange'))
    AS orders(product);
  ```

<a id="flink-sql-json-exists-function"></a>

## JSON_EXISTS

Checks a JSON path.

Syntax
: ```sql
  JSON_EXISTS(jsonValue, path [ { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ])
  ```

Description
: The `JSON_EXISTS` function determines whether a JSON string satisfies a
  specified path search criterion.
  <br/>
  If the `ON ERROR` behavior is omitted, the default is `FALSE ON ERROR`.

Examples
: The following SELECT statements return TRUE.
  <br/>
  ```sql
  -- The following statements return TRUE.
  SELECT JSON_EXISTS('{"a": true}', '$.a');
  SELECT JSON_EXISTS('{"a": [{ "b": 1 }]}', '$.a[0].b');
  SELECT JSON_EXISTS('{"a": true}', 'strict $.b' TRUE ON ERROR);
  ```
  <br/>
  The following SELECT statements return FALSE.
  <br/>
  ```sql
  -- The following statements return FALSE.
  SELECT JSON_EXISTS('{"a": true}', '$.b');
  SELECT JSON_EXISTS('{"a": true}', 'strict $.b' FALSE ON ERROR);
  ```

<a id="flink-sql-json-object-function"></a>

## JSON_OBJECT

Syntax
: ```sql
  JSON_OBJECT([[KEY] key VALUE value]* [ { NULL | ABSENT } ON NULL ])
  ```

Description
: The `JSON_OBJECT` function creates a JSON object string from the specified
  list of key-value pairs.
  <br/>
  Keys must be non-NULL string literals, and values can be arbitrary expressions.
  <br/>
  The `JSON_OBJECT` function returns a JSON string. The `ON NULL` behavior
  defines how to treat NULL values. If omitted, `NULL ON NULL` is the default.
  <br/>
  Values that are created from another JSON construction function calls are
  inserted directly, rather than as a string. This enables building nested JSON
  structures by using the `JSON_OBJECT` and `JSON_ARRAY` construction
  functions.

Examples
: The following SELECT statements return the values indicated in the comment
  lines.
  <br/>
  ```sql
  -- returns '{}'
  SELECT JSON_OBJECT();
  <br/>
  -- returns '{"K1":"V1","K2":"V2"}'
  SELECT JSON_OBJECT('K1' VALUE 'V1', 'K2' VALUE 'V2');
  <br/>
  -- Use an expression as a value.
  SELECT JSON_OBJECT('orderNo' VALUE orders.orderId);
  <br/>
  -- ON NULL
  -- '{"K1":null}'
  SELECT JSON_OBJECT(KEY 'K1' VALUE CAST(NULL AS STRING) NULL ON NULL);
  <br/>
  -- ON NULL
  -- '{}'
  SELECT JSON_OBJECT(KEY 'K1' VALUE CAST(NULL AS STRING) ABSENT ON NULL);
  <br/>
  -- returns '{"K1":{"nested_json":{"value":42}}}'
  SELECT JSON_OBJECT('K1' VALUE JSON('{"nested_json": {"value": 42}}'));
  <br/>
  -- returns '{"K1":{"K2":"V"}}'
  SELECT JSON_OBJECT(
    KEY 'K1'
    VALUE JSON_OBJECT(
      KEY 'K2'
      VALUE 'V'
    )
  );
  ```

<a id="flink-sql-json-objectagg-function"></a>

## JSON_OBJECTAGG

Aggregates key-value expressions into a JSON string.

Syntax
: ```sql
  JSON_OBJECTAGG([KEY] key VALUE value [ { NULL | ABSENT } ON NULL ])
  ```

Description
: The `JSON_OBJECTAGG` function creates a JSON object string by aggregating
  key-value expressions into a single JSON object.
  <br/>
  The `key` expression must return a non-nullable character string. Value
  expressions can be arbitrary, including other JSON functions.
  <br/>
  Keys must be unique. If a key occurs multiple times, an error is thrown.
  <br/>
  If a value is NULL, the `ON NULL` behavior defines what to do. If omitted,
  `NULL ON NULL` is the default.
  <br/>
  The `JSON_OBJECTAGG` function isn’t supported in `OVER` windows.

Example
: ```sql
  -- returns '{"Apple":2,"Banana":17,"Orange":0}'
  SELECT JSON_OBJECTAGG(KEY product VALUE cnt)
  FROM (VALUES ('Apple', 2), ('Banana', 17), ('Orange', 0))
    AS orders(product, cnt);
  ```

<a id="flink-sql-json-query-function"></a>

## JSON_QUERY

Gets values from a JSON string.

Syntax
: ```sql
  JSON_QUERY(jsonValue, path
    [ RETURNING ]
    [ { WITHOUT | WITH CONDITIONAL | WITH UNCONDITIONAL } [ ARRAY ] WRAPPER ]
    [ { NULL | EMPTY ARRAY | EMPTY OBJECT | ERROR } ON EMPTY ]
    [ { NULL | EMPTY ARRAY | EMPTY OBJECT | ERROR } ON ERROR ])
  ```

Description
: The `JSON_QUERY` function extracts JSON values from the specified JSON
  string.
  <br/>
  The result is returned as a `STRING` or an `ARRAY<STRING>`. Use the
  `RETURNING` clause to control the return type.
  <br/>
  The `WRAPPER` clause specifies whether the extracted value should be wrapped
  into an array and whether to do so unconditionally or only if the value itself
  isn’t an array already.
  <br/>
  The `ON EMPTY` and `ON ERROR` clauses specify the behavior if the path
  expression is empty, or in case an error was raised, respectively. By default,
  in both cases NULL is returned. Other choices are to use an empty array, an
  empty object, or to raise an error.

Examples
: The following SELECT statements return the values indicated in the comment
  lines.
  <br/>
  ```sql
  -- returns '{ "b": 1 }'
  SELECT JSON_QUERY('{ "a": { "b": 1 } }', '$.a');
  <br/>
  -- returns '[1, 2]'
  SELECT JSON_QUERY('[1, 2]', '$');
  <br/>
  -- returns NULL
  SELECT JSON_QUERY(CAST(NULL AS STRING), '$');
  <br/>
  -- returns array ['c1','c2']
  SELECT JSON_QUERY('{"a":[{"c":"c1"},{"c":"c2"}]}', 'lax $.a[*].c' RETURNING ARRAY<STRING>);
  <br/>
  -- Wrap the result into an array.
  -- returns '[{}]'
  SELECT JSON_QUERY('{}', '$' WITH CONDITIONAL ARRAY WRAPPER);
  <br/>
  -- returns '[1, 2]'
  SELECT JSON_QUERY('[1, 2]', '$' WITH CONDITIONAL ARRAY WRAPPER);
  <br/>
  -- returns '[[1, 2]]'
  SELECT JSON_QUERY('[1, 2]', '$' WITH UNCONDITIONAL ARRAY WRAPPER);
  <br/>
  -- Scalars must be wrapped to be returned.
  -- returns NULL
  SELECT JSON_QUERY(1, '$');
  <br/>
  -- returns '[1]'
  SELECT JSON_QUERY(1, '$' WITH CONDITIONAL ARRAY WRAPPER);
  <br/>
  -- Behavior if the path expression is empty.
  -- returns '{}'
  SELECT JSON_QUERY('{}', 'lax $.invalid' EMPTY OBJECT ON EMPTY);
  <br/>
  -- Behavior if the path expression has an error.
  -- returns '[]'
  SELECT JSON_QUERY('{}', 'strict $.invalid' EMPTY ARRAY ON ERROR);
  ```

<a id="flink-sql-json-quote-function"></a>

## JSON_QUOTE

Quotes a string as a JSON value by wrapping it with double-quote characters.

Syntax
: ```sql
  JSON_QUOTE(string)
  ```

Description
: The `JSON_QUOTE` function quotes a string as a JSON value by wrapping it
  with double-quote characters, escaping interior quote and special characters
  (`"`, `\`, `/`, `b`, `f`, `n`, `r`, `t`), and returning the result as a string.
  <br/>
  If `string` is NULL, the function returns NULL.

Example
: ```sql
  -- returns { "SQL string" }
  SELECT JSON_QUOTE('SQL string');
  ```

<a id="flink-sql-json-string-function"></a>

## JSON_STRING

Serializes a string to JSON.

Syntax
: ```sql
  JSON_STRING(value)
  ```

Description
: The `JSON_STRING` function returns a JSON string containing the serialized
  value. If the value is NULL, the function returns NULL.

Examples
: The following SELECT statements return the values indicated in the comment
  lines.
  <br/>
  ```sql
  -- returns NULL
  SELECT JSON_STRING(CAST(NULL AS INT));
  <br/>
  -- returns '1'
  SELECT JSON_STRING(1);
  <br/>
  -- returns 'true'
  SELECT JSON_STRING(TRUE);
  <br/>
  -- returns '"Hello, World!"'
  JSON_STRING('Hello, World!');
  <br/>
  -- returns '[1,2]'
  JSON_STRING(ARRAY[1, 2])
  ```

<a id="flink-sql-json-unquote-function"></a>

## JSON_UNQUOTE

Unquotes a JSON value.

Syntax
: ```sql
  JSON_UNQUOTE(string)
  ```

Description
: The `JSON_UNQUOTE` function unquotes a JSON value, unescapes escaped
  special characters (`"`, `\`, `/`, `b`, `f`, `n`, `r`, `t`, `u`), and
  returns the result as a string.
  <br/>
  If `string` is NULL, the function returns NULL.
  <br/>
  If `string` doesn’t start and end with double quotes, or if it starts
  and ends with double quotes but is not a valid JSON string literal, the
  value is passed through unmodified.

Example
: ```sql
  -- returns { "SQL string" }
  SELECT JSON_UNQUOTE('"SQL string"');
  ```

<a id="flink-sql-json-value-function"></a>

## JSON_VALUE

Gets a value from a JSON string.

Syntax
: ```sql
  JSON_VALUE(jsonValue, path
    [RETURNING <dataType>]
    [ { NULL | ERROR | DEFAULT <defaultExpr> } ON EMPTY ]
    [ { NULL | ERROR | DEFAULT <defaultExpr> } ON ERROR ])
  ```

Description
: The `JSON_VALUE` function extracts a scalar value from a JSON string.
  It searches a JSON string with the specified path expression and returns
  the value if the value at that path is scalar.
  <br/>
  Non-scalar values can’t be returned.
  <br/>
  By default, the value is returned as `STRING`. Use `RETURNING` to specify
  a different return type. The following return types are supported:
  <br/>
  - `BOOLEAN`
  - `DOUBLE`
  - `INTEGER`
  - `VARCHAR` / `STRING`
  <br/>
  For empty path expressions or errors, you can define a behavior to return NULL,
  raise an error, or return a defined default value instead. The default is
  `NULL ON EMPTY` or `NULL ON ERROR`, respectively. The default value can be
  a literal or an expression. If the default value itself raises an error, it
  falls through to the error behavior for `ON EMPTY` and raises an error for
  `ON ERROR`.
  <br/>
  For paths that contain special characters, like spaces, you can use
  `['property']` or `["property"]` to select the specified property in a
  parent object. Be sure to put single or double quotes around the property name.
  <br/>
  When using JSON_VALUE in SQL, the path is a character parameter that’s already
  single-quoted, so you must escape the single quotes around the property name,
  for example, `JSON_VALUE('{"a b": "true"}', '$.[''a b'']')`.

Examples
: The following SELECT statements return the values indicated in the comment
  lines.
  <br/>
  ```sql
  -- returns "true"
  SELECT JSON_VALUE('{"a": true}', '$.a');
  <br/>
  -- returns TRUE
  SELECT JSON_VALUE('{"a": true}', '$.a' RETURNING BOOLEAN);
  <br/>
  -- returns "false"
  SELECT JSON_VALUE('{"a": true}', 'lax $.b' DEFAULT FALSE ON EMPTY);
  <br/>
  -- returns "false"
  SELECT JSON_VALUE('{"a": true}', 'strict $.b' DEFAULT FALSE ON ERROR);
  <br/>
  -- returns 0.998D
  SELECT JSON_VALUE('{"a.b": [0.998,0.996]}','$.["a.b"][0]' RETURNING DOUBLE);
  <br/>
  -- returns "right"
  SELECT JSON_VALUE('{"contains blank": "right"}', 'strict $.[''contains blank'']' NULL ON EMPTY DEFAULT 'wrong' ON ERROR);
  ```

## Other built-in functions

- [Aggregate Functions](aggregate-functions.md#flink-sql-aggregate-functions)
- [Changelog Conversion Functions](changelog-conversion.md#flink-sql-changelog-conversion-functions)
- [Collection Functions](collection-functions.md#flink-sql-collection-functions)
- [Comparison Functions](comparison-functions.md#flink-sql-comparison-functions)
- [Conditional Functions](conditional-functions.md#flink-sql-conditional-functions)
- [Datetime Functions](datetime-functions.md#flink-sql-datetime-functions)
- [Hash Functions](hash-functions.md#flink-sql-hash-functions)
- [JSON Functions](#flink-sql-json-functions)
- [ML Preprocessing Functions](ml-preprocessing-functions.md#flink-sql-ml-preprocessing-functions)
- [Model Inference Functions](model-inference-functions.md#flink-sql-model-inference-functions)
- [Numeric Functions](numeric-functions.md#flink-sql-numeric-functions)
- [Search Functions](search-functions.md#flink-sql-search-functions)
- [String Functions](string-functions.md#flink-sql-string-functions)
- [Table API Functions](table-api-functions.md#flink-table-api-functions)

## Related content

- [User-defined Functions](../../concepts/user-defined-functions.md#flink-sql-udfs)
- [Create a User Defined Function](../../how-to-guides/create-udf.md#flink-sql-create-udf)

#### NOTE
This website includes content developed at the [Apache Software Foundation](https://www.apache.org/)
under the terms of the [Apache License v2](https://www.apache.org/licenses/LICENSE-2.0.html).