15.12. JSON Functions and Operators

Cast to JSON

Casting from BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, REAL, DOUBLE or VARCHAR is supported. Casting from ARRAY and MAP is supported when the element type of the array is one of the supported types, or when the key type of the map is VARCHAR and value type of the map is one of the supported types. Behaviors of the casts are shown with the examples below:

SELECT CAST(NULL AS JSON); -- NULL
SELECT CAST(1 AS JSON); -- JSON '1'
SELECT CAST(9223372036854775807 AS JSON); -- JSON '9223372036854775807'
SELECT CAST('abc' AS JSON); -- JSON '"abc"'
SELECT CAST(true AS JSON); -- JSON 'true'
SELECT CAST(1.234 AS JSON); -- JSON '1.234'
SELECT CAST(ARRAY[1, 23, 456] AS JSON); -- JSON '[1,23,456]'
SELECT CAST(ARRAY[1, NULL, 456] AS JSON); -- JSON '[1,null,456]'
SELECT CAST(ARRAY[ARRAY[1, 23], ARRAY[456]] AS JSON); -- JSON '[[1,23],[456]]'
SELECT CAST(MAP(ARRAY['k1', 'k2', 'k3'], ARRAY[1, 23, 456]) AS JSON); -- JSON '{"k1":1,"k2":23,"k3":456}'

Note that casting from NULL to JSON is not straightforward. Casting from a standalone NULL will produce a SQL NULL instead of JSON 'null'. However, when casting from arrays or map containing NULLs, the produced JSON will have nulls in it.

Cast from JSON

Casting to BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, REAL, DOUBLE or VARCHAR is supported. Casting to ARRAY and MAP is supported when the element type of the array is one of the supported types, or when the key type of the map is VARCHAR and value type of the map is one of the supported types. Behaviors of the casts are shown with the examples below:

SELECT CAST(JSON 'null' AS VARCHAR); -- NULL
SELECT CAST(JSON '1' AS INTEGER); -- 1
SELECT CAST(JSON '9223372036854775807' AS BIGINT); -- 9223372036854775807
SELECT CAST(JSON '"abc"' AS VARCHAR); -- abc
SELECT CAST(JSON 'true' AS BOOLEAN); -- true
SELECT CAST(JSON '1.234' AS DOUBLE); -- 1.234
SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER)); -- [1, 23, 456]
SELECT CAST(JSON '[1,null,456]' AS ARRAY(INTEGER)); -- [1, NULL, 456]
SELECT CAST(JSON '[[1,23],[456]]' AS ARRAY(ARRAY(INTEGER))); -- [[1, 23], [456]]
SELECT CAST(JSON '{"k1":1,"k2":23,"k3":456}' AS MAP(VARCHAR, INTEGER)); -- {k1=1, k2=23, k3=456}

JSON arrays can have mixed element types and JSON maps can have mixed value types. This makes it impossible to cast them to SQL arrays and maps in some cases. To address this, Presto supports partial casting of arrays and maps:

SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON)); -- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON)); -- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON)); -- [JSON 'null']

JSON Functions

json_array_contains(json, value) → boolean

Determine if value exists in json (a string containing a JSON array):

SELECT json_array_contains('[1, 2, 3]', 2);

json_array_get(json_array, index) → varchar

Returns the element at the specified index into the json_array. The index is zero-based:

SELECT json_array_get('["a", "b", "c"]', 0); -- 'a'
SELECT json_array_get('["a", "b", "c"]', 1); -- 'b'

This function also supports negative indexes for fetching element indexed from the end of an array:

SELECT json_array_get('["c", "b", "a"]', -1); -- 'a'
SELECT json_array_get('["c", "b", "a"]', -2); -- 'b'

If the element at the specified index doesn’t exist, the function returns null:

SELECT json_array_get('[]', 0); -- null
SELECT json_array_get('["a", "b", "c"]', 10); -- null
SELECT json_array_get('["c", "b", "a"]', -10); -- null

json_array_length(json) → bigint

Returns the array length of json (a string containing a JSON array):

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

json_extract(json, json_path) → json

Evaluates the JSONPath-like expression json_path on json (a string containing JSON) and returns the result as a JSON string:

SELECT json_extract(json, '$.store.book');

json_extract_scalar(json, json_path) → varchar

Like json_extract(), but returns the result value as a string (as opposed to being encoded as JSON). The value referenced by json_path must be a scalar (boolean, number or string):

SELECT json_extract_scalar('[1, 2, 3]', '$[2]');
SELECT json_extract_scalar(json, '$.store.book[0].author');

json_format(json) → varchar

Returns json as a string:

SELECT json_format(JSON '[1, 2, 3]'); -- '[1,2,3]'
SELECT json_format(JSON '"a"'); -- '"a"'

json_parse(string) → json

Parse string as a json:

SELECT json_parse('[1, 2, 3]'); -- JSON '[1,2,3]'
SELECT json_parse('"a"'); -- JSON '"a"'

json_size(json, json_path) → bigint

Like json_extract(), but returns the size of the value. For objects or arrays, the size is the number of members, and the size of a scalar value is zero:

SELECT json_size('{"x": {"a": 1, "b": 2}}', '$.x'); -- 2
SELECT json_size('{"x": [1, 2, 3]}', '$.x'); -- 3
SELECT json_size('{"x": {"a": 1, "b": 2}}', '$.x.a'); -- 0