JSON, XML, CSV and Variant functions

Every Spark SQL JSON, XML, CSV, Variant, Avro and Protobuf function on Databasin Sail: whether it is supported, how it differs from Apache Spark, and examples with results.

Last updated October 20, 2026

Reading time 11 min read

This page lists every Spark SQL JSON, XML, CSV, Variant, Avro and Protobuf function: 28 work on Databasin Sail as in Apache Spark, 5 work with a difference noted below and 5 aren't supported. Each function shows its status on Sail, its usage, and examples whose results on Sail match Spark's.

Every supported function also works from PySpark through pyspark.sql.functions when PySpark has a wrapper for it. The Sail SQL reference lists every function page.

JSON

from_json · get_json_object · json_array_length · json_object_keys · json_tuple · schema_of_json · to_json

from_json

Supported.

from_json(jsonStr, schema[, options]): Returns a struct value with the given jsonStr and schema.

Since Spark 2.2.0.

SELECT from_json('{"a":1, "b":0.8}', 'a INT, b DOUBLE');
-- {"a":1,"b":0.8}

SELECT from_json('{"time":"26/08/2015"}', 'time Timestamp', map('timestampFormat', 'dd/MM/yyyy'));
-- {"time":2015-08-26 00:00:00}

SELECT from_json('{"teacher": "Alice", "student": [{"name": "Bob", "rank": 1}, {"name": "Charlie", "rank": 2}]}', 'STRUCT<teacher: STRING, student: ARRAY<STRUCT<name: STRING, rank: INT>>>');
-- {"teacher":"Alice","student":[{"name":"Bob","rank":1},{"name":"Charlie","rank":2}]}

get_json_object

Supported.

get_json_object(json_txt, path): Extracts a json object from path.

Since Spark 1.5.0.

SELECT get_json_object('{"a":"b"}', '$.a');
-- b

SELECT get_json_object('[{"a":"b"},{"a":"c"}]', '$[0].a');
-- b

SELECT get_json_object('[{"a":"b"},{"a":"c"}]', '$[*].a');
-- ["b","c"]

json_array_length

Supported.

json_array_length(jsonArray): Returns the number of elements in the outermost JSON array.

jsonArray: A JSON array. NULL is returned in case of any other valid JSON string, NULL or an invalid JSON.

Since Spark 3.1.0.

SELECT json_array_length('[1,2,3,4]');
-- 4

SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');
-- 5

SELECT json_array_length('[1,2');
-- NULL

json_object_keys

Supported.

json_object_keys(json_object): Returns all the keys of the outermost JSON object as an array.

json_object: A JSON object. If a valid JSON object is given, all the keys of the outermost object will be returned as an array. If it is any other valid JSON string, an invalid JSON string or an empty string, the function returns null.

Since Spark 3.1.0.

SELECT json_object_keys('{}');
-- []

SELECT json_object_keys('{"key": "value"}');
-- ["key"]

SELECT json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}');
-- ["f1","f2"]

json_tuple

Supported.

json_tuple(jsonStr, p1, p2, ..., pn): Returns a tuple like the function get_json_object, but it takes multiple names. All the input parameters and output column types are string.

Since Spark 1.6.0.

SELECT json_tuple('{"a":1, "b":2}', 'a', 'b');
-- 1 | 2

schema_of_json

Supported.

schema_of_json(json[, options]): Returns schema in the DDL format of JSON string.

Since Spark 2.4.0.

SELECT schema_of_json('[{"col":0}]');
-- ARRAY<STRUCT<col: BIGINT>>

SELECT schema_of_json('[{"col":01}]', map('allowNumericLeadingZeros', 'true'));
-- ARRAY<STRUCT<col: BIGINT>>

to_json

Supported.

to_json(expr[, options]): Returns a JSON string with a given struct value

Since Spark 2.2.0.

SELECT to_json(named_struct('a', 1, 'b', 2));
-- {"a":1,"b":2}

SELECT to_json(named_struct('time', to_timestamp('2015-08-26', 'yyyy-MM-dd')), map('timestampFormat', 'dd/MM/yyyy'));
-- {"time":"26/08/2015"}

SELECT to_json(array(named_struct('a', 1, 'b', 2)));
-- [{"a":1,"b":2}]

SELECT to_json(map('a', named_struct('b', 1)));
-- {"a":{"b":1}}

SELECT to_json(map(named_struct('a', 1),named_struct('b', 2)));
-- {"[1]":{"b":2}}

SELECT to_json(map('a', 1));
-- {"a":1}

SELECT to_json(array(map('a', 1)));
-- [{"a":1}]

SELECT to_json(named_struct('b', 1, 'a', 2), map('sortKeys', 'true'));
-- {"a":2,"b":1}

XML

from_xml · schema_of_xml · to_xml · xpath · xpath_boolean · xpath_double · xpath_float · xpath_int · xpath_long · xpath_number · xpath_short · xpath_string

from_xml

Supported.

from_xml(xmlStr, schema[, options]): Returns a struct value with the given xmlStr and schema.

Since Spark 4.0.0.

SELECT from_xml('<p><a>1</a><b>0.8</b></p>', 'a INT, b DOUBLE');
-- {"a":1,"b":0.8}

SELECT from_xml('<p><time>26/08/2015</time></p>', 'time Timestamp', map('timestampFormat', 'dd/MM/yyyy'));
-- {"time":2015-08-26 00:00:00}

SELECT from_xml('<p><teacher>Alice</teacher><student><name>Bob</name><rank>1</rank></student><student><name>Charlie</name><rank>2</rank></student></p>', 'STRUCT<teacher: STRING, student: ARRAY<STRUCT<name: STRING, rank: INT>>>');
-- {"teacher":"Alice","student":[{"name":"Bob","rank":1},{"name":"Charlie","rank":2}]}

schema_of_xml

Supported.

schema_of_xml(xml[, options]): Returns schema in the DDL format of XML string.

Since Spark 4.0.0.

SELECT schema_of_xml('<p><a>1</a></p>');
-- STRUCT<a: BIGINT>

SELECT schema_of_xml('<p><a attr="2">1</a><a>3</a></p>', map('excludeAttribute', 'true'));
-- STRUCT<a: ARRAY<BIGINT>>

to_xml

Supported.

to_xml(expr[, options]): Returns a XML string with a given struct value

Since Spark 4.0.0.

SELECT to_xml(named_struct('a', 1, 'b', 2));
-- <ROW>
--     <a>1</a>
--     <b>2</b>
-- </ROW>

SELECT to_xml(named_struct('time', to_timestamp('2015-08-26', 'yyyy-MM-dd')), map('timestampFormat', 'dd/MM/yyyy'));
-- <ROW>
--     <time>26/08/2015</time>
-- </ROW>

xpath

Supported.

xpath(xml, xpath): Returns a string array of values within the nodes of xml that match the XPath expression.

Since Spark 2.0.0.

SELECT xpath('<a><b>b1</b><b>b2</b><b>b3</b><c>c1</c><c>c2</c></a>','a/b/text()');
-- ["b1","b2","b3"]

SELECT xpath('<a><b>b1</b><b>b2</b><b>b3</b><c>c1</c><c>c2</c></a>','a/b');
-- [null,null,null]

xpath_boolean

Supported.

xpath_boolean(xml, xpath): Returns true if the XPath expression evaluates to true, or if a matching node is found.

Since Spark 2.0.0.

SELECT xpath_boolean('<a><b>1</b></a>','a/b');
-- true

xpath_double

Supported.

xpath_double(xml, xpath): Returns a double value, the value zero if no match is found, or NaN if a match is found but the value is non-numeric.

Since Spark 2.0.0.

SELECT xpath_double('<a><b>1</b><b>2</b></a>', 'sum(a/b)');
-- 3.0

xpath_float

Supported.

xpath_float(xml, xpath): Returns a float value, the value zero if no match is found, or NaN if a match is found but the value is non-numeric.

Since Spark 2.0.0.

SELECT xpath_float('<a><b>1</b><b>2</b></a>', 'sum(a/b)');
-- 3.0

xpath_int

Supported.

xpath_int(xml, xpath): Returns an integer value, or the value zero if no match is found, or a match is found but the value is non-numeric.

Since Spark 2.0.0.

SELECT xpath_int('<a><b>1</b><b>2</b></a>', 'sum(a/b)');
-- 3

xpath_long

Supported.

xpath_long(xml, xpath): Returns a long integer value, or the value zero if no match is found, or a match is found but the value is non-numeric.

Since Spark 2.0.0.

SELECT xpath_long('<a><b>1</b><b>2</b></a>', 'sum(a/b)');
-- 3

xpath_number

Supported.

xpath_number(xml, xpath): Returns a double value, the value zero if no match is found, or NaN if a match is found but the value is non-numeric.

Since Spark 2.0.0.

SELECT xpath_number('<a><b>1</b><b>2</b></a>', 'sum(a/b)');
-- 3.0

xpath_short

Supported.

xpath_short(xml, xpath): Returns a short integer value, or the value zero if no match is found, or a match is found but the value is non-numeric.

Since Spark 2.0.0.

SELECT xpath_short('<a><b>1</b><b>2</b></a>', 'sum(a/b)');
-- 3

xpath_string

Supported.

xpath_string(xml, xpath): Returns the text contents of the first xml node that matches the XPath expression.

Since Spark 2.0.0.

SELECT xpath_string('<a><b>b</b><c>cc</c></a>','a/c');
-- cc

CSV

from_csv · schema_of_csv · to_csv

from_csv

Supported.

from_csv(csvStr, schema[, options]): Returns a struct value with the given csvStr and schema.

Since Spark 3.0.0.

SELECT from_csv('1, 0.8', 'a INT, b DOUBLE');
-- {"a":1,"b":0.8}

SELECT from_csv('26/08/2015', 'time Timestamp', map('timestampFormat', 'dd/MM/yyyy'));
-- {"time":2015-08-26 00:00:00}

schema_of_csv

Supported.

schema_of_csv(csv[, options]): Returns schema in the DDL format of CSV string.

Since Spark 3.0.0.

SELECT schema_of_csv('1,abc');
-- STRUCT<_c0: INT, _c1: STRING>

to_csv

Supported.

to_csv(expr[, options]): Returns a CSV string with a given struct value

Since Spark 3.0.0.

SELECT to_csv(named_struct('a', 1, 'b', 2));
-- 1,2

SELECT to_csv(named_struct('time', to_timestamp('2015-08-26', 'yyyy-MM-dd')), map('timestampFormat', 'dd/MM/yyyy'));
-- 26/08/2015

Variant

is_valid_variant · is_variant_null · parse_json · schema_of_variant · schema_of_variant_agg · to_variant_object · try_parse_json · try_variant_get · variant_explode · variant_explode_outer · variant_get

is_valid_variant

Supported. New in Spark 4.2.0.

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

SELECT is_valid_variant(parse_json('null'));
-- true

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

is_variant_null

Supported.

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

Since Spark 4.0.0.

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

parse_json

Supported, differs from Spark. Values match Spark when shown or converted with to_json; the Variant binary encoding differs (visible only when comparing raw Variant bytes).

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

Since Spark 4.0.0.

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

schema_of_variant

Supported.

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

Since Spark 4.0.0.

SELECT schema_of_variant(parse_json('null'));
-- VOID

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

schema_of_variant_agg

Supported.

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

Since Spark 4.0.0.

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

to_variant_object

Supported, differs from Spark. Values match Spark when shown or converted with to_json; the Variant binary encoding differs (visible only when comparing raw Variant bytes).

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.

Since Spark 4.0.0.

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}]

try_parse_json

Supported, differs from Spark. Values match Spark when shown or converted with to_json; the Variant binary encoding differs (visible only when comparing raw Variant bytes).

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

Since Spark 4.0.0.

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

SELECT try_parse_json('{"a":1,');
-- NULL

try_variant_get

Supported.

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.

Since Spark 4.0.0.

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

variant_explode

Supported, differs from Spark. Values match Spark when shown or converted with to_json; the Variant binary encoding differs (visible only when comparing raw Variant bytes).

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.

Since Spark 4.0.0.

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

variant_explode_outer

Supported, differs from Spark. Values match Spark when shown or converted with to_json; the Variant binary encoding differs (visible only when comparing raw Variant bytes).

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.

Since Spark 4.0.0.

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

variant_get

Supported.

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.

Since Spark 4.0.0.

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"

Avro

from_avro · schema_of_avro · to_avro

from_avro

Not supported. Avro functions are not implemented (Avro files can still be read).

from_avro(child, jsonFormatSchema, options): Converts a binary Avro value into a Catalyst value.

Note from Spark: The specified schema must match actual schema of the read data, otherwise the behavior is undefined: it may fail or return arbitrary result. To deserialize the data with a compatible and evolved schema, the expected Avro schema can be set via the corresponding option.

Since Spark 4.0.0.

schema_of_avro

Not supported. Avro functions are not implemented (Avro files can still be read).

schema_of_avro(jsonFormatSchema, options): Returns schema in the DDL format of the avro schema in JSON string format.

Since Spark 4.0.0.

to_avro

Not supported. Avro functions are not implemented (Avro files can still be read).

to_avro(child[, jsonFormatSchema]): Converts a Catalyst binary input value into its corresponding Avro format result.

Since Spark 4.0.0.

Protobuf

from_protobuf · to_protobuf

from_protobuf

Not supported. Protobuf functions are refused.

from_protobuf(data, messageName, descFilePath, options): Converts a binary Protobuf value into a Catalyst value.

Note from Spark: The specified Protobuf schema must match actual schema of the read data, otherwise the behavior is undefined: it may fail or return arbitrary result. To deserialize the data with a compatible and evolved schema, the expected Protobuf schema can be set via the corresponding option.

Since Spark 4.0.0.

to_protobuf

Not supported. Protobuf functions are refused.

to_protobuf(child, messageName, descFilePath, options): Converts a Catalyst binary input value into its corresponding Protobuf format result.

Since Spark 4.0.0.

Function names, usage text, argument descriptions and examples are from the Apache Spark SQL built-in functions documentation, Copyright The Apache Software Foundation, licensed under the Apache License, Version 2.0. Apache Spark is a trademark of the Apache Software Foundation. Sail status and notes are from Databasin.

Where to go next

Sail SQL referenceStatements, and every function page. Spark compatibilityWhere Sail differs from Apache Spark. Aggregate functionsMore functions. Window, generator and table functionsMore functions.