JSON 函數與運算子¶

轉換為 JSON¶

支援從 BOOLEAN、TINYINT、SMALLINT、INTEGER、BIGINT、REAL、DOUBLE 或 VARCHAR 轉換。當陣列的元素類型為其中一種支援的類型時，或當映射的鍵類型為 VARCHAR 且映射的值類型為其中一種支援的類型時，或當列的每個欄位類型為其中一種支援的類型時，支援從 ARRAY、MAP 或 ROW 轉換。轉換的行為如下面的範例所示
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]'

注意

從 NULL 轉換為 JSON 並不直接。從獨立的 NULL 轉換將產生 SQL NULL 而不是 JSON 'null'。但是，當從包含 NULL 的陣列或映射轉換時，產生的 JSON 將包含 null。

注意

當從 ROW 轉換為 JSON 時，結果是 JSON 陣列而不是 JSON 物件。這是因為位置在 SQL 中對於列來說比名稱更重要。

從 JSON 轉換¶

支援轉換為 BOOLEAN、TINYINT、SMALLINT、INTEGER、BIGINT、REAL、DOUBLE 或 VARCHAR。當陣列的元素類型為其中一種支援的類型時，或當映射的鍵類型為 VARCHAR 且映射的值類型為其中一種支援的類型時，支援轉換為 ARRAY 和 MAP。轉換的行為如下面的範例所示

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}

注意

JSON 陣列可以有混合的元素類型，而 JSON 映射可以有混合的值類型。這使得在某些情況下無法將它們轉換為 SQL 陣列和映射。為了處理這個問題，Presto 支援陣列和映射的部分轉換

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 轉換為 ROW 時，為了支援舊版，在比對時會忽略 RowType 中雙引號括住的欄位名稱的情況。例如

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 '{"v1":123,"V2":"abc","v3":true}' AS ROW(v1 BIGINT, "V2" VARCHAR, "V3" BOOLEAN)); -- {v1=123, V2=abc, V3=true}

由於欄位重複，以下陳述式會傳回錯誤

SELECT CAST(JSON '{"v1":123,"V2":"abc","v2":"abc2","v3":true}' AS ROW(v1 BIGINT, "V2" VARCHAR, v2 VARCHAR, "V3" BOOLEAN));

若要強制執行從 JSON 轉換為 ROW 時 RowType 中欄位名稱的大小寫，請在協調器和工作人員的組態屬性中將組態屬性 legacy_json_cast 設定為 false。設定屬性後，雙引號括住的欄位名稱的比對會區分大小寫，而未加引號的欄位名稱的比對仍不區分大小寫。例如

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 '{"v1":123,"V2":"abc","v3":true}' AS ROW(v1 BIGINT, "V2" VARCHAR, v3 BOOLEAN)); -- {v1=123, V2=abc, v3=true}
SELECT CAST(JSON '{"v1":123,"V2":"abc","v3":true}' AS ROW(v1 BIGINT, "v2" VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=null, v3=true}
SELECT CAST(JSON '{"v1":123,"V2":"abc", "v2":"abc2","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, "V2" VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc2, V2=abc, v3=true}

如果欄位的名稱不符 (包括大小寫敏感度)，則值為 null。

注意

當從 JSON 轉換為 ROW 時，支援 JSON 陣列和 JSON 物件。

JSON 函數¶

is_json_scalar(json) -> boolean()¶

判斷 json 是否為純量 (即 JSON 數字、JSON 字串、true、false 或 null)

SELECT is_json_scalar('1'); -- true
SELECT is_json_scalar('[1, 2, 3]'); -- false

json_array_contains(json, value) -> boolean()¶

判斷 value 是否存在於 json 中 (包含 JSON 陣列的字串)

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

json_array_get(json_array, index) -> json()¶

警告

此函數的語意已損壞。如果擷取的元素是字串，它將會轉換為無效的 JSON 值，該值未正確加上引號 (該值不會以引號括住，而且任何內部的引號都不會跳脫)。

我們建議不要使用此函數。它無法在不影響現有使用方式的情況下修復，並可能會在未來的版本中移除。

傳回指定索引處的 json_array 中的元素。索引是以零為基礎

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

此函數也支援負索引，以從陣列的結尾擷取索引元素

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

如果指定索引處的元素不存在，則函數會傳回 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()¶

傳回 json 的陣列長度 (包含 JSON 陣列的字串)

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

json_extract(json, json_path) -> json()¶

在 json (包含 JSON 的字串) 上評估類似 JSONPath 的表達式 json_path，並將結果傳回為 JSON 字串

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

json_extract_scalar(json, json_path) -> varchar()¶

與 json_extract() 類似，但傳回結果值為字串 (而不是編碼為 JSON)。json_path 所參考的值必須是純量 (布林值、數字或字串)

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

json_format(json) -> varchar()¶

傳回從輸入 JSON 值序列化而成的 JSON 文字。此為 json_parse() 的反向函式。

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

注意

json_format() 和 CAST(json AS VARCHAR) 具有完全不同的語意。

json_format() 將輸入 JSON 值序列化為符合 RFC 7159 的 JSON 文字。JSON 值可以是 JSON 物件、JSON 陣列、JSON 字串、JSON 數字、true、false 或 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) 將 JSON 值轉換為對應的 SQL VARCHAR 值。對於 JSON 字串、JSON 數字、true、false 或 null，轉換行為與對應的 SQL 類型相同。JSON 物件和 JSON 陣列無法轉換為 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()¶

傳回從輸入 JSON 文字反序列化而成的 JSON 值。此為 json_format() 的反向函式。

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

注意

json_parse() 和 CAST(string AS JSON) 具有完全不同的語意。

json_parse() 預期一個符合 RFC 7159 的 JSON 文字，並傳回從 JSON 文字反序列化而成的 JSON 值。JSON 值可以是 JSON 物件、JSON 陣列、JSON 字串、JSON 數字、true、false 或 null。

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

CAST(string AS JSON) 接受任何 VARCHAR 值作為輸入，並傳回一個 JSON 字串，其值設定為輸入字串。

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

json_size(json, json_path) -> bigint()¶

與 json_extract() 類似，但傳回值的大小。對於物件或陣列，大小是成員的數量，而純量值的大小為零。

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