Skip to content

sql-reference.md

Latest commit

 

History

History
457 lines (350 loc) · 13 KB

sql-reference.md

File metadata and controls

457 lines (350 loc) · 13 KB
Raw

SQL function reference

Documented scalar and aggregate functions registered by register_all. See the README for setup and common workflows.

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.

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.

is_variant_null(value)

Arguments

  • value: Variant expression or an untyped SQL NULL.

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   |
+---------+---------+

Related functions

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).

parse_json(json)

Arguments

  • json: String expression containing a JSON value.

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         |
+----------+--------------+

Related functions

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.

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.

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.

to_json(value[, options])

Arguments

  • value: Variant expression to serialize.
  • options: Optional argument, currently ignored.

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     |
+------+----------+

Related functions

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.

to_variant_object(expression)

Arguments

  • expression: An array, struct, or map. Map keys must be strings, including in nested maps.

Example

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

Related functions

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.

try_parse_json(json)

Arguments

  • json: String expression containing a JSON value.

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         |
+--------------+--------------+

Related functions

try_variant_get

Extracts the value at a path and optionally converts it to a SQL type. Uses the same paths, type hints, and null handling as variant_get, but failed result-type conversions return SQL NULL. 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.

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.

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       |
+-----------+----------+------------+

Related functions

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.

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 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.

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 selecting fields with dots and array elements with zero-based brackets, such as a.b[0]. Use ["a.b"] for a literal key containing a dot. An empty string or SQL NULL selects the root. Use field paths without Spark's leading $; here $ is treated as a field name. Keys containing ] currently require escaping that bracket with a backslash, even inside quotes.
  • type: Optional non-null SQL type string literal, such as BIGINT, DECIMAL(12,2), or ARRAY<INT>. Defaults to VARIANT.

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      |
+-----------+----------+------------+

Related functions