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