Json Functions

This page lists all json functions available in Spark SQL.

from_json

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

Arguments:

  • jsonStr - A JSON string to parse.
  • schema - The schema to use when parsing the JSON string, given as a DDL formatted string or a schema expression.
  • options - Optional. A map of string key-value pairs that control how the JSON is parsed. By default no options are set.

Examples:

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

Note:

A CHAR jsonStr is not rtrimmed; padding is parsed as trailing whitespace in the JSON text. CHAR/VARCHAR in schema is accepted when CHAR/VARCHAR are first-class (spark.sql.charVarchar.standardSemantics.enabled or spark.sql.preserveCharVarcharTypeInfo): fields are assigned (CHAR padded; overflow is a bad record). spark.sql.legacy.charVarcharAsString reads them as STRING. Otherwise the schema raises UNSUPPORTED_CHAR_OR_VARCHAR_AS_STRING. Map-key length checks require spark.sql.charVarchar.standardSemantics.enabled.

Since: 2.2.0


get_json_object

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

Arguments:

  • json_txt - The JSON text to extract from. An expression that evaluates to a string.
  • path - The path identifying the JSON object to extract. An expression that evaluates to a string.

Examples:

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

Since: 1.5.0


json_array

json_array([expr[, ...]]) - Returns a JSON array string with NULL elements dropped.

Arguments:

  • expr - the elements to place in the array.

Examples:

> SELECT json_array(1, 'x', true);
 [1,"x",true]
> SELECT json_array(1, NULL, 3);
 [1,3]
> SELECT json_array();
 []

Since: 4.4.0


json_array_length

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

Arguments:

  • jsonArray - A JSON array. NULL is returned in case of any other valid JSON string, NULL or an invalid JSON. An expression that evaluates to a string.

Examples:

> 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

Since: 3.1.0


json_exists

json_exists(jsonStr, path) - Returns true if the path selects at least one value.

Arguments:

  • jsonStr - a JSON string.
  • path - a SQL/JSON path expression given as a foldable string expression.

Examples:

> SELECT json_exists('{"a":1}', '$.a');
 true
> SELECT json_exists('{"a":1}', '$.b');
 false

Since: 4.4.0


json_object

json_object([key, value [, key, value ...]]) - Returns a JSON object string built from the given key/value pairs.

Arguments:

  • key - a non-null string naming an object member.
  • value - the member's value.

Examples:

> SELECT json_object('id', 7, 'name', 'Ada');
 {"id":7,"name":"Ada"}
> SELECT json_object();
 {}

Since: 4.4.0


json_object_keys

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

Arguments:

  • 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. An expression that evaluates to a string.

Examples:

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

Since: 3.1.0


json_query

json_query(jsonStr, path) - Extracts a JSON fragment as JSON text.

Arguments:

  • jsonStr - a JSON string.
  • path - a SQL/JSON path expression given as a foldable string expression. The path must be wildcard-free, since a single value is resolved.

Examples:

> SELECT json_query('{"a":[1,2]}', '$.a');
 [1,2]

Since: 4.4.0


json_tuple

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.

Arguments:

  • jsonStr - A JSON string to extract fields from.
  • pN - The field names to extract. Each name yields one output column with the corresponding field value.

Examples:

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

Since: 1.6.0


json_typeof

json_typeof(json) - Returns the type of the outermost JSON value, or null if invalid.

Arguments:

  • json - A JSON string. Returns the type of the outermost value ('object', 'array', 'string', 'number', 'boolean', 'null'), or null for an invalid or empty string. An expression that evaluates to a string.

Examples:

> SELECT json_typeof('{"a": 1}');
  object
> SELECT json_typeof('[1, 2, 3]');
  array
> SELECT json_typeof('123');
  number

Since: 4.4.0


json_value

json_value(jsonStr, path) - Extracts a SQL scalar as a string.

Arguments:

  • jsonStr - a JSON string.
  • path - a SQL/JSON path expression given as a foldable string expression. The path must be wildcard-free, since a single scalar is returned.

Examples:

> SELECT json_value('{"id":7,"name":"Ada"}', '$.name');
 Ada

Since: 4.4.0


schema_of_json

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

Arguments:

  • json - A JSON string whose schema is inferred.
  • options - Optional. A map of string key-value pairs that control how the JSON is parsed. By default no options are set.

Examples:

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

Note:

A CHAR json argument is not rtrimmed. Padding is trailing whitespace in the sample document; JSON inference ignores it.

Since: 2.4.0


to_json

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

Arguments:

  • expr - The struct value to convert to a JSON string. An expression that evaluates to a struct, array, map, or variant.
  • options - Options controlling how the JSON string is produced. An expression that evaluates to a map. Must be a constant.

Examples:

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

Since: 2.2.0