JSON（JavaScript Object Notation、JavaScript 对象表示法）是一种轻量级的数据交换格式，采用完全独立于编程语言的文本格式来存储和表示数据。JSON 易于阅读和编写，同时也方便机器解析和生成，并且能够有效地提升网络传输效率。在网络数据传输领域，JSON 已成为了 XML 强有力的替代者。
JSON 数据类型
PostgreSQL 提供了两种 JSON 数据类型：JSON 以及 JSONB。这两种类型主要的区别在于数据存储格式，JSONB 使用二进制格式存储数据，更易于处理。
PostgreSQL 推荐优先选择 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 (
2,
'桌子',
'{"color":"黑色", "material":"金属", "drawers":[{"side":"左侧", "height":"30cm"}, {"side":"右侧", "height":"40cm"}]}'
);
我们使用同样的方法再创建一条记录：
INSERT INTO product (id, product_name, attributes)
VALUES (
3,
'茶几',
'{"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":"黑色"}';
QUERY PLAN |
------------------------------------------------------------------------------------+
Bitmap Heap Scan on product (cost=20.00..24.01 rows=1 width=78) |
Recheck Cond: (attributes @> '{"color": "黑色"}'::jsonb) |
-> Bitmap Index Scan on idx_product_attributes (cost=0.00..20.00 rows=1 width=0)|
Index Cond: (attributes @> '{"color": "黑色"}'::jsonb) |
1365
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
