介绍
在本实验中，你将探索 PostgreSQL 的 JSONB 数据类型的高级操作。你将学习如何在关系型数据库中有效地存储、管理和查询 JSON 数据。
本实验将指导你创建一个带有 JSONB 列的表，插入数据，然后应用 GIN 索引来提高搜索性能。你还将练习查询嵌套的 JSON 结构，更新 JSONB 文档中的特定元素，以及对你的 JSON 数据执行聚合计算。
创建表并索引 JSONB 字段
在此步骤中，你将创建一个表来存储使用 JSONB 数据类型的产品信息，然后创建一个 GIN 索引来优化对此数据的查询。JSONB 以分解的二进制格式存储 JSON 数据，输入时稍慢，但处理速度快得多。GIN（广义倒排索引）非常适合索引复合值，例如 JSONB 列中的值。
首先，打开终端并使用 psql 交互式 shell 连接到 PostgreSQL 数据库：
sudo -u postgres psql
你现在将看到 PostgreSQL 提示符，它看起来像 postgres=#。
接下来，创建一个名为 products 的表，其中包含一个 id 列和一个 JSONB 类型的 data 列。
CREATE TABLE products (
id SERIAL PRIMARY KEY,
data JSONB
);
现在，将一些示例产品数据插入 products 表中。
INSERT INTO products (data) VALUES
('{"name": "Laptop", "price": 1200, "tags": ["electronics", "computer"]}'),
('{"name": "Keyboard", "price": 75, "tags": ["electronics", "accessory"]}'),
('{"name": "Mouse", "price": 30, "tags": ["electronics", "accessory"]}'),
('{"name": "Monitor", "price": 300, "tags": ["electronics", "display"]}');
为了显著加快 data 列内的搜索速度，请在其上创建 GIN 索引。
CREATE INDEX idx_products_data ON products USING GIN (data);
你可以使用 \di 命令来验证索引是否已成功创建，该命令会列出所有索引。
\di
你应该在关系列表中看到 idx_products_data，类似于下面的输出：
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+---------------------+-------+----------+-----------+------------+-------------
public | idx_products_data | index | postgres | products | 16 kB |
public | products_pkey | index | postgres | products | 16 kB |
(2 rows)
你已成功设置了表并索引了 JSONB 列。你将停留在 psql shell 中进行下一步。
查询嵌套 JSON 结构
在此步骤中，你将学习如何查询 JSONB 列中的数据，包括嵌套结构。PostgreSQL 为此提供了多种运算符。
首先，让我们向 products 表插入一些具有更复杂、嵌套数据的产品。
INSERT INTO products (data) VALUES
('{"name": "Gaming PC", "price": 1500, "specs": {"cpu": "Intel i7", "ram": "32GB", "storage": "1TB SSD"}}'),
('{"name": "Office PC", "price": 800, "specs": {"cpu": "Intel i5", "ram": "16GB", "storage": "500GB SSD"}}');
要访问顶级键，你可以使用 ->> 运算符，它将值作为文本返回。让我们查找“Laptop”的价格。
SELECT data ->> 'price' FROM products WHERE data ->> 'name' = 'Laptop';
输出将是：
price
-------
1200
(1 row)
要访问嵌套 JSON 对象中的键，你可以链式使用 -> 和 ->> 运算符。-> 运算符获取 JSON 对象字段，而 ->> 则将其作为文本获取。让我们检索“Gaming PC”的 CPU 规格。
SELECT data -> 'specs' ->> 'cpu' FROM products WHERE data ->> 'name' = 'Gaming PC';
此命令将返回 CPU 类型：
?column?
--------------
Intel i7
(1 row)
你也可以在 WHERE 子句中使用这些运算符，根据嵌套值过滤结果。例如，查找所有具有“Intel i5”CPU 的产品。
SELECT data ->> 'name' FROM products WHERE data -> 'specs' ->> 'cpu' = 'Intel i5';
查询将返回匹配产品的名称：
name
-----------
Office PC
(1 row)
你现在能够查询 JSONB 列中的顶级和嵌套数据了。
更新特定 JSONB 元素
在此步骤中，你将学习如何使用 jsonb_set 函数修改 JSONB 列中的特定元素。这比检索整个 JSON 对象、在应用程序中修改它然后写回更有效率。
jsonb_set 函数的语法如下：jsonb_set(target_jsonb, path_array, new_value_jsonb, create_if_missing)。
让我们将“Laptop”的价格从 1200 更新到 1250。价格的路径是 '{price}'，新值必须转换为 JSONB。
UPDATE products
SET data = jsonb_set(data, '{price}', '1250'::jsonb)
WHERE data ->> 'name' = 'Laptop';
通过选择笔记本电脑的数据来验证更新。
SELECT data FROM products WHERE data ->> 'name' = 'Laptop';
输出应显示新价格：
data
--------------------------------------------------------------------------
{"name": "Laptop", "price": 1250, "tags": ["electronics", "computer"]}
(1 row)
你也可以更新嵌套值。让我们将“Office PC”的 RAM 升级到“32GB”。嵌套 ram 键的路径是 '{specs,ram}'。
UPDATE products
SET data = jsonb_set(data, '{specs,ram}', '"32GB"'::jsonb)
WHERE data ->> 'name' = 'Office PC';
现在，验证此嵌套更新。
SELECT data -> 'specs' FROM products WHERE data ->> 'name' = 'Office PC';
结果将确认 ram 值的更改：
?column?
------------------------------------------------------------
{"cpu": "Intel i5", "ram": "32GB", "storage": "500GB SSD"}
(1 row)
你现在已经学会了如何对 JSONB 数据执行有针对性的更新。
聚合 JSON 数据
在最后一步中，你将对从 JSONB 列中提取的数据执行聚合计算。这对于报告和分析非常有用。
要执行计算，你通常需要提取一个值并将其转换为数字类型。让我们计算所有产品的平均价格。
SELECT AVG((data ->> 'price')::numeric) FROM products;
该查询将 price 提取为文本，将其转换为 numeric，然后计算平均值。输出将是一个数字：
avg
------------------------
659.1666666666666667
(1 row)
你也可以在 GROUP BY 子句中使用聚合函数。让我们找出每个标签的产品总数。为此，我们首先需要使用 jsonb_array_elements_text 将 tags 数组展开为单独的行。
SELECT tag, COUNT(*)
FROM products, jsonb_array_elements_text(data -> 'tags') AS tag
GROUP BY tag;
此查询为每个唯一标签生成一个计数：
tag | count
-------------+-------
accessory | 2
electronics | 4
computer | 1
display | 1
(4 rows)
最后，让我们找出所有带有“electronics”标签的产品的总价值。
SELECT SUM((data ->> 'price')::numeric)
FROM products
WHERE data -> 'tags' @> '"electronics"'::jsonb;
@> 运算符检查左侧的 JSONB 值是否包含右侧的 JSONB 值。在这里，我们检查 tags 数组是否包含元素“electronics”。
结果是四种电子产品的价格总和：
sum
--------
1655
(1 row)
你现在已经学会了如何在 JSONB 数据上使用聚合函数。要退出 psql shell，请输入：
\q
总结
在本实验中，你学习了在 PostgreSQL 中处理 JSONB 数据的几种高级操作。你首先创建了一个带有 JSONB 列的表，并了解了使用 GIN 索引优化查询性能的重要性。然后，你练习了使用 -> 和 ->> 操作符查询顶层和嵌套的 JSONB 数据。此外，你还学习了如何使用 jsonb_set 函数对 JSONB 文档进行有针对性的修改，以及如何使用 AVG、COUNT 和 SUM 等聚合函数对你的 JSON 数据进行强大的数据分析。
