JSON 概览

Goose 提供了丰富的 SQL 函数，可用于从现有 JSON 中读取值并创建新的 JSON 数据。 JSON 功能由 json 扩展提供，该扩展在大多数 Goose 发行版中默认内置，并会在首次使用时自动加载。 如需手动安装或加载，请参阅“安装与加载”页面。

关于 JSON

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

JSONPath 与 JSON Pointer 语法

Goose 实现了多种 JSON 提取接口：JSONPath 与 JSON Pointer。两者都可与箭头操作符（->）及 json_extract 函数配合使用。

注意，Goose 在 JSONPath 中仅支持“查找型”路径：使用 .<key> 提取字段，或用 [<index>] 提取数组元素。 数组支持从尾部索引，两种语法都支持通配符 *。 Goose 不支持完整 JSONPath 语法，因为后续复杂变换可直接借助 SQL 完成。

建议在应用中统一选择 JSONPath 或 JSON Pointer 其中一种语法并保持一致。

索引

警告：遵循 PostgreSQL 约定，Goose 的 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 | goose -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 子句 创建表，无需手动指定 schema：

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

从 Goose 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