PostgreSQL JSON/JSONB 高级操作

介绍

在这个实验（Lab）中，我们将探索 PostgreSQL JSON/JSONB 数据类型上的高级操作。我们将专注于提升 JSONB 列中的查询性能和数据操作。

你将学习如何在 JSONB 字段上创建 GIN 索引以优化搜索，查询嵌套的 JSON 结构，更新 JSONB 列中的特定元素，以及聚合 JSON 数据以进行报告和分析。

Skills Graph

使用 GIN 索引 JSONB 字段

在这一步中，我们将探索如何在 PostgreSQL 中为 JSONB 字段创建 GIN 索引。GIN（Generalized Inverted Index，广义倒排索引）索引对于索引 JSONB 数据特别有用，因为它们允许你高效地搜索 JSON 文档中的键和值。

首先，使用 psql 命令以 postgres 用户身份连接到 PostgreSQL 数据库：

sudo -u postgres psql

现在，让我们创建一个名为 products 的表，其中包含一个 JSONB 类型的 data 列：

CREATE TABLE IF NOT EXISTS products (
    id SERIAL PRIMARY KEY,
    data JSONB
);

接下来，将一些示例数据插入到 products 表中：

INSERT INTO products (data) VALUES
('{"name": "Laptop", "price": 1200, "features": ["16GB RAM", "512GB SSD"]}'),
('{"name": "Keyboard", "price": 75, "features": ["Mechanical", "RGB"]}'),
('{"name": "Mouse", "price": 30, "features": ["Wireless", "Ergonomic"]}'),
('{"name": "Monitor", "price": 300, "features": ["27 inch", "144Hz"]}');

为了加速对 JSONB 字段的查询，我们可以创建一个 GIN 索引。例如，要索引整个 JSONB 文档，请执行以下命令：

CREATE INDEX idx_products_data ON products USING GIN (data);

要验证索引是否已创建，你可以在 psql 中使用 \di 命令：

\di idx_products_data

输出应显示索引 idx_products_data 及其详细信息。

最后，退出 psql shell：

\q

查询嵌套的 JSON 结构

在这一步中，我们将学习如何使用 JSONB 数据类型在 PostgreSQL 中查询嵌套的 JSON 结构。

首先，使用 psql 命令以 postgres 用户身份连接到 PostgreSQL 数据库：

sudo -u postgres psql

让我们向 products 表中添加一些更复杂的嵌套数据。

INSERT INTO products (data) VALUES
('{"name": "Gaming PC", "price": 1500, "specs": {"cpu": "Intel i7", "ram": "32GB", "storage": "1TB SSD", "gpu": "Nvidia RTX 3070"}}'),
('{"name": "Office PC", "price": 800, "specs": {"cpu": "Intel i5", "ram": "16GB", "storage": "500GB SSD", "gpu": "Integrated"}}');

要访问嵌套 JSON 结构中的键，你可以链式使用 -> 操作符。例如，要检索“Gaming PC”的 CPU，你可以使用：

SELECT data -> 'specs' ->> 'cpu' FROM products WHERE name = 'Gaming PC';

->> 操作符用于将值作为文本检索。如果你使用 ->，结果仍然是一个 JSONB 对象。

你还可以根据嵌套 JSON 结构中的值来过滤行。例如，要查找所有具有 Intel i5 CPU 的产品：

SELECT * FROM products WHERE data -> 'specs' ->> 'cpu' = 'Intel i5';

让我们找到具有 Nvidia RTX 3070 GPU 的产品的名称。

SELECT name FROM products WHERE data -> 'specs' ->> 'gpu' = 'Nvidia RTX 3070';

这个查询应该返回 "Gaming PC"。

最后，退出 psql shell：

\q

更新特定的 JSONB 元素

在这一步中，我们将学习如何在 PostgreSQL 中更新 JSONB 列中的特定元素。

首先，使用 psql 命令以 postgres 用户身份连接到 PostgreSQL 数据库：

sudo -u postgres psql

我们将使用 jsonb_set 函数来更新 JSONB 数据中的特定元素。基本语法是：

jsonb_set(target JSONB, path TEXT[], new_value JSONB, create_missing BOOLEAN)

假设我们要将“Laptop”的价格更新为 1250。

UPDATE products
SET data = jsonb_set(data, '{price}', '1250'::JSONB)
WHERE name = 'Laptop';

要验证更新，你可以运行以下查询：

SELECT data FROM products WHERE name = 'Laptop';

输出应显示“Laptop”的价格已更新为 1250。

让我们将“Office PC”的价格增加 100。

UPDATE products
SET data = jsonb_set(data, '{price}', ((data ->> 'price')::numeric + 100)::TEXT::JSONB)
WHERE name = 'Office PC';

要验证更新，你可以运行以下查询：

SELECT data FROM products WHERE name = 'Office PC';

输出应显示“Office PC”的价格已增加 100。

最后，退出 psql shell：

\q

聚合 JSON 数据

在这一步中，我们将探索如何在 PostgreSQL 中聚合存储在 JSONB 列中的数据。

首先，使用 psql 命令以 postgres 用户身份连接到 PostgreSQL 数据库：

sudo -u postgres psql

聚合 JSONB 数据通常涉及从 JSONB 对象中提取值，然后应用聚合函数，如 SUM、AVG、MIN、MAX 和 COUNT。

要计算所有产品的平均价格，你可以使用以下查询：

SELECT AVG((data ->> 'price')::numeric) FROM products;

在这里，我们使用 ->> 将 price 提取为文本，将其转换为数值类型，然后使用 AVG 函数计算平均值。

让我们向产品添加一个“category”（类别）字段：

UPDATE products SET data = jsonb_set(data, '{category}', '"Electronics"'::JSONB) WHERE id IN (1,4,5);
UPDATE products SET data = jsonb_set(data, '{category}', '"Accessories"'::JSONB) WHERE id IN (2,3);
UPDATE products SET data = jsonb_set(data, '{category}', '"Computers"'::JSONB) WHERE id IN (6);

现在，我们可以计算每个类别中产品的数量：

SELECT data ->> 'category', COUNT(*) FROM products GROUP BY data ->> 'category';

此查询将 category 值提取为文本，并根据此值对行进行分组。

让我们计算“Electronics”（电子产品）类别中所有产品的总价。

SELECT SUM((data ->> 'price')::numeric) FROM products WHERE data ->> 'category' = 'Electronics';

此查询应返回 Laptop、Monitor 和 Gaming PC 的价格总和。

最后，退出 psql shell：

\q

总结

在这个实验中，我们探索了 PostgreSQL JSONB 数据上的高级操作，重点是索引、查询、更新和聚合。我们在 JSONB 字段上创建了 GIN 索引，以优化查询性能。我们还演示了如何查询嵌套的 JSON 结构以及更新特定的 JSONB 元素。最后，我们学习了如何使用各种函数来聚合 JSON 数据。