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

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


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

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