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.

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.

Examples:

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

Since: 1.6.0

schema_of_json ¶

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

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