PostgreSQL JSON：存储、查询与操作 – wiki基地

作者： /

PostgreSQL JSON：存储、查询与操作的深度探索

随着互联网应用的发展，半结构化数据（Semi-structured Data）变得越来越普遍，其中 JSON (JavaScript Object Notation) 格式因其简洁、易读以及广泛的应用而成为主流。传统的关系型数据库以严格的模式（Schema）而著称，这与 JSON 的灵活性似乎有些冲突。然而，现代关系型数据库如 PostgreSQL 已经强大地集成了对 JSON 数据的支持，允许开发者在保持关系型数据库核心优势的同时，灵活地处理半结构化或无模式的数据。

本文将深入探讨 PostgreSQL 如何存储、高效查询和灵活操作 JSON 数据，涵盖 json 和 jsonb 数据类型的区别、丰富的操作符与函数、索引策略以及在实际应用中的最佳实践。

1. JSON 在 PostgreSQL 中的引入与优势

PostgreSQL 从 9.2 版本开始引入 json 数据类型，并在 9.4 版本引入了更强大的 jsonb 数据类型。这使得 PostgreSQL 不仅仅是一个纯粹的关系型数据库，更成为一个能够同时处理结构化和半结构化数据的混合数据库系统。

在 PostgreSQL 中使用 JSON 数据的主要优势包括：

  1. 灵活性 (Flexibility): 可以在不修改表模式的情况下存储具有不同属性集合的记录，非常适合存储用户偏好、产品属性、日志数据等。
  2. 数据内聚性 (Data Cohesion): 将相关联的、半结构化的数据直接存储在一条记录中，减少了传统关系型模式下可能需要的多个关联表，简化了数据模型。
  3. 性能 (Performance): 特别是 jsonb 类型配合适当的索引，可以实现对 JSON 内部数据的快速查询和检索。
  4. 富操作 (Rich Operations): PostgreSQL 提供了一整套强大的操作符和函数，用于高效地查询、提取、转换和修改 JSON 数据。

虽然 JSON 提供了灵活性，但也并非银弹。过度使用 JSON 可能导致模式管理的复杂性转移到应用层，并且某些复杂的跨JSON文档查询或聚合可能不如结构化数据高效。因此，理解何时使用 JSON 以及如何正确使用它至关重要。

2. 存储 JSON 数据：json vs jsonb

PostgreSQL 提供了两种数据类型来存储 JSON 数据：json 和 jsonb。理解它们之间的区别是高效使用 PostgreSQL JSON 功能的基础。

2.1 json 数据类型

json 类型存储的是输入的 JSON 文本的精确副本。它在存储时不会对 JSON 数据进行任何处理或验证，除了检查它是否是一个有效的 JSON 字符串。

  • 存储方式: 存储为纯文本字符串。
  • 处理: 查询时需要重新解析 JSON 字符串。
  • 特点:
    • 保留原始格式：包括空格、对象键的顺序、重复的对象键（尽管 JSON 标准允许但建议避免重复键，解析器通常只保留最后一个）。
    • 处理速度相对较慢：因为每次查询都需要重新解析。
    • 不支持索引：无法直接为 json 类型的列创建支持 JSON 内部结构的索引。

示例:

“`sql
CREATE TABLE products_json (
id SERIAL PRIMARY KEY,
data JSON
);

INSERT INTO products_json (data) VALUES
(‘{
“name”: “Laptop”,
“price”: 1200.00,
“tags”: [“electronics”, “computer”, “portable”],
“features”: {
“cpu”: “Intel i7”,
“ram”: “16GB”
},
“available”: true
}’);

— 查询时需要解析
SELECT data -> ‘name’ FROM products_json WHERE id = 1;
“`

2.2 jsonb 数据类型

jsonb 类型（’json’ binary 的缩写）存储的是 JSON 数据的二进制表示。它在输入时就会对 JSON 数据进行解析，并将其转换为一种内部的二进制格式。

  • 存储方式: 存储为解析后的二进制格式。
  • 处理: 输入时即解析，查询时无需重复解析，处理速度更快。
  • 特点:
    • 不保留原始格式：不保留空格，不保证对象键的顺序，对于重复的对象键，只会保留最后一个值。
    • 更快的处理速度：由于是二进制格式，查询和操作更高效。
    • 支持索引：可以创建 GIN 或 GiST 索引，极大地加速对 JSON 内部数据的查询。
    • 支持更丰富的操作符和函数。

示例:

“`sql
CREATE TABLE products_jsonb (
id SERIAL PRIMARY KEY,
data JSONB
);

INSERT INTO products_jsonb (data) VALUES
(‘{
“name”: “Smartphone”,
“price”: 800.00,
“tags”: [“electronics”, “mobile”],
“features”: {
“screen”: “6.5 inch”,
“camera”: “108MP”
},
“available”: true,
“name”: “Mobile Phone” — 注意：键重复
}’);

— 存储后，键顺序可能改变，重复的”name”键只保留最后一个
SELECT data FROM products_jsonb WHERE id = 1;
— 结果可能类似: {“name”: “Mobile Phone”, “price”: 800.00, “tags”: [“electronics”, “mobile”], “features”: {“screen”: “6.5 inch”, “camera”: “108MP”}, “available”: true}
“`

2.3 如何选择 json vs jsonb？

在绝大多数情况下，推荐使用 jsonb。

  • 如果你需要频繁地查询 JSON 数据的内容，或者需要对 JSON 数据进行索引以提高查询性能，那么 jsonb 是唯一的选择。
  • 如果你需要使用 PostgreSQL 提供的丰富 JSON 操作符和函数，jsonb 支持的更多更高效。
  • 只有当你需要严格保留输入的 JSON 文本格式（包括空格、键的原始顺序、重复键）时，才应该考虑使用 json 类型。但请注意，保留这些特性可能会牺牲性能和功能。

总结： 将 jsonb 视为 PostgreSQL 中处理 JSON 的首选类型。

3. 查询 JSON 数据

PostgreSQL 为 json 和 jsonb 类型提供了丰富的操作符和函数来查询和提取 JSON 数据。虽然许多操作符和函数对两种类型都适用，但 jsonb 通常有专门优化的版本，并且支持索引加速。

我们将主要以 jsonb 为例进行说明。假设我们有一个名为 orders 的表，其中包含一个 order_details 的 JSONB 列，结构示例如下：

json
{
"order_id": "ORD123",
"customer": {
"name": "Alice",
"city": "New York"
},
"items": [
{
"item_id": "A99",
"name": "Widget",
"price": 10.00,
"quantity": 2
},
{
"item_id": "B45",
"name": "Gadget",
"price": 25.50,
"quantity": 1
}
],
"status": "shipped",
"total_amount": 45.50,
"timestamp": "2023-10-27T10:00:00Z"
}

为了演示，我们先创建一个表并插入一些数据：

“`sql
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
order_details JSONB
);

INSERT INTO orders (order_details) VALUES
(‘{
“order_id”: “ORD101”,
“customer”: {
“name”: “Alice”,
“city”: “New York”
},
“items”: [
{
“item_id”: “A99”,
“name”: “Widget”,
“price”: 10.00,
“quantity”: 2
},
{
“item_id”: “B45”,
“name”: “Gadget”,
“price”: 25.50,
“quantity”: 1
}
],
“status”: “shipped”,
“total_amount”: 45.50,
“timestamp”: “2023-10-27T10:00:00Z”
}’),
(‘{
“order_id”: “ORD102”,
“customer”: {
“name”: “Bob”,
“city”: “Los Angeles”
},
“items”: [
{
“item_id”: “C78”,
“name”: “Doodad”,
“price”: 5.00,
“quantity”: 5
}
],
“status”: “pending”,
“total_amount”: 25.00,
“timestamp”: “2023-10-27T11:00:00Z”
}’),
(‘{
“order_id”: “ORD103”,
“customer”: {
“name”: “Alice”,
“city”: “New York”
},
“items”: [
{
“item_id”: “A99”,
“name”: “Widget”,
“price”: 10.00,
“quantity”: 1
}
],
“status”: “shipped”,
“total_amount”: 10.00,
“timestamp”: “2023-10-27T12:00:00Z”
}’);
“`

3.1 基本元素访问操作符

  • -> (JSON value): 从 JSON 对象中提取一个字段，或从 JSON 数组中提取一个元素（通过索引）。结果是 jsonb 类型。
  • ->> (text value): 从 JSON 对象中提取一个字段，或从 JSON 数组中提取一个元素（通过索引）。结果是 text 类型。

示例:

“`sql
— 获取第一个订单的 order_id (jsonb 类型)
SELECT order_details -> ‘order_id’ FROM orders WHERE id = 1;
— 结果: “ORD101” (注意保留了JSON字符串的引号)

— 获取第一个订单的 order_id (text 类型)
SELECT order_details ->> ‘order_id’ FROM orders WHERE id = 1;
— 结果: ORD101

— 获取第一个订单的 customer 对象 (jsonb 类型)
SELECT order_details -> ‘customer’ FROM orders WHERE id = 1;
— 结果: {“name”: “Alice”, “city”: “New York”}

— 获取第一个订单 items 数组的第一个元素 (jsonb 类型)
SELECT order_details -> ‘items’ -> 0 FROM orders WHERE id = 1;
— 结果: {“item_id”: “A99”, “name”: “Widget”, “price”: 10.00, “quantity”: 2}

— 获取第一个订单 items 数组的第一个元素的 name (text 类型)
SELECT order_details -> ‘items’ -> 0 ->> ‘name’ FROM orders WHERE id = 1;
— 结果: Widget
“`

注意: -> 和 ->> 操作符只能用于访问当前级别的键或数组索引。对于更深层次的嵌套，需要链式使用这些操作符。

3.2 路径访问操作符

当 JSON 结构嵌套较深时，链式使用 ->/->> 会变得冗长。PostgreSQL 提供了路径访问操作符。路径由一系列文本（对象键）和/或整数（数组索引）组成。

  • #> (JSON value): 按指定路径提取 JSON 子树。结果是 jsonb 类型。
  • #>> (text value): 按指定路径提取 JSON 子树，并将其转换为 text 类型。

路径参数是一个 text[] 数组。

示例:

“`sql
— 获取第一个订单的 customer 对象的 city (jsonb 类型)
SELECT order_details #> ‘{customer, city}’ FROM orders WHERE id = 1;
— 结果: “New York”

— 获取第一个订单的 customer 对象的 city (text 类型)
SELECT order_details #>> ‘{customer, city}’ FROM orders WHERE id = 1;
— 结果: New York

— 获取第一个订单 items 数组的第一个元素的 price (text 类型)
SELECT order_details #>> ‘{items, 0, price}’ FROM orders WHERE id = 1;
— 结果: 10.00
“`

路径访问操作符使得访问深层嵌套数据更加简洁。

3.3 过滤数据 (WHERE 子句)

可以在 WHERE 子句中使用上述操作符来过滤数据。

示例:

“`sql
— 查找所有状态为 ‘shipped’ 的订单
SELECT id, order_details ->> ‘order_id’
FROM orders
WHERE order_details ->> ‘status’ = ‘shipped’;
— 结果: id=1, order_id=ORD101; id=3, order_id=ORD103

— 查找所有总金额大于 30.00 的订单
— 注意: JSON 中存储的数字即使看起来是数字，用 ->> 提取出来也是 text 类型。需要显式转换为 numeric 或 double precision 进行比较。
SELECT id, order_details ->> ‘order_id’, order_details ->> ‘total_amount’
FROM orders
WHERE (order_details ->> ‘total_amount’)::numeric > 30.00;
— 结果: id=1, order_id=ORD101, total_amount=45.50

— 查找所有客户城市为 ‘New York’ 的订单
SELECT id, order_details ->> ‘order_id’, order_details #>> ‘{customer, name}’
FROM orders
WHERE order_details #>> ‘{customer, city}’ = ‘New York’;
— 结果: id=1, order_id=ORD101, customer_name=Alice; id=3, order_id=ORD103

— 查找所有包含 item_id 为 ‘A99’ 的商品的订单
— 需要使用包含操作符 @> 或使用 functions
— 方法 1: 使用 containment (@>) – 查找 JSONB 值是否包含另一个 JSONB 值
SELECT id, order_details ->> ‘order_id’
FROM orders
WHERE order_details @> ‘{“items”: [{“item_id”: “A99”}]}’;
— 结果: id=1, order_id=ORD101; id=3, order_id=ORD103
— 注意: containment 检查的是结构和值的包含关系。上面这个例子要求 items 数组包含一个对象，该对象包含 item_id:”A99″。

— 方法 2: 使用 jsonb_array_elements 将数组展开进行查找 (更精确，例如查找 quantity > 1 的A99)
SELECT DISTINCT o.id, o.order_details ->> ‘order_id’
FROM orders o,
jsonb_array_elements(o.order_details -> ‘items’) as item
WHERE item ->> ‘item_id’ = ‘A99’ AND (item ->> ‘quantity’)::int > 1;
— 结果: id=1, order_id=ORD101
“`

3.4 存在性检查操作符

PostgreSQL 提供了一系列操作符来检查 JSONB 数据中是否存在特定的键、路径或键的集合。这些操作符对于过滤非常有用，并且可以利用 GIN 索引进行加速。

  • ?: 检查字符串（键）是否存在于 JSONB 对象的顶层键中。
  • ?|: 检查字符串数组中的任何一个键是否存在于 JSONB 对象的顶层键中。
  • ?&: 检查字符串数组中的所有键是否存在于 JSONB 对象的顶层键中。
  • @>: 检查左边的 JSONB 值是否包含右边的 JSONB 值。可以用于检查对象包含特定键值对，或数组包含特定元素。
  • <@: 检查右边的 JSONB 值是否包含左边的 JSONB 值（@> 的反向）。

示例:

“`sql
— 查找所有包含 “customer” 键的订单 (基本所有订单都包含)
SELECT id, order_details ->> ‘order_id’
FROM orders
WHERE order_details ? ‘customer’;
— 结果: id=1, ORD101; id=2, ORD102; id=3, ORD103

— 查找包含 “status” 或 “total_amount” 键的订单
SELECT id, order_details ->> ‘order_id’
FROM orders
WHERE order_details ?| ARRAY[‘status’, ‘total_amount’];
— 结果: id=1, ORD101; id=2, ORD102; id=3, ORD103 (如果都包含，依然返回)

— 查找同时包含 “status” 和 “total_amount” 键的订单
SELECT id, order_details ->> ‘order_id’
FROM orders
WHERE order_details ?& ARRAY[‘status’, ‘total_amount’];
— 结果: id=1, ORD101; id=2, ORD102; id=3, ORD103 (如果都包含，依然返回)

— 使用 @> 检查对象包含：查找 customer 的 city 是 ‘New York’ 的订单
SELECT id, order_details ->> ‘order_id’
FROM orders
WHERE order_details @> ‘{“customer”: {“city”: “New York”}}’;
— 结果: id=1, ORD101; id=3, ORD103

— 使用 @> 检查数组包含：查找 items 数组中包含 item_id 为 ‘A99’ 的元素的订单
SELECT id, order_details ->> ‘order_id’
FROM orders
WHERE order_details @> ‘{“items”: [{“item_id”: “A99”}]}’;
— 结果: id=1, ORD101; id=3, ORD103
— 注意：这种 containment 检查对于数组元素是模糊匹配，只要数组中 某个 对象包含 {“item_id”: “A99”} 就匹配。它不关心 {“item_id”: “A99”} 是数组中的唯一元素还是与其他键值对共存。

— 使用 @> 检查数组包含：查找 items 数组包含完全匹配 {“item_id”: “C78”, “name”: “Doodad”, “price”: 5.00, “quantity”: 5} 的元素的订单
SELECT id, order_details ->> ‘order_id’
FROM orders
WHERE order_details @> ‘{“items”: [{“item_id”: “C78”, “name”: “Doodad”, “price”: 5.00, “quantity”: 5}]}’;
— 结果: id=2, ORD102
“`

3.5 将 JSON 数据展开为行和列

PostgreSQL 提供了强大的函数来将 JSON 数组或对象展开为关系型数据，这使得将 JSON 数据与表中的其他列进行联合查询或聚合成为可能。

  • jsonb_each(jsonb): 展开顶层 JSONB 对象为一组键/值对，每对一行。键是 text，值是 jsonb。
  • jsonb_each_text(jsonb): 展开顶层 JSONB 对象为一组键/值对，每对一行。键和值都是 text。
  • jsonb_object_keys(jsonb): 返回顶层 JSONB 对象的所有键组成的 text 数组。
  • jsonb_array_elements(jsonb): 展开顶层 JSONB 数组为一组元素，每个元素一行。结果是 jsonb。
  • jsonb_array_elements_text(jsonb): 展开顶层 JSONB 数组为一组元素，每个元素一行。结果是 text。

这些函数通常与 FROM 子句或 LATERAL JOIN 结合使用。

示例:

“`sql
— 使用 jsonb_each 展开第一个订单的顶层键值对
SELECT key, value FROM jsonb_each( (SELECT order_details FROM orders WHERE id = 1) );
/ 结果示例:
key | value
—————+——————————————–
order_id | “ORD101”
customer | {“name”: “Alice”, “city”: “New York”}
items | [{“item_id”: “A99”, …}, {“item_id”: “B45”, …}]
status | “shipped”
total_amount | 45.50
timestamp | “2023-10-27T10:00:00Z”
/

— 使用 jsonb_object_keys 获取第一个订单的所有顶层键
SELECT jsonb_object_keys(order_details) FROM orders WHERE id = 1;
— 结果: {“order_id”, “customer”, “items”, “status”, “total_amount”, “timestamp”}

— 使用 jsonb_array_elements 展开第一个订单的 items 数组
SELECT value FROM jsonb_array_elements( (SELECT order_details -> ‘items’ FROM orders WHERE id = 1) );
/* 结果示例:
value

{“item_id”: “A99”, “name”: “Widget”, “price”: 10.00, “quantity”: 2}
{“item_id”: “B45”, “name”: “Gadget”, “price”: 25.50, “quantity”: 1}
*/

— 将所有订单的 items 数组展开，并与订单信息关联 (使用 LATERAL JOIN)
SELECT o.id, o.order_details ->> ‘order_id’,
item ->> ‘item_id’ as item_id,
item ->> ‘name’ as item_name,
(item ->> ‘quantity’)::int as quantity
FROM orders o
LATERAL JOIN jsonb_array_elements(o.order_details -> ‘items’) as item;
/ 结果示例:
id | order_id | item_id | item_name | quantity
—-+———-+———+———–+———-
1 | ORD101 | A99 | Widget | 2
1 | ORD101 | B45 | Gadget | 1
2 | ORD102 | C78 | Doodad | 5
3 | ORD103 | A99 | Widget | 1
/

— 查找所有购买了 item_id 为 ‘A99’ 且 quantity > 1 的订单，并显示订单 ID 和客户姓名
SELECT DISTINCT o.id, o.order_details #>> ‘{customer, name}’ as customer_name
FROM orders o
LATERAL JOIN jsonb_array_elements(o.order_details -> ‘items’) as item
WHERE item ->> ‘item_id’ = ‘A99’ AND (item ->> ‘quantity’)::int > 1;
/ 结果示例:
id | customer_name
—-+—————
1 | Alice
/
“`

LATERAL JOIN 非常强大，它允许在 FROM 子句中引用前面表（o）的列，并将函数对每一行（o.order_details -> ‘items’）的输出作为临时的行集（item）进行连接。

4. 操作与修改 JSON 数据

除了查询，PostgreSQL 还提供了修改 JSONB 数据的功能，包括添加、更新、删除和合并元素。这些操作对于维护 JSON 数据的生命周期至关重要。

4.1 添加/更新元素

  • jsonb_set(target jsonb, path text[], new_value jsonb [, create_if_missing boolean]): 在指定的路径上设置（添加或更新）一个新的值。target 是要修改的 JSONB 值，path 是一个 text[] 数组表示要设置的路径，new_value 是要设置的新值（必须是 jsonb 类型），create_if_missing 是可选参数，默认为 true，表示如果路径不存在是否创建。

示例:

“`sql
— 更新第一个订单的状态为 ‘delivered’
UPDATE orders
SET order_details = jsonb_set(order_details, ‘{status}’, ‘”delivered”‘) — 新值需要是 jsonb 类型，字符串需要加双引号
WHERE id = 1;

— 向第二个订单添加一个备注字段
UPDATE orders
SET order_details = jsonb_set(order_details, ‘{note}’, ‘”Priority delivery requested”‘)
WHERE id = 2;

— 向第一个订单的 items 数组的第一个元素添加一个字段 “discount”: 0.1
UPDATE orders
SET order_details = jsonb_set(order_details, ‘{items, 0, discount}’, ‘0.1’::jsonb) — 数字或布尔等可以直接转换为jsonb
WHERE id = 1;

— 如果 customer 下没有 phone 字段，则添加一个
UPDATE orders
SET order_details = jsonb_set(order_details, ‘{customer, phone}’, ‘”123-456-7890″‘, true)
WHERE id = 3; — 假设 id=3 的订单 customer 下没有 phone 字段

— 如果 customer 下有 phone 字段，则更新它 (create_if_missing 为 true 时)
UPDATE orders
SET order_details = jsonb_set(order_details, ‘{customer, phone}’, ‘”987-654-3210″‘, true)
WHERE id = 3; — 此时会更新 phone 字段
“`

4.2 删除元素

  • -: 操作符用于删除 JSONB 对象中的一个键或 JSONB 数组中的一个元素（通过索引）。
    • jsonb - text: 从对象中删除具有指定键的字段。
    • jsonb - text[]: 从对象中删除具有指定键数组中的所有键的字段。
    • jsonb - integer: 从数组中删除具有指定索引的元素。
  • #<: 操作符用于按路径删除 JSONB 元素或字段。
    • jsonb #< text[]: 按路径删除指定的元素或字段。

示例:

“`sql
— 从第一个订单中删除 “timestamp” 字段
UPDATE orders
SET order_details = order_details – ‘timestamp’
WHERE id = 1;

— 从第二个订单中同时删除 “status” 和 “note” 字段
UPDATE orders
SET order_details = order_details – ARRAY[‘status’, ‘note’]
WHERE id = 2;

— 从第一个订单的 items 数组中删除第一个元素 (索引 0)
UPDATE orders
SET order_details = order_details -> ‘items’ – 0
WHERE id = 1; — 注意: 这里只修改了 items 子树，还需要把它放回原位置。更安全的方式是使用 jsonb_set 或 #<。

— 使用 #< 按路径删除第一个订单 items 数组的第一个元素
UPDATE orders
SET order_details = order_details #< ‘{items, 0}’
WHERE id = 1;

— 使用 #< 按路径删除第一个订单 customer 对象的 city 字段
UPDATE orders
SET order_details = order_details #< ‘{customer, city}’
WHERE id = 1;
“`

4.3 合并 JSONB 对象

  • ||: 操作符用于合并两个 JSONB 对象。如果两个对象有相同的键，右边对象的值会覆盖左边对象的值。对于数组，|| 操作符会简单地将右边的数组连接到左边数组的末尾。

示例:

“`sql
— 合并一个新对象到第一个订单的 order_details 中
UPDATE orders
SET order_details = order_details || ‘{“processed_by”: “system”, “processing_date”: “2023-10-27”}’
WHERE id = 1;

— 将一个新的 item 添加到第二个订单的 items 数组末尾
UPDATE orders
SET order_details = jsonb_set(
order_details,
‘{items}’,
order_details -> ‘items’ || ‘[{“item_id”: “D01”, “name”: “Accessory”, “price”: 2.50, “quantity”: 10}]’::jsonb
)
WHERE id = 2;
— 这里需要先提取 items 数组，与新数组 || 连接，再用 jsonb_set 放回去。
“`

4.4 构建 JSON 值

PostgreSQL 也提供了从关系型数据构建 JSON 值的功能，这在将查询结果格式化为 JSON 输出时非常有用。

  • json_object(key1, val1, key2, val2, ...) 或 json_object(text[] keys, text[] values): 从键值对构建 JSON 对象。键值必须是文本。
  • jsonb_object(key1, val1, key2, val2, ...) 或 jsonb_object(text[] keys, jsonb[] values): 从键值对构建 JSONB 对象。键必须是文本，值可以是任意类型（将转换为 jsonb）。
  • json_build_object(key1, val1, key2, val2, ...): 从任意类型的参数构建 JSON 对象。参数类型会自动转换为 JSON 类型。
  • jsonb_build_object(key1, val1, key2, val2, ...): 从任意类型的参数构建 JSONB 对象。参数类型会自动转换为 JSONB 类型。
  • json_array(val1, val2, ...): 从任意类型的参数构建 JSON 数组。
  • jsonb_array(val1, val2, ...): 从任意类型的参数构建 JSONB 数组。
  • json_agg(value) / jsonb_agg(value): 将输入行的值聚合为一个 JSON / JSONB 数组。
  • json_object_agg(key, value) / jsonb_object_agg(key, value): 将输入行的键值对聚合为一个 JSON / JSONB 对象。

示例:

“`sql
— 构建一个简单的 JSONB 对象
SELECT jsonb_build_object(‘name’, ‘Product A’, ‘price’, 100.00);
— 结果: {“name”: “Product A”, “price”: 100.00}

— 构建一个包含数组的 JSONB 对象
SELECT jsonb_build_object(‘id’, 1, ‘tags’, jsonb_array(‘new’, ‘sale’));
— 结果: {“id”: 1, “tags”: [“new”, “sale”]}

— 将查询结果聚合为 JSONB 数组
SELECT jsonb_agg(order_details ->> ‘order_id’) FROM orders;
— 结果: [“ORD101”, “ORD102”, “ORD103”]

— 将查询结果聚合为 JSONB 对象 (例如按客户姓名分组订单ID)
SELECT order_details #>> ‘{customer, name}’ as customer_name,
jsonb_agg(order_details ->> ‘order_id’) as order_ids
FROM orders
GROUP BY 1;
/ 结果示例:
customer_name | order_ids
—————+——————-
Alice | [“ORD101”, “ORD103”]
Bob | [“ORD102”]
/

— 将展开的 items 数组聚合回一个 JSONB 对象结构 (例如，只保留item_id和quantity)
SELECT o.id,
jsonb_agg(jsonb_build_object(
‘item_id’, item ->> ‘item_id’,
‘quantity’, (item ->> ‘quantity’)::int
)) as simplified_items
FROM orders o
LATERAL JOIN jsonb_array_elements(o.order_details -> ‘items’) as item
GROUP BY o.id;
/ 结果示例:
id | simplified_items
—-+——————————————————————–
1 | [{“item_id”: “A99”, “quantity”: 2}, {“item_id”: “B45”, “quantity”: 1}]
2 | [{“item_id”: “C78”, “quantity”: 5}]
3 | [{“item_id”: “A99”, “quantity”: 1}]
/
“`

这些构建和聚合函数在处理需要从关系型数据生成 JSON 输出的场景中非常有用。

5. JSONB 索引

高效地查询 JSONB 数据很大程度上依赖于正确的索引。由于 jsonb 存储的是二进制格式，PostgreSQL 可以为其创建专门的索引。最常用的索引类型是 GIN (Generalized Inverted Index) 和 GiST (Generalized Search Tree)，特别是 GIN。

5.1 GIN 索引 (jsonb_ops 和 jsonb_path_ops)

GIN 索引对于存储包含许多相同值的项（例如数组、文档）非常有效。它适用于需要快速查找包含特定值或路径的文档的场景。

PostgreSQL 为 jsonb 提供了两种主要的 GIN 操作符类：

  1. jsonb_ops (Default): 这是默认的操作符类。它索引了 JSONB 值中的所有键、值以及路径。

    • 支持的操作符：?, ?|, ?&, @>, <@, =。
    • 用途：适用于需要频繁使用这些操作符进行查询的场景，特别是查找包含特定键、键值对或子结构的文档。
    • 索引大小：相对较大，因为它索引了更多信息。

    sql
    -- 为 order_details 列创建默认的 GIN 索引
    CREATE INDEX orders_order_details_gin_idx ON orders USING GIN (order_details);

  2. jsonb_path_ops: 这是一个更小的、更快的操作符类，但它只索引键和路径，不索引值本身。

    • 支持的操作符：主要支持 @> (containment)。
    • 用途：适用于主要使用 @> 操作符进行包含查询的场景。索引更小，对于 @> 查询性能更好。
    • 索引大小：相对较小。

    sql
    -- 为 order_details 列创建 jsonb_path_ops GIN 索引
    CREATE INDEX orders_order_details_path_gin_idx ON orders USING GIN (order_details jsonb_path_ops);

何时使用 jsonb_ops vs jsonb_path_ops?

  • 如果你的查询主要使用 ?, ?|, ?& 来检查键是否存在，或者使用 = 进行精确匹配，或者混合使用 @> 和这些操作符，那么使用默认的 jsonb_ops。
  • 如果你的查询几乎只使用 @> 来检查一个 JSONB 值是否包含另一个 JSONB 子结构，并且需要减小索引大小或优化 @> 查询性能，那么考虑使用 jsonb_path_ops。

对于上面的 orders 表，以下查询可以利用 GIN 索引：

“`sql
— 利用 GIN 索引加速 ? 操作 (需要 jsonb_ops)
SELECT id, order_details ->> ‘order_id’ FROM orders WHERE order_details ? ‘status’;

— 利用 GIN 索引加速 @> 操作 (jsonb_ops 或 jsonb_path_ops 都可以，path_ops可能更快且索引更小)
SELECT id, order_details ->> ‘order_id’ FROM orders WHERE order_details @> ‘{“customer”: {“city”: “New York”}}’;

— 利用 GIN 索引加速 @> 操作查找数组包含 (jsonb_ops 或 jsonb_path_ops)
SELECT id, order_details ->> ‘order_id’ FROM orders WHERE order_details @> ‘{“items”: [{“item_id”: “A99”}]}’;
“`

5.2 GiST 索引

GiST 索引也可以用于 jsonb，主要用于支持 @> 和 <@ 操作符，但在大多数场景下，GIN 索引对于包含查询更常用且性能更好。GiST 索引在某些特定场景下（如范围查询或 KNN 查找，尽管后者与标准JSON查询关系不大）有优势。对于典型的 JSONB 键/值/路径查找，通常优先考虑 GIN。

sql
-- 为 order_details 列创建 GiST 索引 (不常用，作为了解)
CREATE INDEX orders_order_details_gist_idx ON orders USING GIST (order_details);

5.3 索引表达式

有时你想索引 JSONB 结构中特定路径下的值，特别是当这些值是原子类型（如文本、数字）并且你经常基于这些值进行精确匹配或范围查询时。你可以使用索引表达式来创建索引：

“`sql
— 为客户姓名创建一个基于文本值的 B-tree 索引
CREATE INDEX orders_customer_name_idx ON orders ((order_details #>> ‘{customer, name}’));

— 为总金额创建一个基于 numeric 值的 B-tree 索引
CREATE INDEX orders_total_amount_idx ON orders ((order_details ->> ‘total_amount’)::numeric);

— 为 items 数组中所有 item_id 的值创建一个 GIN 索引 (使用 jsonb_path_ops 通常够了)
— 如果需要对 item_id 进行精确查找，直接在 jsonb_path_ops 或 jsonb_ops 索引上使用 @> 即可。
— 如果需要更复杂的查询，例如基于 item_id 的聚合或 join，可能需要将 items 数组展开，
— 但为展开后的 item_id 创建索引表达式也是可能的， though it gets complex quickly.
— CREATE INDEX orders_item_ids_idx ON orders USING GIN ((order_details -> ‘items’) jsonb_path_ops); — 这会索引items数组的jsonb内容，包括item_id的值

— 如果你需要经常查询 items 数组中某个具体 item 的某个属性（例如 price > 20 的 item_id），
— 传统的 B-tree 索引表达式难以直接应用于数组内部的变长元素。
— 这时要么依赖jsonb_ops/jsonb_path_ops的containment索引，要么使用LATERAL JOIN后在结果集上查询，
— 或考虑将关键的、频繁查询的子属性提升到顶层或单独的关联表中。
“`

索引表达式适用于将 JSONB 内部的特定、结构稳定的值“提升”为可索引的、具有明确数据类型的列，从而利用传统的 B-tree 索引进行高效查询。

6. 优势、挑战与最佳实践

6.1 优势总结

  • 灵活的模式: 轻松适应数据结构的变化。
  • 简化数据模型: 减少对多个关联表的依赖，特别是在存储稀疏或变化频繁的属性时。
  • 高性能的查询和操作: 特别是 jsonb 类型配合 GIN/GiST 索引和丰富的内置函数。
  • 原子性操作: PostgreSQL 的 JSONB 操作符和函数是事务安全的。

6.2 挑战与注意事项

  • 模式管理的责任转移: 模式的强制性检查从数据库层转移到应用层，需要更严格的应用层数据验证。
  • 查询复杂性: 查询 JSON 内部数据通常不如查询标准关系型列直观，需要学习和掌握特定的操作符和函数。
  • 数据类型不明确: JSON 内部的值没有固定的数据类型（所有值本质上都是 jsonb 基元或结构），在查询时需要显式进行类型转换（如 ::numeric, ::int, ::boolean）。
  • 性能权衡: 尽管 jsonb 支持索引，但与结构化数据列上的索引相比，某些类型的查询或聚合可能仍需更多资源。特别是涉及复杂数组处理或跨多个 JSON 文档的关联时。
  • 可读性与维护: 复杂的 JSON 结构和深层嵌套可能降低数据的可读性和查询的维护性。

6.3 最佳实践

  1. 优先使用 jsonb: 除非你有非常特殊的需求必须保留原始 JSON 文本格式。
  2. 为频繁查询的路径或键创建索引: 根据你的查询模式，选择合适的 GIN 索引 (jsonb_ops 或 jsonb_path_ops) 或基于表达式的 B-tree 索引。使用 EXPLAIN 分析查询计划，验证索引是否被有效使用。
  3. 将经常查询、需要严格类型或参与复杂关联的关键属性“提升”到标准列中: 如果某个属性（如 order_id, customer_id, status）在业务逻辑中非常重要，经常用于 JOIN、GROUP BY 或 WHERE 子句，考虑将其存储为单独的列，并创建相应的索引。JSONB 列可以用于存储其余的、变化较多或不常查询的属性集合。
  4. 显式进行类型转换: 在比较或计算从 JSONB 中提取的值时，始终使用 ::type 进行显式类型转换，例如 (order_details ->> 'total_amount')::numeric。
  5. 理解并利用展开函数 (jsonb_each, jsonb_array_elements) 和 LATERAL JOIN: 这些工具在需要将 JSON 内部数据与关系型数据结合查询或分析时非常强大。
  6. 文档化你的 JSON 结构: 虽然 JSON 模式灵活，但在应用层和数据库查询中，了解 JSON 文档的预期结构至关重要。考虑使用 JSON Schema 等工具进行文档化和验证。
  7. 监控性能: 对于高负载的应用，定期监控涉及 JSONB 列的查询性能，并根据需要调整索引策略或数据模型。

7. 总结

PostgreSQL 对 JSON (特别是 jsonb) 的支持是其作为一款现代化、功能强大数据库的重要体现。它成功地在传统关系型模型的严谨性与现代应用对数据灵活性的需求之间找到了平衡。通过掌握 json 和 jsonb 的区别、灵活运用丰富的查询操作符和函数、并结合合理的索引策略，开发者可以在 PostgreSQL 中高效地存储、管理和分析半结构化数据。

从简单的键值提取到复杂的嵌套结构查询和数组展开，PostgreSQL 提供了处理 JSON 数据的全方位能力。然而，这种能力也伴随着模式管理责任的转移和潜在的查询复杂性。因此，在设计数据库模式时，明智地决定哪些数据适合存储在 JSON 中，哪些应该作为传统列，以及如何正确地索引和查询 JSON 数据，是构建高性能、可维护应用的关鍵。

深入理解本文介绍的 PostgreSQL JSON 功能，将使你能够更好地利用这一强大特性，在日益多样化的数据环境中构建更灵活、更强大的数据库应用。

发表评论

您的邮箱地址不会被公开。 必填项已用 * 标注

滚动至顶部