In MySQL, the SELECT JSON_STORAGE_SIZE('{"a": 1}') Result; 1 function returns the number of bytes used to store the binary representation of a JSON document.
You provide the JSON document as an argument.
When the argument is a SELECT JSON_STORAGE_SIZE('{"a": 1}') Result; 2 column, this function returns the space used to store the JSON document as it was inserted into the column, prior to any partial updates that may have been performed on it afterwards.
When the argument is a string, the function returns the amount of storage space in the JSON binary representation that is created by parsing the string as JSON and converting it to binary.
Syntax
The syntax goes like this:
JSON_STORAGE_SIZE(json_val)Where SELECT JSON_STORAGE_SIZE('{"a": 1}') Result; 3 is the JSON document for which to return the storage size. This must be a valid JSON document or a string which can be parsed as one.
Example
Left-pad the string with "ABC", to a total length of 20:
SELECT LPAD("SQL Tutorial", 20, "ABC");
Try it Yourself »Definition and Usage
The LPAD() function left-pads a string with another string, to a certain length.
Note: Also look at the RPAD() function.
Syntax
LPAD(string, length, lpad_string)
Parameter Values
ParameterDescriptionstringRequired. The original string. If the length of the original string is larger than the length parameter, this function removes the overfloating characters from stringlengthRequired. The length of the string after it has been left-paddedlpad_stringRequired. The string to left-pad to stringTechnical Details
More Examples
Example
Left-pad the text in "CustomerName" with "ABC", to a total length of 30:
SELECT LPAD(CustomerName, 30, "ABC") AS LeftPadCustomerName
FROM Customers;
Casting from 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} SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true} SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {value1=123, value2=abc, value3=true} 8, 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} SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true} SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {value1=123, value2=abc, value3=true} 9, 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'] 0, 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'] 1, 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'] 2, 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'] 3, 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'] 4 or 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'] 5 is supported. Casting from 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'] 6, 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'] 7 or 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'] 8 is supported when the element type of the array is one of the supported types, or when the key type of the map is 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'] 5 and value type of the map is one of the supported types, or when every field type of the row 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_FROM_ENTRIES(ARRAY[('k1', 1), ('k2', 23), ('k3', 456)]) AS JSON); -- JSON '{"k1":1,"k2":23,"k3":456}' SELECT CAST(CAST(ROW(123, 'abc', true) AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)) AS JSON); -- JSON '[123,"abc",true]'
Note
Casting from NULL to SELECT is_json_scalar('1'); -- true SELECT is_json_scalar('[1, 2, 3]'); -- false 0 is not straightforward. Casting from a standalone SELECT is_json_scalar('1'); -- true SELECT is_json_scalar('[1, 2, 3]'); -- false 1 will produce a SQL SELECT is_json_scalar('1'); -- true SELECT is_json_scalar('[1, 2, 3]'); -- false 1 instead of SELECT is_json_scalar('1'); -- true SELECT is_json_scalar('[1, 2, 3]'); -- false 3. However, when casting from arrays or map containing SELECT is_json_scalar('1'); -- true SELECT is_json_scalar('[1, 2, 3]'); -- false 1s, the produced SELECT is_json_scalar('1'); -- true SELECT is_json_scalar('[1, 2, 3]'); -- false 0 will have SELECT is_json_scalar('1'); -- true SELECT is_json_scalar('[1, 2, 3]'); -- false 6s in it.
Note
When casting from 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'] 8 to SELECT is_json_scalar('1'); -- true SELECT is_json_scalar('[1, 2, 3]'); -- false 0, the result is a JSON array rather than a JSON object. This is because positions are more important than names for rows in SQL.
Cast from JSON
Casting to 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} SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true} SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {value1=123, value2=abc, value3=true} 8, 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} SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true} SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {value1=123, value2=abc, value3=true} 9, 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'] 0, 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'] 1, 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'] 2, 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'] 3, 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'] 4 or 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'] 5 is supported. Casting to 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'] 6 and 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'] 7 is supported when the element type of the array is one of the supported types, or when the key type of the map is 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'] 5 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} SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true} SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {value1=123, value2=abc, value3=true}
Note
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']
Note
When casting from SELECT is_json_scalar('1'); -- true SELECT is_json_scalar('[1, 2, 3]'); -- false 0 to 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'] 8, both JSON array and JSON object are supported.
JSON Functions
is_json_scalar(json) → booleanDetermine if SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON) SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]' 2 is a scalar (i.e. a JSON number, a JSON string, SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON) SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]' 3, SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON) SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]' 4 or SELECT is_json_scalar('1'); -- true SELECT is_json_scalar('[1, 2, 3]'); -- false 6):
SELECT is_json_scalar('1'); -- true SELECT is_json_scalar('[1, 2, 3]'); -- false
json_array_contains(json, value) → booleanDetermine if SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON) SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]' 6 exists in SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON) SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]' 2 (a string containing a JSON array):
SELECT json_array_contains('[1, 2, 3]', 2);
json_array_get(json_array, index) → jsonWarning
The semantics of this function are broken. If the extracted element is a string, it will be converted into an invalid SELECT is_json_scalar('1'); -- true SELECT is_json_scalar('[1, 2, 3]'); -- false 0 value that is not properly quoted (the value will not be surrounded by quotes and any interior quotes will not be escaped).
We recommend against using this function. It cannot be fixed without impacting existing usages and may be removed in a future release.
Returns the element at the specified index into the SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON) SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]' 9. The index is zero-based:
SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON) SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]'
This function also supports negative indexes for fetching element indexed from the end of an array:
SELECT json_array_get('["c", [3, 9], "a"]', -1); -- JSON 'a' (invalid JSON) SELECT json_array_get('["c", [3, 9], "a"]', -2); -- JSON '[3,9]'
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) → bigintReturns the array length of SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON) SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]' 2 (a string containing a JSON array):
SELECT json_array_length('[1, 2, 3]');
json_extract(json, json_path) → jsonEvaluates the JSONPath-like expression SELECT json_array_get('["c", [3, 9], "a"]', -1); -- JSON 'a' (invalid JSON) SELECT json_array_get('["c", [3, 9], "a"]', -2); -- JSON '[3,9]' 1 on SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON) SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]' 2 (a string containing JSON) and returns the result as a JSON string:
SELECT json_extract(json, '$.store.book');
json_extract_scalar(json, json_path) → varcharLike , but returns the result value as a string (as opposed to being encoded as JSON). The value referenced by SELECT json_array_get('["c", [3, 9], "a"]', -1); -- JSON 'a' (invalid JSON) SELECT json_array_get('["c", [3, 9], "a"]', -2); -- JSON '[3,9]' 1 must be a scalar (boolean, number or string):
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} SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true} SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {value1=123, value2=abc, value3=true} 0
json_format(json) → varcharReturns the JSON text serialized from the input JSON value. This is inverse function to :
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} SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true} SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {value1=123, value2=abc, value3=true} 1
Note
and SELECT json_array_get('["c", [3, 9], "a"]', -1); -- JSON 'a' (invalid JSON) SELECT json_array_get('["c", [3, 9], "a"]', -2); -- JSON '[3,9]' 7 have completely different semantics.
serializes the input JSON value to JSON text conforming to RFC 7159. The JSON value can be a JSON object, a JSON array, a JSON string, a JSON number, SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON) SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]' 3, SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON) SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]' 4 or SELECT is_json_scalar('1'); -- true SELECT is_json_scalar('[1, 2, 3]'); -- false 6:
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} SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true} SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {value1=123, value2=abc, value3=true} 2
SELECT json_array_get('["c", [3, 9], "a"]', -1); -- JSON 'a' (invalid JSON) SELECT json_array_get('["c", [3, 9], "a"]', -2); -- JSON '[3,9]' 7 casts the JSON value to the corresponding SQL VARCHAR value. For JSON string, JSON number, SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON) SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]' 3, SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON) SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]' 4 or SELECT is_json_scalar('1'); -- true SELECT is_json_scalar('[1, 2, 3]'); -- false 6, the cast behavior is same as the corresponding SQL type. JSON object and JSON array cannot be cast to VARCHAR:
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} SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true} SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {value1=123, value2=abc, value3=true} 3
json_parse(string) → jsonReturns the JSON value deserialized from the input JSON text. This is inverse function to :
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} SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true} SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {value1=123, value2=abc, value3=true} 4
Note
and SELECT json_array_get('[]', 0); -- null SELECT json_array_get('["a", "b", "c"]', 10); -- null SELECT json_array_get('["c", "b", "a"]', -10); -- null 8 have completely different semantics.
expects a JSON text conforming to RFC 7159, and returns the JSON value deserialized from the JSON text. The JSON value can be a JSON object, a JSON array, a JSON string, a JSON number, SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON) SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]' 3, SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON) SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]' 4 or SELECT is_json_scalar('1'); -- true SELECT is_json_scalar('[1, 2, 3]'); -- false 6:
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} SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true} SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {value1=123, value2=abc, value3=true} 5
SELECT json_array_get('[]', 0); -- null SELECT json_array_get('["a", "b", "c"]', 10); -- null SELECT json_array_get('["c", "b", "a"]', -10); -- null 8 takes any VARCHAR value as input, and returns a JSON string with its value set to input string:
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} SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true} SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {value1=123, value2=abc, value3=true} 6
json_size(json, json_path) → bigintLike , 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: