SQL function reference#

Documented scalar and aggregate functions registered by register_all. See Getting started for setup and the cookbook for common workflows.

Function	Description
`is_valid_variant(value)`	Validates a Variant value using Arrow validation.
`is_variant_null(value)`	Returns true if the input is a present Variant null, and false otherwise, including SQL NULL and missing fields.
`parse_json(json)`	Parses a JSON string into a Variant value.
`schema_of_variant(value)`	Returns a SQL schema string for a Variant value.
`schema_of_variant_agg(value)`	Returns a SQL schema string by merging the schemas of Variant values across rows.
`to_json(value[, options])`	Serializes a Variant value to a JSON string.
`to_variant_object(expression)`	Converts an array, struct, or string-keyed map to VARIANT.
`try_parse_json(json)`	Parses a JSON string into a Variant value, returning SQL NULL for invalid JSON.
`try_variant_get(value, path[, type])`	Extracts the value at a path and optionally converts it to a SQL type, returning SQL NULL when the result-type conversion fails.
`variant_delete(v, path1[, path2, ...])`	Deletes object fields or array elements at one or more JSONPaths, applied left to right.
`variant_get(value, path[, type])`	Extracts the value at a path and optionally converts it to a SQL type.

`is_valid_variant`#

Validates a Variant value using Arrow validation. Returns false for malformed bytes. SQL NULL stays SQL NULL; Variant null is valid.

Invalid storage schemas and unsupported reconstruction raise errors. Arrow validation is stricter than Spark for metadata, UTF-8, object keys, offsets, nesting, and date/time ranges. Malformed shredded metadata can still panic in Arrow; see #11069.


Syntax	`is_valid_variant(value)`
Arguments	`value`: Variant expression to validate.

Example

> SELECT is_valid_variant(parse_json('null')) AS valid;
+-------+
| valid |
+-------+
| true  |
+-------+

`is_variant_null`#

Returns true if the input is a present Variant null, and false otherwise, including SQL NULL and missing fields. The result is a non-null BOOLEAN. Checks only the top-level value; nulls inside objects or arrays do not make the container null. This predicate does not validate the full Variant contents.


Syntax	`is_variant_null(value)`
Arguments	`value`: Variant expression or an untyped SQL NULL.
Related	`variant_get`, `parse_json`

Example

> SELECT is_variant_null(parse_json('null')) AS variant_null, is_variant_null(NULL) AS sql_null;
+--------------+----------+
| variant_null | sql_null |
+--------------+----------+
| true         | false    |
+--------------+----------+
> SELECT is_variant_null(variant_get(parse_json('{"a":null}'), '$.a')) AS present, is_variant_null(variant_get(parse_json('{"a":null}'), '$.b')) AS missing;
+---------+---------+
| present | missing |
+---------+---------+
| true    | false   |
+---------+---------+

`parse_json`#

Parses a JSON string into a Variant value. Invalid JSON raises an error. SQL NULL returns SQL NULL; the JSON text null produces a present Variant null.

Uses Arrow's JSON parser. Fractional JSON numbers become DOUBLE; decimal precision and duplicate-key handling have known differences from Spark (see #71).


Syntax	`parse_json(json)`
Arguments	`json`: String expression containing a JSON value.
Related	`try_parse_json`, `to_json`

Example

> SELECT to_json(parse_json('{"a":1}')) AS json;
+---------+
| json    |
+---------+
| {"a":1} |
+---------+
> SELECT parse_json(NULL) IS NULL AS sql_null, is_variant_null(parse_json('null')) AS variant_null;
+----------+--------------+
| sql_null | variant_null |
+----------+--------------+
| true     | true         |
+----------+--------------+

`schema_of_variant`#

Returns a SQL schema string for a Variant value. SQL NULL returns SQL NULL; Variant null has schema VOID. Object fields are sorted by name. Array element schemas are merged, widening compatible types and using VARIANT for incompatible types.

Fractional numbers parsed by parse_json have schema DOUBLE, unlike Spark's decimal inference. Use CAST(decimal_value AS VARIANT) to preserve decimal types.


Syntax	`schema_of_variant(value)`
Arguments	`value`: Variant expression whose schema is inferred, or an untyped SQL NULL.

Example

> SELECT schema_of_variant(parse_json('null')) AS schema;
+--------+
| schema |
+--------+
| VOID   |
+--------+
> SELECT schema_of_variant(parse_json('[{"b":true,"a":0}]')) AS schema;
+--------------------------------------+
| schema                               |
+--------------------------------------+
| ARRAY<OBJECT<a: BIGINT, b: BOOLEAN>> |
+--------------------------------------+

`schema_of_variant_agg`#

Returns a SQL schema string by merging the schemas of Variant values across rows. SQL NULL values are ignored. Empty input, all-SQL-NULL input, and all-Variant-null input return VOID. Compatible types are widened; incompatible types become VARIANT. Object fields are merged by name and sorted in the output. Array element schemas are merged using the same rules.

Fractional numbers parsed by parse_json contribute DOUBLE, unlike Spark's decimal inference. Use CAST(decimal_value AS VARIANT) to preserve decimal types.


Syntax	`schema_of_variant_agg(value)`
Arguments	`value`: Variant expression to aggregate, or an untyped SQL NULL.

Example

> SELECT schema_of_variant_agg(parse_json(j)) AS schema FROM (VALUES ('1'), ('2'), ('3')) AS t(j);
+--------+
| schema |
+--------+
| BIGINT |
+--------+
> SELECT schema_of_variant_agg(parse_json(j)) AS schema FROM (VALUES ('{"a":1}'), ('{"b":true}'), ('{"c":1.23}')) AS t(j);
+------------------------------------------+
| schema                                   |
+------------------------------------------+
| OBJECT<a: BIGINT, b: BOOLEAN, c: DOUBLE> |
+------------------------------------------+

`to_json`#

Serializes a Variant value to a JSON string. SQL NULL returns SQL NULL; Variant null becomes the string null. Strings retain their JSON quotes. This function accepts Variant values only. The optional second argument is currently ignored; serialization options are not implemented.


Syntax	`to_json(value[, options])`
Arguments	`value`: Variant expression to serialize. `options`: Optional argument, currently ignored.
Related	`parse_json`, `try_parse_json`

Example

> SELECT to_json(parse_json('"hello"')) AS json;
+---------+
| json    |
+---------+
| "hello" |
+---------+
> SELECT to_json(parse_json('null')) AS json, to_json(parse_json(NULL)) IS NULL AS sql_null;
+------+----------+
| json | sql_null |
+------+----------+
| null | true     |
+------+----------+

`to_variant_object`#

Converts an array, struct, or string-keyed map to VARIANT. Structs and maps become unordered objects. SQL NULL inputs remain SQL NULL; null array elements become Variant null.

Unlike Spark, null object fields are currently omitted and duplicate object keys are not rejected. Nested VARIANT inputs are not supported.

Use CAST(value AS VARIANT) for scalar inputs.


Syntax	`to_variant_object(expression)`
Arguments	`expression`: An array, struct, or map. Map keys must be strings, including in nested maps.
Related	`parse_json`, `to_json`

Example

> SELECT to_json(to_variant_object(named_struct('a', 1))) AS json;
+---------+
| json    |
+---------+
| {"a":1} |
+---------+

`try_parse_json`#

Parses a JSON string into a Variant value, returning SQL NULL for invalid JSON. In a column, a malformed value produces NULL for that row. SQL NULL returns SQL NULL; the JSON text null produces a present Variant null. Invalid argument types or counts still raise errors. Shares the parser limitations of parse_json.


Syntax	`try_parse_json(json)`
Arguments	`json`: String expression containing a JSON value.
Related	`parse_json`, `to_json`

Example

> SELECT to_json(try_parse_json('{"a":1}')) AS json;
+---------+
| json    |
+---------+
| {"a":1} |
+---------+
> SELECT try_parse_json('{') IS NULL AS invalid_json, is_variant_null(try_parse_json('null')) AS variant_null;
+--------------+--------------+
| invalid_json | variant_null |
+--------------+--------------+
| true         | true         |
+--------------+--------------+

`try_variant_get`#

Extracts the value at a path and optionally converts it to a SQL type, returning SQL NULL when the result-type conversion fails. Uses the same paths, type hints, and null handling as variant_get. Without a type hint, returns Variant. Invalid paths and invalid type hints still raise errors.

Register with register_variant_get_functions to enable TRY_CAST of getters. Also register VariantExprPlanner for : field access. TRY_CAST of an untyped getter or colon access uses the same conversion policy as try_variant_get.


Syntax	`try_variant_get(value, path[, type])`
Alternative syntax	`TRY_CAST(variant_get(value, path) AS type)` `TRY_CAST(value:field AS type)`
Arguments	`value`: Variant expression to extract from. `path`: String expression using the path syntax described for `variant_get`. `type`: Optional non-null SQL type string literal. Defaults to `VARIANT`; accepts the same types as `variant_get`.
Related	`variant_get`, `is_variant_null`

Example

> SELECT try_variant_get(parse_json('{"a":1}'), '$.a', 'BIGINT') AS value;
+-------+
| value |
+-------+
| 1     |
+-------+
> SELECT try_variant_get(parse_json('[1,"hello"]'), '$[1]', 'BIGINT') IS NULL AS failed_cast;
+-------------+
| failed_cast |
+-------------+
| true        |
+-------------+
> WITH input AS (SELECT parse_json('{"price":"invalid"}') AS v)
  SELECT try_variant_get(v, '$.price', 'DECIMAL(12,2)') IS NULL AS type_hint,
         TRY_CAST(variant_get(v, '$.price') AS DECIMAL(12,2)) IS NULL AS sql_cast,
         TRY_CAST(v:price AS DECIMAL(12,2)) IS NULL AS colon_cast
  FROM input;
+-----------+----------+------------+
| type_hint | sql_cast | colon_cast |
+-----------+----------+------------+
| true      | true     | true       |
+-----------+----------+------------+

`variant_delete`#

Deletes object fields or array elements at one or more JSONPaths, applied left to right. Missing targets and type mismatches leave the value unchanged. SQL NULL input returns SQL NULL; NULL paths are skipped. Paths must start with $ and cannot select the root alone.


Syntax	`variant_delete(v, path1[, path2, ...])`
Arguments	`v`: Variant expression to update. `path`: String expression identifying a field or array element.

Example

> SELECT variant_delete(parse_json('{"a":[1,2,3]}'), '$.a[0]', '$.a[0]') AS v;
+-----------+
| v         |
+-----------+
| {"a":[3]} |
+-----------+

`variant_get`#

Extracts the value at a path and optionally converts it to a SQL type. Without a type hint, returns Variant. SQL NULL input, a SQL NULL path, and missing paths return SQL NULL. Variant null remains a Variant null for Variant output and becomes SQL NULL for typed output. Invalid paths, invalid type hints, and failed conversions raise errors.

Type names follow DataFusion SQL semantics, including session settings. For example, INT8 means BIGINT; Arrow type names such as Int64 are not accepted. Nested Variant target types are not supported.

Register with register_variant_get_functions to enable SQL casts of getters. Also register VariantExprPlanner for : field access. A cast of an untyped getter or colon access performs the same conversion as an explicit type hint. Casts of already typed getters retain the intermediate conversion. The registration helper follows session type settings, including SET / RESET. Use VARIANT as the type hint or cast target to retain a Variant result.


Syntax	`variant_get(value, path[, type])`
Alternative syntax	`CAST(variant_get(value, path) AS type)` `value:field` `value:field::type`
Arguments	`value`: Variant expression to extract from. `path`: String expression starting with `$`, such as `$.a.b[0]`. `$` selects the root. Use `$["a.b"]` and `$["$name"]` for literal field names. Quoted keys preserve backslashes (Spark 4.2). `type`: Optional non-null SQL type string literal, such as `BIGINT`, `DECIMAL(12,2)`, or `ARRAY<INT>`. Defaults to `VARIANT`.
Related	`try_variant_get`, `is_variant_null`

Example

> SELECT variant_get(parse_json('{"a":1}'), '$.a', 'BIGINT') AS value;
+-------+
| value |
+-------+
| 1     |
+-------+
> SELECT variant_get(parse_json('{"a":1}'), '$.b', 'BIGINT') IS NULL AS missing;
+---------+
| missing |
+---------+
| true    |
+---------+
> SELECT to_json(variant_get(parse_json('[1,"hello"]'), '$[1]')) AS json;
+---------+
| json    |
+---------+
| "hello" |
+---------+
> WITH input AS (SELECT parse_json('{"price":12.5}') AS v)
  SELECT variant_get(v, '$.price', 'DECIMAL(12,2)') AS type_hint,
         CAST(variant_get(v, '$.price') AS DECIMAL(12,2)) AS sql_cast,
         v:price::DECIMAL(12,2) AS colon_cast
  FROM input;
+-----------+----------+------------+
| type_hint | sql_cast | colon_cast |
+-----------+----------+------------+
| 12.50     | 12.50    | 12.50      |
+-----------+----------+------------+

SQL function reference#

is_valid_variant#

is_variant_null#

parse_json#

schema_of_variant#

schema_of_variant_agg#

to_json#

to_variant_object#

try_parse_json#

try_variant_get#

variant_delete#

variant_get#

`is_valid_variant`#

`is_variant_null`#

`parse_json`#

`schema_of_variant`#

`schema_of_variant_agg`#

`to_json`#

`to_variant_object`#

`try_parse_json`#

`try_variant_get`#

`variant_delete`#

`variant_get`#