JSON 提取函数
有两种提取函数，它们各自有对应的运算符。这些运算符仅当字符串存储为 JSON 逻辑类型时才能使用。这些函数支持与 JSON 标量函数 相同的两种位置表示法。
| 函数 | 别名 | 运算符 | 描述 |
|---|---|---|---|
json_exists(json, path) |
如果提供的 path 存在于 json 中，则返回 true，否则返回 false。 |
||
json_extract(json, path) |
json_extract_path |
-> |
从给定 path 的 json 中提取 JSON。如果 path 是一个 LIST，结果将是一个 JSON 的 LIST。 |
json_extract_string(json, path) |
json_extract_path_text |
->> |
从给定 path 的 json 中提取 VARCHAR。如果 path 是一个 LIST，结果将是一个 VARCHAR 的 LIST。 |
json_value(json, path) |
从给定 path 的 json 中提取 JSON。如果所提供路径上的 json 不是标量值，它将返回 NULL。 |
请注意，用于 JSON 提取的箭头运算符 -> 优先级较低，因为它也用于 lambda 函数。因此，在表示相等比较 (=) 等操作时，您需要用括号将 -> 运算符括起来。例如
SELECT ((JSON '{"field": 42}')->'field') = 42;
警告 DuckDB 的 JSON 数据类型使用 0-based indexing（从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-based indexing（从0开始的索引）。
如果需要从同一个 JSON 中提取多个值，更有效的方法是提取一个路径列表
以下操作将导致 JSON 被解析两次，
导致查询速度变慢且使用更多内存
SELECT
json_extract(j, 'family') AS family,
json_extract(j, 'species') AS species
FROM example;
| family | species |
|---|---|
| "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。
| 函数 | 描述 |
|---|---|
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 是一个 JSON 对象，则将其键作为 VARCHAR 的 LIST 返回。如果指定了 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');
1
JSONPath 语法使用 . 分隔字段，并使用 [i] 访问数组元素，并且总是以 $ 开头。使用相同的例子，我们可以这样做
SELECT json_extract('{"duck": [1, 2, 3]}', '$.duck[0]');
1
请注意，DuckDB 的 JSON 数据类型使用 0-based indexing（从0开始的索引）。
JSONPath 更具表达力，也可以从列表末尾访问
SELECT json_extract('{"duck": [1, 2, 3]}', '$.duck[#-1]');
3
JSONPath 还允许使用双引号转义语法标记
SELECT json_extract('{"duck.goose": [1, 2, 3]}', '$."duck.goose"[1]');
2
使用 鸭科生物家族 的示例
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]');
4
SELECT json_array_length(j, 'species') FROM example;
4
SELECT json_array_length(j, '/species') FROM example;
4
SELECT json_array_length(j, '$.species') FROM example;
4
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 聚合函数。
| 函数 | 描述 |
|---|---|
json_group_array(any) |
返回一个 JSON 数组，其中包含聚合中 any 的所有值。 |
json_group_object(key, value) |
返回一个 JSON 对象，其中包含聚合中所有 key、value 对。 |
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。
| 函数 | 描述 |
|---|---|
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 表函数
DuckDB 实现了两个 JSON 表函数，它们接受一个 JSON 值并从中生成一个表。
| 函数 | 描述 |
|---|---|
json_each(json[ ,path] |
遍历 json 并为顶级数组或对象中的每个元素返回一行。 |
json_tree(json[ ,path] |
以深度优先的方式遍历 json，并为结构中的每个元素返回一行。 |
如果元素不是数组或对象，则返回元素本身。如果提供了可选的 path 参数，则遍历将从给定路径处的元素开始，而不是根元素。
结果表包含以下列
| 字段 | 类型 | 描述 |
|---|---|---|
key |
VARCHAR |
元素相对于其父级的键 |
value |
JSON |
元素的值 |
type |
VARCHAR |
此元素的 json_type（函数） |
atom |
JSON |
此元素的 json_value（函数） |
id |
UBIGINT |
元素标识符，按解析顺序编号 |
parent |
UBIGINT |
父元素的 id |
fullkey |
VARCHAR |
元素的 JSON 路径 |
path |
VARCHAR |
父元素的 JSON 路径 |
json |
JSON（虚拟） |
json 参数 |
root |
TEXT（虚拟） |
path 参数 |
rowid |
BIGINT（虚拟） |
行标识符 |
这些函数类似于 SQLite 中同名函数。请注意，因为 json_each 和 json_tree 函数引用了同一 FROM 子句中的先前子查询，所以它们是横向连接（lateral joins）。
示例
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 je.*, je.rowid
FROM example AS e, json_each(e.j) AS je;
| key | value | type | atom | id | parent | fullkey | path | rowid |
|---|---|---|---|---|---|---|---|---|
| family | "anatidae" | VARCHAR | "anatidae" | 2 | NULL | $.family | $ | 0 |
| species | ["duck","goose"] | ARRAY | NULL | 4 | NULL | $.species | $ | 1 |
| coolness | 42.42 | DOUBLE | 42.42 | 8 | NULL | $.coolness | $ | 2 |
| family | "canidae" | VARCHAR | "canidae" | 2 | NULL | $.family | $ | 0 |
| species | ["labrador","bulldog"] | ARRAY | NULL | 4 | NULL | $.species | $ | 1 |
| hair | true | BOOLEAN | true | 8 | NULL | $.hair | $ | 2 |
SELECT je.*, je.rowid
FROM example AS e, json_each(e.j, '$.species') AS je;
| key | value | type | atom | id | parent | fullkey | path | rowid |
|---|---|---|---|---|---|---|---|---|
| 0 | "duck" | VARCHAR | "duck" | 5 | NULL | $.species[0] | $.species | 0 |
| 1 | "goose" | VARCHAR | "goose" | 6 | NULL | $.species[1] | $.species | 1 |
| 0 | "labrador" | VARCHAR | "labrador" | 5 | NULL | $.species[0] | $.species | 0 |
| 1 | "bulldog" | VARCHAR | "bulldog" | 6 | NULL | $.species[1] | $.species | 1 |
SELECT je.key, je.value, je.type, je.id, je.parent, je.fullkey, je.rowid
FROM example AS e, json_tree(e.j) AS je;
| key | value | type | id | parent | fullkey | rowid |
|---|---|---|---|---|---|---|
| NULL | {"family":"anatidae","species":["duck","goose"],"coolness":42.42} | OBJECT | 0 | NULL | $ | 0 |
| family | "anatidae" | VARCHAR | 2 | 0 | $.family | 1 |
| species | ["duck","goose"] | ARRAY | 4 | 0 | $.species | 2 |
| 0 | "duck" | VARCHAR | 5 | 4 | $.species[0] | 3 |
| 1 | "goose" | VARCHAR | 6 | 4 | $.species[1] | 4 |
| coolness | 42.42 | DOUBLE | 8 | 0 | $.coolness | 5 |
| NULL | {"family":"canidae","species":["labrador","bulldog"],"hair":true} | OBJECT | 0 | NULL | $ | 0 |
| family | "canidae" | VARCHAR | 2 | 0 | $.family | 1 |
| species | ["labrador","bulldog"] | ARRAY | 4 | 0 | $.species | 2 |
| 0 | "labrador" | VARCHAR | 5 | 4 | $.species[0] | 3 |
| 1 | "bulldog" | VARCHAR | 6 | 4 | $.species[1] | 4 |
| hair | true | BOOLEAN | 8 | 0 | $.hair | 5 |