《PostgreSQL 开发指南》第 31 篇 JSON 文档存储
JSON（JavaScript Object Notation、JavaScript 对象表示法）是一种轻量级的数据交换格式，采用完全独立于编程语言的文本格式来存储和表示数据。JSON 易于阅读和编写，同时也方便机器解析和生成，并且能够有效地提升网络传输效率。在网络数据传输领域，JSON 已成为了 XML 强有力的替代者。
如果想要学习和了解 JSON，推荐 JSON 官方网站 。
JSON 数据类型
PostgreSQL 提供了两种 JSON 数据类型：JSON 以及 JSONB。这两种类型主要的区别在于数据存储格式，JSONB 使用二进制格式存储数据，更易于处理。
PostgreSQL 推荐优先选择 JSONB 数据类型。
下表描述了两种数据类型之间的区别：
| 功能 | JSON | JSONB |
|---|---|---|
| 存储格式 | 字符串原文存储 | 解析后的二进制 |
| 全文索引 | 不支持 | 支持 |
| 保留空白符 | 保留 | 不保留 |
| 保留键的顺序 | 保留 | 不保留 |
| 保留重复键 | 保留 | 不保留 |
由于存储格式的不同，JSONB 输入时稍微慢一些（需要转换），但是查询时快很多。
接下来的内容主要使用 JSONB 数据类型，但是大部分功能也可以使用 JSON 数据类型。
定义 JSON 字段
首先创建一个产品表 product：
CREATE TABLE product (
id INTEGER NOT NULL PRIMARY KEY,
product_name VARCHAR(100),
attributes JSONB
);产品表 product 中包含一个 JSONB 类型的字段 attributes，用于存储产品的属性。
JSON 字段赋值
我们可以直接使用字符串为 JSON 字段赋值，但是要求数据必须是有效的 JSON 格式，否则将会返回错误。
执行以下语句插入一条产品记录：
INSERT INTO product (id, product_name, attributes)
VALUES (1, '椅子','{"color":"棕色", "material":"实木", "height":"60cm"}');接下来我们插入一条不符合 JSON 格式的数据：
INSERT INTO product (id, product_name, attributes)
VALUES (2, '沙发椅', '"color":"白色:50cm}');
SQL 错误 [22P02]: ERROR: invalid input syntax for type json
详细：Expected end of input, but found ":".
位置：71
在位置：JSON data, line 1: "color":...
Error position: line: 2 pos: 70以下语句插入了一条包含 JSON 数组的产品信息：
INSERT INTO product (id, product_name, attributes)
VALUES (
'桌子',
'{"color":"黑色", "material":"金属", "drawers":[{"side":"左侧", "height":"30cm"}, {"side":"右侧", "height":"40cm"}]}'
);我们使用同样的方法再创建一条记录：
INSERT INTO product (id, product_name, attributes)
VALUES (
'茶几',
'{"color":"棕色", "material":["金属", "实木"]}'
);以上方法虽然使用简单，但是输入比较麻烦。为此，PostgreSQL 提供了一些方便生产 JSON 数据的函数。
jsonb_build_object 函数可以通过一系列输入创建二进制的 JSON 对象，例如：
SELECT jsonb_build_object('color', '黑色', 'material', '塑料');
jsonb_build_object |
---------------------------------+
{"color": "黑色", "material": "塑料"}|我们可以利用该函数插入数据，而不需要手动输入方括号、逗号、冒号等 JSON 符号。例如：
INSERT INTO product (id, product_name, attributes)
VALUES (4, '小型桌子', JSONB_BUILD_OBJECT('color', '黑色', 'material', '塑料'));其他常用的构建 JSON 数据的函数如下：
- json_build_object
- to_json 以及 to_jsonb
- array_to_json
- row_to_json
- json_build_array 以及 jsonb_build_array
- json_object 以及 jsonb_object
查询 JSON 字段数据
JSON 字段的查询和普通字段没有什么区别，例如：
SELECT id, product_name, attributes
FROM product;
id|product_name|attributes |
--+------------+------------------------------------------------------------------------------------------------------------------+
1|椅子 |{"color": "棕色", "height": "60cm", "material": "实木"} |
2|桌子 |{"color": "黑色", "drawers": [{"side": "左侧", "height": "30cm"}, {"side": "右侧", "height": "40cm"}], "material": "金属"}|
3|茶几 |{"color": "棕色", "material": ["金属", "实木"]} |
4|小型桌子 |{"color": "黑色", "material": "塑料"} |获取单个属性
我们不仅可以查询整个 JSON 字段，也可以提取 JSON 数据中指定节点的属性值。例如：
SELECT id, product_name, attributes -> 'color' AS color
FROM product;
id|product_name|color|
--+------------+-----+
1|椅子 |"棕色" |
2|桌子 |"黑色" |
3|茶几 |"棕色" |
4|小型桌子 |"黑色" |运算符 -> 可以通过指定节点的键获取相应的数据。这种方法返回的数据仍然是 JSON 类型，使用双引号包含。
如果想要以字符串形式返回节点中的数据值，可以使用运算符 ->>。例如：
SELECT id, product_name, attributes -> 'color' AS color
FROM product;
id|product_name|color|
--+------------+-----+
1|椅子 |棕色 |
2|桌子 |黑色 |
3|茶几 |棕色 |
4|小型桌子 |黑色 |如果查询的 JSON 节点不存在，将会返回空值：
SELECT id, product_name, attributes ->> 'height' AS height
FROM product;
id|product_name|height|
--+------------+------+
1|椅子 |60cm |
2|桌子 | |
3|茶几 | |
4|小型桌子 | |获取数组属性
属性 drawers 是一个 JSON 数组，我们同样可以查询它的内容：
SELECT id, product_name, attributes ->> 'drawers' AS drawers
FROM product
WHERE id = 2;
id|product_name|drawers |
--+------------+--------------------------------------------------------------------+
2|桌子 |[{"side": "左侧", "height": "30cm"}, {"side": "右侧", "height": "40cm"}]|属性 drawers 包含了 2 个元素，每个元素代表一个抽屉，每个抽屉都拥有 2 个属性。
如果我们想要查看属性 drawers 中的第一个元素，可以多次使用 -> 运算符：
SELECT id, product_name, attributes -> 'drawers' -> 0 AS drawer1
FROM product
WHERE id = 2;
id|product_name|drawer1 |
--+------------+--------------------------------+
2|桌子 |{"side": "左侧", "height": "30cm"}|第一个 -> 运算符返回了 drawers 属性，第二个 -> 运算符返回了该属性中的第 1 个数组元素（数组下标从 0 开始）。
我们也可以使用另外两个运算符获取嵌套的属性，例如：
SELECT id, product_name,
attributes #> '{drawers, 1}' AS drawer1,
attributes #>> '{drawers, 1}' AS drawer1_text
FROM product
WHERE id = 2;
id|product_name|drawer1 |drawer1_text |
--+------------+--------------------------------+--------------------------------+
2|桌子 |{"side": "右侧", "height": "40cm"}|{"side": "右侧", "height": "40cm"}|运算符 #> 以及 #>> 可以通过指定 JSON 节点的路径获取嵌套属性，路径可以包含键的名称或者数组元素下标，返回类型分别为 JSON 和字符串。
基于 JSON 数据的过滤
如果我们想要查看颜色为棕色、材料为实木、高度为 60cm 的椅子，可以尝试使用以下查询语句：
SELECT id, product_name, attributes
FROM product
WHERE attributes = '{"color":"棕色", "material":"实木", "height":"60cm"}';
id|product_name|attributes |
--+------------+---------------------------------------------------+
1|椅子 |{"color": "棕色", "height": "60cm", "material": "实木"}|查询返回了我们期望的结果，因为查询条件中的字符串和 attributes 字段完全匹配，我们提供了完整的属性信息。
如果我们只想要基于某个属性（例如颜色）查找产品，这种方法就无法返回正确的数据了。例如：
SELECT id, product_name, attributes
FROM product
WHERE attributes = '{"color":"棕色"}';
id|product_name|attributes|
--+------------+----------+这种情况下我们可以使用前文查询属性的方法，例如：
SELECT id, product_name, attributes
FROM product
WHERE attributes ->> 'color' = '棕色';
id|product_name|attributes |
--+------------+---------------------------------------------------+
1|椅子 |{"color": "棕色", "height": "60cm", "material": "实木"}|
3|茶几 |{"color": "棕色", "material": ["金属", "实木"]} |我们使用了 ->> 运算符，而不是 -> 运算符，因为前者返回的是字符串类型，后者返回的则是 JSON 数据类型。
JSON 转换为数据行
PostgreSQL 支持将 JSON 字段转换为数据行格式。例如，jsonb_each 函数可以将每个键值对转换为一个记录：
SELECT id, product_name, jsonb_each(attributes)
FROM product;
id|product_name|jsonb_each |
--+------------+------------------------------------------------------------------------------------------------+
1|椅子 |(color,"""棕色""") |
1|椅子 |(height,"""60cm""") |
1|椅子 |(material,"""实木""") |
2|桌子 |(color,"""黑色""") |
2|桌子 |(drawers,"[{""side"": ""左侧"", ""height"": ""30cm""}, {""side"": ""右侧"", ""height"": ""40cm""}]")|
2|桌子 |(material,"""金属""") |
3|茶几 |(color,"""棕色""") |
3|茶几 |(material,"[""金属"", ""实木""]") |
4|小型桌子 |(color,"""黑色""") |
4|小型桌子 |(material,"""塑料""") |与此类似的函数还有：
- jsonb_each_text
- json_each 以及 json_each_text
- json_array_elements 以及 json_array_elements_text
- jsonb_array_elements 以及 jsonb_array_elements_text
我们还可以使用 json_object_keys 或者 jsonb_object_keys 函数获取 JSON 字段中的所有键的名称：
SELECT id, product_name, jsonb_object_keys(attributes)
FROM product;
id|product_name|jsonb_object_keys|
--+------------+-----------------+
1|椅子 |color |
1|椅子 |height |
1|椅子 |material |
2|桌子 |color |
2|桌子 |drawers |
2|桌子 |material |
3|茶几 |color |
3|茶几 |material |
4|小型桌子 |color |
4|小型桌子 |material |判断属性是否存在
PostgreSQL 还提供了一些用于判断 JSON 属性是否存在的运算符，例如 ? 运算符。
以下语句可以查找拥有 drawers 属性的产品：
SELECT id, product_name, attributes
FROM product
WHERE attributes ? 'drawers' = true;
id|product_name|attributes |
--+------------+------------------------------------------------------------------------------------------------------------------+
2|桌子 |{"color": "黑色", "drawers": [{"side": "左侧", "height": "30cm"}, {"side": "右侧", "height": "40cm"}], "material": "金属"}|其他存在性运算符可以参考 官方文档 。
更新 JSON 字段数据
使用 UPDATE 语句更新 JSON 字段时，可以通过 || 运算符将新的键值增加到已有 JSON 数据。例如：
UPDATE product
SET attributes = attributes || '{"width":"100cm"}'
WHERE id = 1;
SELECT *
FROM product
WHERE id = 1;
id|product_name|attributes |
--+------------+---------------------------------------------------------------------+
1|椅子 |{"color": "棕色", "width": "100cm", "height": "60cm", "material": "实木"}|新的属性 width 被添加到了数据的中间，因为 JSONB 数据类型不会保留键的顺序。
另外一种方法就是利用 jsonb_insert 方法，例如：
UPDATE product
SET attributes = jsonb_insert(attributes, '{"weight"}', '"1kg"')
WHERE id = 3;
SELECT *
FROM product
WHERE id = 3;
id|product_name|attributes |
--+------------+----------------------------------------------------------+
3|茶几 |{"color": "棕色", "weight": "1kg", "material": ["金属", "实木"]}|如果想要更新已有键的数值，可以使用 jsonb_set 函数。例如：
UPDATE product
SET attributes = jsonb_set(attributes, '{height}', '"75cm"')
WHERE id = 1;
SELECT *
FROM product
WHERE id = 1;
id|product_name|attributes |
--+------------+---------------------------------------------------------------------+
1|椅子 |{"color": "棕色", "width": "100cm", "height": "75cm", "material": "实木"}|以上语句将 id 等于 1 的产品的属性 height 修改为 75cm。
删除 JSON 字段数据
删除整个 JSON 字段数据可以简单地将其设置为 NULL，例如：
UPDATE product
SET attributes = NULL
WHERE id = 4;
SELECT *
FROM product
WHERE id = 4;
id|product_name|attributes|
--+------------+----------+
4|小型桌子 | |删除 JSON 字段中的某个属性可以使用 - 运算符，例如：
UPDATE product
SET attributes = attributes - 'height'
WHERE id = 1;
SELECT *
FROM product
WHERE id = 1;
id|product_name|attributes |
--+------------+---------------------------------------------------+
1|椅子 |{"color": "棕色", "width": "100cm", "material": "实木"}|产品 1 中的 height 属性已经被删除了。
另一种删除 JSON 属性的方法是利用 jsonb_set_lax 函数，例如
UPDATE product
SET attributes = jsonb_set_lax(attributes, '{"width"}', null, false, 'delete_key')
WHERE id = 1;
SELECT *
FROM product
WHERE id = 1;
id|product_name|attributes |
--+------------+---------------------------------+
1|椅子 |{"color": "棕色", "material": "实木"}|函数中的第三个参数表示将 width 属性设置为空，第四个参数表示属性不存在时不创建新的属性，第五个参数表示删除被设置为空的属性。
全文索引
当我们基于 JSON 属性查询数据时，可能会存在性能问题。我们生成一批产品数据：
WITH RECURSIVE t AS (
SELECT 10 n, '产品'||10 product_name, '{"color": "棕色", "height": "60cm", "material": "实木"}' attr
UNION ALL
SELECT n+1, '产品'||n+1, '{"color": "棕色", "height": "60cm", "material": "实木"}'
FROM t WHERE t.n<10000
INSERT INTO product
SELECT n, product_name, attr::jsonb FROM t;然后查看以下语句的执行计划：
EXPLAIN
SELECT id, product_name, attributes
FROM product
WHERE attributes @> '{"color":"黑色"}';
QUERY PLAN |
--------------------------------------------------------+
Seq Scan on product (cost=0.00..258.93 rows=1 width=78)|
Filter: (attributes @> '{"color": "黑色"}'::jsonb) |以上语句表示查找拥有黑色的产品。执行计划显示使用了全表顺序扫描，数据量很小的时候没有问题。但是对于数据量大的表，查询速度可能会很慢。
为此，PostgreSQL 提供了支持 JSON 字段的全文索引，可以优化查询的性能。这种索引的类型为 GIN（通用倒排索引），通常用于搜索引擎。
我们可以基于 JSON 字段创建一个全文索引：
CREATE INDEX idx_product_attributes ON product USING GIN(attributes);关键字 USING GIN 用于指定索引类型。
再次查看执行计划：
EXPLAIN
SELECT id, product_name, attributes
FROM product
WHERE attributes @> '{"color":"黑色"}';