JSON 提取函数
存在两个提取函数，它们各自拥有对应的运算符。仅当字符串存储为 JSON 逻辑类型时，才能使用这些运算符。这些函数支持与 JSON 标量函数 相同的两种位置表示法。
| 函数 | 别名 | 运算符 | 描述 |
|---|---|---|---|
json_exists(json, path) |
如果提供的路径存在于 json 中，则返回 true，否则返回 false。 |
||
json_extract(json, path) |
json_extract_path |
-> |
从 json 的给定 path 处提取 JSON。如果 path 是一个 LIST，则结果将是一个 JSON 的 LIST。 |
json_extract_string(json, path) |
json_extract_path_text |
->> |
从 json 的给定 path 处提取 VARCHAR。如果 path 是一个 LIST，则结果将是一个 VARCHAR 的 LIST。 |
json_value(json, path) |
从 json 的给定 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;
| 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_haystack 中包含 json_needle，则返回 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 开始的索引。
JSONPath 表达能力更强，还可以从列表末尾进行访问：
SELECT json_extract('{"duck": [1, 2, 3]}', '$.duck[#-1]');
3
JSONPath 还允许使用双引号对语法标记进行转义：
SELECT json_extract('{"duck.goose": [1, 2, 3]}', '$."duck.goose"[1]');
2
使用 鸭科 (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]');
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) |
返回包含聚合中所有 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 等嵌套类型。
| 函数 | 描述 |
|---|---|
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 |