Variant Functions

This page lists all variant functions available in Spark SQL.

is_valid_variant

is_valid_variant(v) - Returns true if the variant is valid, false if it is malformed, NULL if v is NULL.

Arguments:

  • v - A variant value to validate.

Examples:

> SELECT is_valid_variant(parse_json('null'));
 true
> SELECT is_valid_variant(parse_json('[{"b":true,"a":0}]'));
 true

Since: 4.2.0


is_variant_null

is_variant_null(expr) - Check if a variant value is a variant null. Returns true if and only if the input is a variant null and false otherwise (including in the case of SQL NULL).

Arguments:

  • expr - A variant value to check.

Examples:

> SELECT is_variant_null(parse_json('null'));
 true
> SELECT is_variant_null(parse_json('"null"'));
 false
> SELECT is_variant_null(parse_json('13'));
 false
> SELECT is_variant_null(parse_json(null));
 false
> SELECT is_variant_null(variant_get(parse_json('{"a":null, "b":"spark"}'), "$.c"));
 false
> SELECT is_variant_null(variant_get(parse_json('{"a":null, "b":"spark"}'), "$.a"));
 true

Since: 4.0.0


parse_json

parse_json(jsonStr) - Parse a JSON string as a Variant value. Throw an exception when the string is not valid JSON value.

Arguments:

  • jsonStr - The JSON string to parse as a Variant value. An expression that evaluates to a string.

Examples:

> SELECT parse_json('{"a":1,"b":0.8}');
 {"a":1,"b":0.8}

Since: 4.0.0


schema_of_variant

schema_of_variant(v) - Returns schema in the SQL format of a variant.

Arguments:

  • v - A variant value whose schema is returned.

Examples:

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

Since: 4.0.0


schema_of_variant_agg

schema_of_variant_agg(v) - Returns the merged schema in the SQL format of a variant column.

Arguments:

  • v - A variant column whose per-row schemas are merged into a single schema.

Examples:

> SELECT schema_of_variant_agg(parse_json(j)) FROM VALUES ('1'), ('2'), ('3') AS tab(j);
 BIGINT
> SELECT schema_of_variant_agg(parse_json(j)) FROM VALUES ('{"a": 1}'), ('{"b": true}'), ('{"c": 1.23}') AS tab(j);
 OBJECT<a: BIGINT, b: BOOLEAN, c: DECIMAL(3,2)>

Since: 4.0.0


to_variant_object

to_variant_object(expr) - Convert a nested input (array/map/struct) into a variant where maps and structs are converted to variant objects which are unordered unlike SQL structs. Input maps can only have string keys.

Arguments:

  • expr - A nested value of array, map, or struct type. Maps must have string keys. Nested values of any type are allowed.

Examples:

> SELECT to_variant_object(named_struct('a', 1, 'b', 2));
 {"a":1,"b":2}
> SELECT to_variant_object(array(1, 2, 3));
 [1,2,3]
> SELECT to_variant_object(array(named_struct('a', 1)));
 [{"a":1}]
> SELECT to_variant_object(array(map("a", 2)));
 [{"a":2}]

Since: 4.0.0


try_parse_json

try_parse_json(jsonStr) - Parse a JSON string as a Variant value. Return NULL when the string is not valid JSON value.

Arguments:

  • jsonStr - The JSON string to parse as a Variant value. An expression that evaluates to a string.

Examples:

> SELECT try_parse_json('{"a":1,"b":0.8}');
 {"a":1,"b":0.8}
> SELECT try_parse_json('{"a":1,');
 NULL

Since: 4.0.0


try_variant_array_append

try_variant_array_append(v, path, val) - Appends a value to the array in a variant at the given JSONPath location. Returns the variant unchanged if a path key or index is absent. Returns NULL if a path segment hits a value of an incompatible type, the target is not an array, or if any argument is NULL.

Arguments:

  • v - A variant value to mutate.
  • path - A string expression evaluating to a JSONPath identifying the target array. A valid path should start with $ and is followed by zero or more segments like [123], .name, ['name'], or ["name"].
  • val - Any expression castable to variant.

Examples:

> SELECT try_variant_array_append(parse_json('[1, 2, 3]'), '$', 4);
 [1,2,3,4]
> SELECT try_variant_array_append(parse_json('{"a": [1, 2]}'), '$.a', 3);
 {"a":[1,2,3]}
> SELECT try_variant_array_append(parse_json('[1]'), '$', parse_json('[2, 3]'));
 [1,[2,3]]
> SELECT try_variant_array_append(parse_json('{"a": 1}'), '$.missing', 2);
 {"a":1}
> SELECT try_variant_array_append(parse_json('{"a": 1}'), '$.a', 2);
 NULL
> SELECT try_variant_array_append(parse_json('[1, 2]'), '$', null);
 NULL

Since: 4.3.0


try_variant_get

try_variant_get(v, path[, type]) - Extracts a sub-variant from v according to path, and then cast the sub-variant to type. When type is omitted, it is default to variant. Returns null if the path does not exist or the cast fails.

Arguments:

  • v - A variant value to extract from.
  • path - A string literal in JSONPath format that identifies the sub-variant to extract.
  • type - An optional string literal naming the SQL type to cast the extracted sub-variant to. When omitted, it defaults to variant.

Examples:

> SELECT try_variant_get(parse_json('{"a": 1}'), '$.a', 'int');
 1
> SELECT try_variant_get(parse_json('{"a": 1}'), '$.b', 'int');
 NULL
> SELECT try_variant_get(parse_json('[1, "2"]'), '$[1]', 'string');
 2
> SELECT try_variant_get(parse_json('[1, "2"]'), '$[2]', 'string');
 NULL
> SELECT try_variant_get(parse_json('[1, "hello"]'), '$[1]');
 "hello"
> SELECT try_variant_get(parse_json('[1, "hello"]'), '$[1]', 'int');
 NULL

Since: 4.0.0


try_variant_insert

try_variant_insert(v, path, val) - Inserts a value into a variant at the given JSONPath location. An object path adds a new field; an array path inserts at the index, shifting later elements right. Missing intermediate keys are created. Returns NULL if the field already exists or a path segment hits a value of an incompatible type, or if any argument is NULL.

Arguments:

  • v - A variant value to mutate.
  • path - A string expression evaluating to a JSONPath identifying the insertion target. A valid path should start with $ and is followed by one or more segments like [123], .name, ['name'], or ["name"]. The root path $ is not allowed.
  • val - Any expression castable to variant.

Examples:

> SELECT try_variant_insert(parse_json('{"a": 1}'), '$.b', 2);
 {"a":1,"b":2}
> SELECT try_variant_insert(parse_json('{}'), '$.a.b', 1);
 {"a":{"b":1}}
> SELECT try_variant_insert(parse_json('["a","b","c"]'), '$[1]', 'z');
 ["a","z","b","c"]
> SELECT try_variant_insert(parse_json('["a","b"]'), '$[5]', 'z');
 ["a","b",null,null,null,"z"]
> SELECT try_variant_insert(parse_json('{"a": 1}'), '$.a', 2);
 NULL
> SELECT try_variant_insert(parse_json('{"a": 1}'), '$.a.b', 2);
 NULL
> SELECT try_variant_insert(parse_json('{}'), '$.a', null);
 NULL

Since: 4.3.0


try_variant_set

try_variant_set(v, path, val[, create_if_missing]) - Sets or upserts a value in a variant at the given JSONPath location. An existing object field or array element at the target is replaced. A missing field, array index, or intermediate path is created, unless create_if_missing is false, in which case the variant is left unchanged. Returns NULL if a path segment hits a value of an incompatible type, or if any argument is NULL.

Arguments:

  • v - A variant value to mutate.
  • path - A string expression evaluating to a JSONPath identifying the set target. A valid path should start with $ and is followed by one or more segments like [123], .name, ['name'], or ["name"]. The root path $ is not allowed.
  • val - Any expression castable to variant.
  • create_if_missing - An optional boolean (default true). Must be a constant.

Examples:

> SELECT try_variant_set(parse_json('{"a": 1}'), '$.a', 2);
 {"a":2}
> SELECT try_variant_set(parse_json('{"a": 1}'), '$.b', 3);
 {"a":1,"b":3}
> SELECT try_variant_set(parse_json('{"a": {"b": 1}}'), '$.a.c.d', 2);
 {"a":{"b":1,"c":{"d":2}}}
> SELECT try_variant_set(parse_json('["a","b","c"]'), '$[1]', 'z');
 ["a","z","c"]
> SELECT try_variant_set(parse_json('["a","b","c"]'), '$[5]', 'z');
 ["a","b","c",null,null,"z"]
> SELECT try_variant_set(parse_json('{"a": 1}'), '$.b', 2, false);
 {"a":1}
> SELECT try_variant_set(parse_json('{"a": 1}'), '$.a.b', 2);
 NULL
> SELECT try_variant_set(parse_json('{"a": 1}'), '$.a', null);
 NULL

Since: 4.3.0


variant_array_append

variant_array_append(v, path, val) - Appends a value to the array in a variant at the given JSONPath location. Returns the variant unchanged if a path key or index is absent. Throws an error if a path segment hits a value of an incompatible type or the target is not an array. Returns NULL if any argument is NULL.

Arguments:

  • v - A variant value to mutate.
  • path - A string expression evaluating to a JSONPath identifying the target array. A valid path should start with $ and is followed by zero or more segments like [123], .name, ['name'], or ["name"].
  • val - Any expression castable to variant.

Examples:

> SELECT variant_array_append(parse_json('[1, 2, 3]'), '$', 4);
 [1,2,3,4]
> SELECT variant_array_append(parse_json('{"a": [1, 2]}'), '$.a', 3);
 {"a":[1,2,3]}
> SELECT variant_array_append(parse_json('[1]'), '$', parse_json('[2, 3]'));
 [1,[2,3]]
> SELECT variant_array_append(parse_json('{"a": 1}'), '$.missing', 2);
 {"a":1}
> SELECT variant_array_append(parse_json('[1, 2]'), '$', parse_json('null'));
 [1,2,null]
> SELECT variant_array_append(parse_json('[1, 2]'), '$', null);
 NULL

Since: 4.3.0


variant_delete

variant_delete(v, path1[, path2, ...]) - Removes fields or array elements from a variant at the given JSONPath locations. Multiple paths are applied left to right. Returns NULL if v is NULL; NULL paths are skipped.

Arguments:

  • v - A variant value to mutate.
  • path1, path2, ... - One or more string expressions, each evaluating to a JSONPath identifying a deletion target. A valid path should start with $ and is followed by one or more segments like [123], .name, ['name'], or ["name"]. The root path $ is not allowed.

Examples:

> SELECT variant_delete(parse_json('{"a": 1, "b": 2, "c": 3, "items": [1, 2, 3]}'), NULL, '$.a', '$.c');
 {"b":2,"items":[1,2,3]}
> SELECT variant_delete(parse_json('{"a": 1, "b": 2, "c": 3, "items": [1, 2, 3]}'), '$.missing');
 {"a":1,"b":2,"c":3,"items":[1,2,3]}
> SELECT variant_delete(parse_json('{"a": 1, "b": 2, "c": 3, "items": [1, 2, 3]}'), '$.items[0]', '$.items[0]');
 {"a":1,"b":2,"c":3,"items":[3]}
> SELECT variant_delete(NULL, '$.a');
 NULL

Since: 4.3.0


variant_explode

variant_explode(expr) - It separates a variant object/array into multiple rows containing its fields/elements. Its result schema is struct<pos int, key string, value variant>. pos is the position of the field/element in its parent object/array, and value is the field/element value. key is the field name when exploding a variant object, or is NULL when exploding a variant array. It ignores any input that is not a variant array/object, including SQL NULL, variant null, and any other variant values.

Examples:

> SELECT * from variant_explode(parse_json('["hello", "world"]'));
 0  NULL    "hello"
 1  NULL    "world"
> SELECT * from variant_explode(input => parse_json('{"a": true, "b": 3.14}'));
 0  a   true
 1  b   3.14

Since: 4.0.0


variant_explode_outer

variant_explode_outer(expr) - It separates a variant object/array into multiple rows containing its fields/elements. Its result schema is struct<pos int, key string, value variant>. pos is the position of the field/element in its parent object/array, and value is the field/element value. key is the field name when exploding a variant object, or is NULL when exploding a variant array. It ignores any input that is not a variant array/object, including SQL NULL, variant null, and any other variant values.

Examples:

> SELECT * from variant_explode_outer(parse_json('["hello", "world"]'));
 0  NULL    "hello"
 1  NULL    "world"
> SELECT * from variant_explode_outer(input => parse_json('{"a": true, "b": 3.14}'));
 0  a   true
 1  b   3.14

Since: 4.0.0


variant_from_arrays

variant_from_arrays(keys, values) - Creates a variant object from the given arrays of keys and values. The keys must be non-null strings and the two arrays must have the same length.

Arguments:

  • keys - An array of non-null strings used as the object keys.
  • values - An array of values, with the same length as the keys array.

Examples:

> SELECT variant_from_arrays(array('a', 'b'), array(1, 2));
 {"a":1,"b":2}

Since: 4.4.0


variant_from_entries

variant_from_entries(entries) - Creates a variant object from an array of key/value struct entries. The keys must be non-null strings.

Arguments:

  • entries - An array of key/value structs, where the first field is a non-null string key.

Examples:

> SELECT variant_from_entries(array(struct('a', 1), struct('b', 2)));
 {"a":1,"b":2}

Since: 4.4.0


variant_get

variant_get(v, path[, type]) - Extracts a sub-variant from v according to path, and then cast the sub-variant to type. When type is omitted, it is default to variant. Returns null if the path does not exist. Throws an exception if the cast fails.

Arguments:

  • v - A variant value to extract from.
  • path - A string literal in JSONPath format that identifies the sub-variant to extract.
  • type - An optional string literal naming the SQL type to cast the extracted sub-variant to. When omitted, it defaults to variant.

Examples:

> SELECT variant_get(parse_json('{"a": 1}'), '$.a', 'int');
 1
> SELECT variant_get(parse_json('{"a": 1}'), '$.b', 'int');
 NULL
> SELECT variant_get(parse_json('[1, "2"]'), '$[1]', 'string');
 2
> SELECT variant_get(parse_json('[1, "2"]'), '$[2]', 'string');
 NULL
> SELECT variant_get(parse_json('[1, "hello"]'), '$[1]');
 "hello"

Since: 4.0.0


variant_insert

variant_insert(v, path, val) - Inserts a value into a variant at the given JSONPath location. An object path adds a new field (error if it already exists); an array path inserts at the index, shifting later elements right. Missing intermediate keys are created. Throws an error if a path segment hits a value of an incompatible type. Returns NULL if any argument is NULL.

Arguments:

  • v - A variant value to mutate.
  • path - A string expression evaluating to a JSONPath identifying the insertion target. A valid path should start with $ and is followed by one or more segments like [123], .name, ['name'], or ["name"]. The root path $ is not allowed.
  • val - Any expression castable to variant.

Examples:

> SELECT variant_insert(parse_json('{"a": 1}'), '$.b', 2);
 {"a":1,"b":2}
> SELECT variant_insert(parse_json('{}'), '$.a.b', 1);
 {"a":{"b":1}}
> SELECT variant_insert(parse_json('["a","b","c"]'), '$[1]', 'z');
 ["a","z","b","c"]
> SELECT variant_insert(parse_json('["a","b"]'), '$[5]', 'z');
 ["a","b",null,null,null,"z"]
> SELECT variant_insert(parse_json('{}'), '$.a', parse_json('{"x":1}'));
 {"a":{"x":1}}
> SELECT variant_insert(parse_json('{}'), '$.a', parse_json('null'));
 {"a":null}
> SELECT variant_insert(parse_json('{}'), '$.a', null);
 NULL

Since: 4.3.0


variant_pick

variant_pick(v, path1[, path2, ...]) - Keeps only the fields or array elements of a variant at the given JSONPath locations, preserving their enclosing structure; kept array elements are compacted into a new array in their original order. If no path matches, an object or array input yields an empty object or array, while a scalar or variant-null input is unchanged. Returns NULL if v is NULL; NULL paths are skipped.

Arguments:

  • v - A variant value to project.
  • path1, path2, ... - One or more string expressions, each evaluating to a JSONPath identifying a substructure to keep. A valid path should start with $ and is followed by zero or more segments like [123], .name, ['name'], or ["name"].

Examples:

> SELECT variant_pick(parse_json('{"a": 1, "b": 2, "c": 3}'), '$.a', '$.c');
 {"a":1,"c":3}
> SELECT variant_pick(parse_json('{"a": {"b": 1, "c": 2}, "d": 3}'), '$.a.b');
 {"a":{"b":1}}
> SELECT variant_pick(parse_json('[10, 20, 30, 40]'), '$[0]', '$[2]');
 [10,30]
> SELECT variant_pick(parse_json('{"a": 1, "b": 2}'), NULL, '$.a', '$.missing');
 {"a":1}
> SELECT variant_pick(parse_json('{"a": {"b": 1}}'), '$.a.x');
 {}
> SELECT variant_pick(parse_json('42'), '$.a');
 42
> SELECT variant_pick(NULL, '$.a');
 NULL

Since: 4.4.0


variant_set

variant_set(v, path, val[, create_if_missing]) - Sets or upserts a value in a variant at the given JSONPath location. An existing object field or array element at the target is replaced. A missing field, array index, or intermediate path is created, unless create_if_missing is false, in which case the variant is left unchanged. Throws an error if a path segment hits a value of an incompatible type. Returns NULL if any argument is NULL.

Arguments:

  • v - A variant value to mutate.
  • path - A string expression evaluating to a JSONPath identifying the set target. A valid path should start with $ and is followed by one or more segments like [123], .name, ['name'], or ["name"]. The root path $ is not allowed.
  • val - Any expression castable to variant.
  • create_if_missing - An optional boolean (default true). Must be a constant.

Examples:

> SELECT variant_set(parse_json('{"a": 1}'), '$.a', 2);
 {"a":2}
> SELECT variant_set(parse_json('{"a": 1}'), '$.b', 3);
 {"a":1,"b":3}
> SELECT variant_set(parse_json('{"a": {"b": 1}}'), '$.a.c.d', 2);
 {"a":{"b":1,"c":{"d":2}}}
> SELECT variant_set(parse_json('["a","b","c"]'), '$[1]', 'z');
 ["a","z","c"]
> SELECT variant_set(parse_json('["a","b","c"]'), '$[5]', 'z');
 ["a","b","c",null,null,"z"]
> SELECT variant_set(parse_json('{"a": 1}'), '$.b', 2, false);
 {"a":1}
> SELECT variant_set(parse_json('{"a": 1}'), '$.a', parse_json('null'));
 {"a":null}
> SELECT variant_set(parse_json('{"a": 1}'), '$.a', null);
 NULL

Since: 4.3.0


variant_strip_nulls

variant_strip_nulls(v[, include_arrays]) - Recursively removes object fields and array elements whose value is a variant null, unless include_arrays is false, in which case null array elements are kept. Returns NULL if any argument is NULL.

Arguments:

  • v - The variant value to strip.
  • include_arrays - An optional boolean (default true). Must be a constant.

Examples:

> SELECT variant_strip_nulls(parse_json('{"a": 1, "b": null, "c": 3}'));
 {"a":1,"c":3}
> SELECT variant_strip_nulls(parse_json('[1, null, 3]'));
 [1,3]
> SELECT variant_strip_nulls(parse_json('{"a": {"b": null, "c": [1, null]}}'));
 {"a":{"c":[1]}}
> SELECT variant_strip_nulls(parse_json('{"a": [1, null], "b": null}'), false);
 {"a":[1,null]}
> SELECT variant_strip_nulls(parse_json('{"a": null}'));
 {}
> SELECT variant_strip_nulls(parse_json('null'));
 null
> SELECT variant_strip_nulls(NULL);
 NULL

Since: 4.3.0