15.12. JSON Functions
-
json_array_contains
(json, value) → boolean Determine if
value
exists injson
(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
onjson
(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 byjson_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