7.12. JSON Functions and Operators

    Note

    Casting from NULL to JSON is not straightforward. Castingfrom a standalone NULL will produce a SQL NULL instead ofJSON 'null'. However, when casting from arrays or map containingNULLs, the produced JSON will have nulls in it.

    Note

    When casting from ROW to JSON, the result is a JSON array ratherthan a JSON object. This is because positions are more important thannames for rows in SQL.

    Casting to BOOLEAN, TINYINT, SMALLINT, INTEGER, , 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:

    1. SELECT CAST(JSON 'null' AS VARCHAR); — NULL
    2. SELECT CAST(JSON '1' AS INTEGER); — 1
    3. SELECT CAST(JSON '9223372036854775807' AS BIGINT); — 9223372036854775807
    4. SELECT CAST(JSON '"abc"' AS VARCHAR); — abc
    5. SELECT CAST(JSON 'true' AS BOOLEAN); — true
    6. SELECT CAST(JSON '1.234' AS DOUBLE); — 1.234
    7. SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER)); — [1, 23, 456]
    8. SELECT CAST(JSON '[1,null,456]' AS ARRAY(INTEGER)); — [1, NULL, 456]
    9. SELECT CAST(JSON '[[1,23],[456]]' AS ARRAY(ARRAY(INTEGER))); — [[1, 23], [456]]
    10. SELECT CAST(JSON '{"k1":1,"k2":23,"k3":456}' AS MAP(VARCHAR, INTEGER)); — {k1=1, k2=23, k3=456}
    11. SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); — {v1=123, v2=abc, v3=true}
    12. SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); — {value1=123, value2=abc, value3=true}

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

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

    Note

    When casting from JSON to ROW, both JSON array and JSON object are supported.

    • is_json_scalar(json) → boolean
    • Determine if json is a scalar (i.e. a JSON number, a JSON string, true, false or null): 
      1. SELECT is_json_scalar('1'); — true
      2. SELECT is_json_scalar('[1, 2, 3]'); — false
    • json_array_contains(json, value) → boolean
    • Determine if value exists in json (a string containing a JSON array):
      • json_array_get(json_array, index) → json
      • Warning The semantics of this function are broken. If the extracted elementis a string, it will be converted into an invalid JSON value thatis not properly quoted (the value will not be surrounded by quotesand any interior quotes will not be escaped). We recommend against using this function. It cannot be fixed withoutimpacting existing usages and may be removed in a future release. Returns the element at the specified index into the json_array.The index is zero-based: 
        1. SELECT json_array_get('["a", [3, 9], "c"]', 0); — JSON 'a' (invalid JSON)
        2. SELECT json_array_get('["a", [3, 9], "c"]', 1); — JSON '[3,9]'
        This function also supports negative indexes for fetching element indexedfrom the end of an array: If the element at the specified index doesn’t exist, the function returns null: 
        1. SELECT json_array_get('[]', 0); — null
        2. SELECT json_array_get('["a", "b", "c"]', 10); — null
        3. 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): 
        1. 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: 
        1. SELECT json_extract(json, '$.store.book');
      • (json, json_path) → varchar
      • Like , but returns the result value as a string (as opposedto being encoded as JSON). The value referenced by json_path must be ascalar (boolean, number or string): 
        1. SELECT json_extract_scalar('[1, 2, 3]', '$[2]');
        2. SELECT json_extract_scalar(json, '$.store.book[0].author');
      • json_format(json) → varchar
      • Returns the JSON text serialized from the input JSON value.This is inverse function to json_parse(): 
        1. SELECT json_format(JSON '[1, 2, 3]'); — '[1,2,3]'
        2. SELECT json_format(JSON '"a"'); — '"a"'

      Note

      and CAST(json AS VARCHAR) have completelydifferent semantics.

      CAST(json AS VARCHAR) casts the JSON value to the corresponding SQL VARCHAR value.For JSON string, JSON number, true, false or null, the castbehavior is same as the corresponding SQL type. JSON object and JSON arraycannot be cast to VARCHAR:

      1. SELECT CAST(JSON '{"a": 1, "b": 2}' AS VARCHAR); -- ERROR!
      2. SELECT CAST(JSON '[1, 2, 3]' AS VARCHAR); -- ERROR!
      3. SELECT CAST(JSON '"abc"' AS VARCHAR); -- 'abc'; Note the double quote is gone
      4. SELECT CAST(JSON 'true' AS VARCHAR); -- 'true'
      5. SELECT CAST(JSON 'null' AS VARCHAR); -- NULL
      • json_parse(string) → json
      • Returns the JSON value deserialized from the input JSON text.This is inverse function to json_format(): 
        1. SELECT json_parse('[1, 2, 3]'); — JSON '[1,2,3]'
        2. SELECT json_parse('"abc"'); — JSON '"abc"'

      Note

      and CAST(string AS JSON) have completelydifferent semantics.

      json_parse() expects a JSON text conforming to , and returnsthe JSON value deserialized from the JSON text.The JSON value can be a JSON object, a JSON array, a JSON string, a JSON number,true, false or null:

      1. SELECT json_parse('not_json'); -- ERROR!
      2. SELECT json_parse('["a": 1, "b": 2]'); -- JSON '["a": 1, "b": 2]'
      3. SELECT json_parse('[1, 2, 3]'); -- JSON '[1,2,3]'
      4. SELECT json_parse('"abc"'); -- JSON '"abc"'
      5. SELECT json_parse('42'); -- JSON '42'
      6. SELECT json_parse('true'); -- JSON 'true'
      7. SELECT json_parse('null'); -- JSON 'null'

      CAST(string AS JSON) takes any VARCHAR value as input, and returnsa JSON string with its value set to input string:

      1. SELECT CAST('not_json' AS JSON); -- JSON '"not_json"'
      2. SELECT CAST('["a": 1, "b": 2]' AS JSON); -- JSON '"[\"a\": 1, \"b\": 2]"'
      3. SELECT CAST('[1, 2, 3]' AS JSON); -- JSON '"[1, 2, 3]"'
      4. SELECT CAST('"abc"' AS JSON); -- JSON '"\"abc\""'
      5. SELECT CAST('42' AS JSON); -- JSON '"42"'
      6. SELECT CAST('true' AS JSON); -- JSON '"true"'
      7. SELECT CAST('null' AS JSON); -- JSON '"null"'
      • json_size(json, json_path) → bigint
      • Like , 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: 
        1. SELECT json_size('{"x": {"a": 1, "b": 2}}', '$.x'); — 2
        2. SELECT json_size('{"x": [1, 2, 3]}', '$.x'); — 3