Help Center/ MapReduce Service/ Component Operation Guide (LTS)/ Using HetuEngine/ HetuEngine SQL Syntax/ SQL Functions and Operators/ JSON Functions and Operators
Updated on 2023-01-11 GMT+08:00
View PDF

JSON Functions and Operators

  • Cast to JSON 
    SELECT CAST(9223372036854775807 AS JSON); -- JSON '9223372036854775807'
  • Cast from JSON 
    SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER)); -- [1, 23, 456]

JSON Function

The value can be converted from BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, REAL, DOUBLE, or VARCHAR. If the element type of an array is one of the supported types, the key type of a map is VARCHAR, and the value type of a map is one of the supported types, or the type of each field in a row is one of the supported types, the array can be converted from ARRAY, MAP, or ROW. The following example shows the behavior of the transformation:

The conversion from NULL to JSON cannot be simply implemented. Converting from a separate NULL produces a SQLNULL instead of JSON'null'. However, when converted from an array or Map containing NULL, the generated JSON will contain NULL.

When converted from ROW to JSON, the result is a JSON array, not a JSON object. This is because for rows in SQL, the location is more important than the name.

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}'
SELECT CAST(CAST(ROW(123, 'abc', true) AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)) AS JSON);-- JSON '[123,"abc",true]'

JSON-to-other Types

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}  
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 arrays and JSON objects are supported during conversion from JSON to ROW.

JSON arrays can have mixed element types, and JSON Maps can have mixed value types. This makes it impossible to convert it to SQL arrays and maps in some cases. To solve this problem, HetuEngine supports partial conversion of arrays and maps.

SELECTCAST(JSON'[[1, 23], 456]'ASARRAY(JSON));-- [JSON '[1,23]', JSON '456']
SELECTCAST(JSON'{"k1": [1, 23], "k2": 456}'ASMAP(VARCHAR,JSON));-- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECTCAST(JSON'[null]'ASARRAY(JSON));-- [JSON 'null']
  • is_json_scalar(json) → boolean

    Check whether JSON is a scalar (that is, JSON number, JSON character string, true, false, or null).

    select is_json_scalar(json'[1,22]'); -- false
  • json_array_contains(json, value) → boolean

    Checks whether a value is contained in json.

    select json_array_contains(json '[1,23,44]',23); -- true
  • json_array_get(json_array, index) → json

    The semantics of the function has been broken. If the extracted element is a string, it will be converted to an invalid JSON value that is not correctly enclosed in quotation marks (the value will not be enclosed in quotation marks, and any internal quotation marks will not be escaped). You are advised not to use this function. The function cannot be corrected without affecting existing usage and may be deleted in future versions.

    Returns the JSON element at the specified index position. The index starts from 0.

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

    The index page supports negative numbers, indicating that the index page starts from the last element. The value -1 indicates the last element. If the index length exceeds the actual length, null is returned.

    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]'
  • json_array_length(json) → bigint

    Returns the length of json.

    select json_array_length(json '[1,2,3,4]'); -- 4
  • get_json_object(string json,string json_path);

    Captures information in json based on the json_path format.

    select get_json_object('{"id": 1, "value":"xxx"}', '$.value');  -- "xxx"
  • json_extract(json, json_path) → json

    Captures information in json based on the json_path format.

    select JSON_EXTRACT(json '{"id": 1, "value":"xxx"}', '$.value');-- JSON "xxx"
  • json_extract_scalar(json, json_path) → varchar

    The function is the same as that of json_extract. The return value is varchar.

    select JSON_EXTRACT_scalar(json '{"id": 1, "value": "xxx"}', '$.value'); -- xxx
  • json_format(json) → varchar

    Converts json to a serialized JSON text.

    select JSON_format(json '{"id": 1, "value":"xxx"}'); -- {"id":1, "value":"xxx"}

    Notes:

    json_format and CAST(json AS VARCHAR) have completely different semantics.

    json_format serializes the input JSON value into JSON text that complies with the 7159 standard. The JSON value can be a JSON object, JSON array, JSON string, JSON number, true, false, or null:

    SELECT json_format(JSON '{"a": 1, "b": 2}'); -- '{"a":1,"b":2}' 
SELECT json_format(JSON '[1, 2, 3]'); -- '[1,2,3]' 
SELECT json_format(JSON '"abc"'); -- '"abc"' 
SELECT json_format(JSON '42'); -- '42' 
SELECT json_format(JSON 'true'); -- 'true' 
SELECT json_format(JSON 'null'); -- 'null'

    CAST(json AS VARCHAR) converts the JSON value to the corresponding SQL VARCHAR value. For JSON strings, JSON numbers, true, false, or null, the conversion behavior is the same as that of the corresponding SQL type. JSON objects and JSON arrays cannot be converted to VARCHAR.

    SELECT CAST(JSON '{"a": 1, "b": 2}' AS VARCHAR); -- ERROR! 
SELECT CAST(JSON '[1, 2, 3]' AS VARCHAR); -- ERROR! 
SELECT CAST(JSON '"abc"' AS VARCHAR); -- 'abc'; Note the double quote is gone 
SELECT CAST(JSON '42' AS VARCHAR); -- '42' 
SELECT CAST(JSON 'true' AS VARCHAR); -- 'true' 
SELECT CAST(JSON 'null' AS VARCHAR); -- NULL
  • json_parse(string) → json

    Contrary to json_format(json), convert a JSON character string to JSON.

    json_parse and json_extract are used together to parse JSON character strings in data tables.

    select JSON_parse('{"id": 1, "value":"xxx"}'); -- json {"id":1, "value":"xxx"}
  • json_size(json, json_path) → bigint

    It is similar to json_extract, but the number of objects in JSON is returned.

    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