Variant Functions¶
This page lists all variant functions available in Spark SQL.
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).
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.
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.
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.
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.
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.
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_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.
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
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_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.
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