JSON Processing Functions

Search Shortcut cmd + k | ctrl + k

Documentation / Data Import / JSON Files

JSON 提取函数

有两个提取函数，它们有各自的操作符。只有当字符串存储为JSON逻辑类型时，才能使用这些操作符。这些函数支持与JSON标量函数相同的两种位置表示法。

函数	别名	操作符	描述
`json_exists(json, path)`			如果提供的路径存在于`json`中，则返回`true`，否则返回`false`。
`json_extract(json, path)`	`json_extract_path`	`->`	从给定的`path`中提取`JSON`。如果`path`是一个`LIST`，结果将是一个`JSON`的`LIST`。
`json_extract_string(json, path)`	`json_extract_path_text`	`->>`	从给定的`path`中提取`VARCHAR`。如果`path`是一个`LIST`，结果将是一个`VARCHAR`的`LIST`。
`json_value(json, path)`			从给定的`path`中提取`JSON`。如果提供的路径中的`json`不是标量值，它将返回`NULL`。

请注意，用于JSON提取的箭头操作符->具有较低的优先级，因为它也用于lambda函数中。

因此，在表达诸如相等比较（=）等操作时，您需要用括号将->运算符括起来。例如：

SELECT ((JSON '{"field": 42}')->'field') = 42;

警告 DuckDB的JSON数据类型使用基于0的索引。

示例：

CREATE TABLE example (j JSON);
INSERT INTO example VALUES
    ('{ "family": "anatidae", "species": [ "duck", "goose", "swan", null ] }');

SELECT json_extract(j, '$.family') FROM example;

"anatidae"

SELECT j->'$.family' FROM example;

"anatidae"

SELECT j->'$.species[0]' FROM example;

"duck"

SELECT j->'$.species[*]' FROM example;

["duck", "goose", "swan", null]

SELECT j->>'$.species[*]' FROM example;

[duck, goose, swan, null]

SELECT j->'$.species'->0 FROM example;

"duck"

SELECT j->'species'->['0','1'] FROM example;

["duck", "goose"]

SELECT json_extract_string(j, '$.family') FROM example;

anatidae

SELECT j->>'$.family' FROM example;

anatidae

SELECT j->>'$.species[0]' FROM example;

duck

SELECT j->'species'->>0 FROM example;

duck

SELECT j->'species'->>['0','1'] FROM example;

[duck, goose]

请注意，DuckDB的JSON数据类型使用基于0的索引。

如果需要从同一个JSON中提取多个值，提取路径列表会更高效：

以下将导致JSON被解析两次：

导致查询速度变慢并占用更多内存：

SELECT
    json_extract(j, 'family') AS family,
    json_extract(j, 'species') AS species
FROM example;

科	物种
"anatidae"	["duck","goose","swan",null]

以下代码产生相同的结果，但速度更快且内存效率更高：

WITH extracted AS (
    SELECT json_extract(j, ['family', 'species']) AS extracted_list
    FROM example
)
SELECT
    extracted_list[1] AS family,
    extracted_list[2] AS species
FROM extracted;

JSON 标量函数

以下标量JSON函数可用于获取有关存储的JSON值的信息。除了json_valid(json)之外，所有JSON函数在提供无效的JSON时都会产生错误。

我们支持两种符号来描述JSON中的位置：JSON Pointer 和 JSONPath。

Function	Description
`json_array_length(json[, path])`	返回 JSON 数组 `json` 中的元素数量，如果不是 JSON 数组则返回 `0`。如果指定了 `path`，则返回给定 `path` 处的 JSON 数组中的元素数量。如果 `path` 是一个 `LIST`，则结果将是数组长度的 `LIST`。
`json_contains(json_haystack, json_needle)`	如果 `json_needle` 包含在 `json_haystack` 中，则返回 `true`。两个参数都是 JSON 类型，但 `json_needle` 也可以是数值或字符串，不过字符串必须用双引号括起来。
`json_keys(json[, path])`	返回`json`的键作为`VARCHAR`的`LIST`，如果`json`是一个JSON对象。如果指定了`path`，则返回给定`path`处的JSON对象的键。如果`path`是一个`LIST`，结果将是`VARCHAR`的`LIST`的`LIST`。
`json_structure(json)`	返回 `json` 的结构。如果结构不一致（例如，数组中的类型不兼容），则默认为 `JSON`。
`json_type(json[, path])`	返回提供的`json`的类型，类型可能是`ARRAY`、`BIGINT`、`BOOLEAN`、`DOUBLE`、`OBJECT`、`UBIGINT`、`VARCHAR`和`NULL`。如果指定了`path`，则返回给定`path`处元素的类型。如果`path`是`LIST`，则结果将是类型的`LIST`。
`json_valid(json)`	返回 `json` 是否为有效的 JSON。
`json(json)`	解析并压缩 `json`。

JSONPointer语法使用/分隔每个字段。例如，要提取键为duck的数组的第一个元素，您可以这样做：

SELECT json_extract('{"duck": [1, 2, 3]}', '/duck/0');

JSONPath语法使用.分隔字段，使用[i]访问数组元素，并且总是以$开头。使用相同的示例，我们可以执行以下操作：

SELECT json_extract('{"duck": [1, 2, 3]}', '$.duck[0]');

Note that DuckDB's JSON data type uses 0-based indexing.

JSONPath 更具表现力，还可以从列表的末尾访问：

SELECT json_extract('{"duck": [1, 2, 3]}', '$.duck[#-1]');

JSONPath 还允许使用双引号来转义语法标记：

SELECT json_extract('{"duck.goose": [1, 2, 3]}', '$."duck.goose"[1]');

使用anatidae生物科的示例：

CREATE TABLE example (j JSON);
INSERT INTO example VALUES
    ('{ "family": "anatidae", "species": [ "duck", "goose", "swan", null ] }');

SELECT json(j) FROM example;

{"family":"anatidae","species":["duck","goose","swan",null]}

SELECT j.family FROM example;

"anatidae"

SELECT j.species[0] FROM example;

"duck"

SELECT json_valid(j) FROM example;

true

SELECT json_valid('{');

false

SELECT json_array_length('["duck", "goose", "swan", null]');

SELECT json_array_length(j, 'species') FROM example;

SELECT json_array_length(j, '/species') FROM example;

SELECT json_array_length(j, '$.species') FROM example;

SELECT json_array_length(j, ['$.species']) FROM example;

[4]

SELECT json_type(j) FROM example;

OBJECT

SELECT json_keys(j) FROM example;

[family, species]

SELECT json_structure(j) FROM example;

{"family":"VARCHAR","species":["VARCHAR"]}

SELECT json_structure('["duck", {"family": "anatidae"}]');

["JSON"]

SELECT json_contains('{"key": "value"}', '"value"');

true

SELECT json_contains('{"key": 1}', '1');

true

SELECT json_contains('{"top_key": {"key": "value"}}', '{"key": "value"}');

true

JSON 聚合函数

有三个JSON聚合函数。

Function	Description
`json_group_array(any)`	返回一个包含聚合中所有`any`值的JSON数组。
`json_group_object(key, value)`	返回一个包含所有`key`, `value`对的JSON对象。
`json_group_structure(json)`	返回聚合中所有`json`的合并`json_structure`。

示例：

CREATE TABLE example1 (k VARCHAR, v INTEGER);
INSERT INTO example1 VALUES ('duck', 42), ('goose', 7);

SELECT json_group_array(v) FROM example1;

[42, 7]

SELECT json_group_object(k, v) FROM example1;

{"duck":42,"goose":7}

CREATE TABLE example2 (j JSON);
INSERT INTO example2 VALUES
    ('{"family": "anatidae", "species": ["duck", "goose"], "coolness": 42.42}'),
    ('{"family": "canidae", "species": ["labrador", "bulldog"], "hair": true}');

SELECT json_group_structure(j) FROM example2;

{"family":"VARCHAR","species":["VARCHAR"],"coolness":"DOUBLE","hair":"BOOLEAN"}

将JSON转换为嵌套类型

在许多情况下，逐个从JSON中提取值效率低下。相反，我们可以一次性“提取”所有值，将JSON转换为嵌套类型LIST和STRUCT。

Function	Description
`json_transform(json, structure)`	根据指定的`structure`转换`json`。
`from_json(json, structure)`	`json_transform` 的别名。
`json_transform_strict(json, structure)`	与 `json_transform` 相同，但在类型转换失败时抛出错误。
`from_json_strict(json, structure)`	`json_transform_strict` 的别名。

structure 参数是与 json_structure 返回的相同形式的 JSON。可以修改 structure 参数以将 JSON 转换为所需的结构和类型。可以从 JSON 中提取比现有键/值对更少的键/值对，也可以提取更多：缺失的键将变为 NULL。

示例：

CREATE TABLE example (j JSON);
INSERT INTO example VALUES
    ('{"family": "anatidae", "species": ["duck", "goose"], "coolness": 42.42}'),
    ('{"family": "canidae", "species": ["labrador", "bulldog"], "hair": true}');

SELECT json_transform(j, '{"family": "VARCHAR", "coolness": "DOUBLE"}') FROM example;

{'family': anatidae, 'coolness': 42.420000}
{'family': canidae, 'coolness': NULL}

SELECT json_transform(j, '{"family": "TINYINT", "coolness": "DECIMAL(4, 2)"}') FROM example;

{'family': NULL, 'coolness': 42.42}
{'family': NULL, 'coolness': NULL}

SELECT json_transform_strict(j, '{"family": "TINYINT", "coolness": "DOUBLE"}') FROM example;

Invalid Input Error: Failed to cast value: "anatidae"

JSON 提取函数

JSON 标量函数

JSON 聚合函数

将JSON转换为嵌套类型

关于此页面

本文内容