DuckDB 支持用于从现有 JSON 读取值和创建新 JSON 数据的 SQL 函数。JSON 通过 json 扩展支持，该扩展随大多数 DuckDB 分发版一起提供，并在首次使用时自动加载。如果您想手动安装或加载它，请参阅“安装和加载”页面。

关于 JSON

JSON 是一种开放标准的文件格式和数据交换格式，它使用人类可读的文本来存储和传输由属性-值对和数组（或其他可序列化值）组成的数据对象。虽然它对于表格数据来说不是一种非常高效的格式，但它被广泛使用，尤其作为数据交换格式。

JSONPath 和 JSON Pointer 语法

DuckDB 为 JSON 提取实现了多种接口：JSONPath 和 JSON Pointer。它们都支持箭头运算符 (->) 和 json_extract 函数调用。

请注意，DuckDB 仅支持 JSONPath 中的查找，即使用 .<key> 提取字段或使用 [<index>] 提取数组元素。数组可以从后向前索引，并且这两种方法都支持通配符 *。DuckDB 不支持完整的 JSONPath 语法，因为 SQL 可用于任何进一步的转换。

最好选择 JSONPath 或 JSON Pointer 语法，并在整个应用程序中使用它。

索引

警告：遵循 PostgreSQL 的约定，DuckDB 对其 ARRAY 和 LIST 数据类型使用 1-基于索引，但对 JSON 数据类型使用 0-基于索引。

示例

加载 JSON

从磁盘读取 JSON 文件，自动推断选项

SELECT * FROM 'todos.json';

使用自定义选项的 read_json 函数

SELECT *
FROM read_json('todos.json',
               format = 'array',
               columns = {userId: 'UBIGINT',
                          id: 'UBIGINT',
                          title: 'VARCHAR',
                          completed: 'BOOLEAN'});

从 stdin 读取 JSON 文件，自动推断选项

cat data/json/todos.json | duckdb -c "SELECT * FROM read_json('/dev/stdin')"

将 JSON 文件读入表中

CREATE TABLE todos (userId UBIGINT, id UBIGINT, title VARCHAR, completed BOOLEAN);
COPY todos FROM 'todos.json' (AUTO_DETECT true);

另外，可以使用 CREATE TABLE ... AS SELECT 子句创建表，而无需手动指定模式

CREATE TABLE todos AS
    SELECT * FROM 'todos.json';

自 DuckDB v1.3.0 起，JSON 读取器返回 filename 虚拟列

SELECT filename, *
FROM 'todos-*.json';

写入 JSON

将查询结果写入 JSON 文件

COPY (SELECT * FROM todos) TO 'todos.json';

JSON 数据类型

创建一个带列以存储 JSON 数据并向其中插入数据的表

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

检索 JSON 数据

检索 family 键的值

SELECT j.family FROM example;

"anatidae"

使用 JSONPath 表达式将 family 键的值提取为 JSON

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

"anatidae"

使用 JSONPath 表达式将 family 键的值提取为 VARCHAR

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

anatidae

对特殊字符使用引号

包含特殊字符 [ 和 . 的 JSON 对象键可以通过用双引号 (") 将它们括起来使用

SELECT '{"d[u]._\"ck":42}'->'$."d[u]._\"ck"' AS v;

42