PostgreSQL jsonb 字段查询操作符

sql -- 创建产品表，info 字段为 jsonb 类型 CREATE TABLE products ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, info JSONB );

-- 插入示例数据 INSERT INTO products (name, info) VALUES ('智能手机', '{"brand": "TechCorp", "price": 599, "tags": ["electronics", "mobile"], "specs": {"screen": 6.1, "storage": 128}}'), ('笔记本电脑', '{"brand": "ComputeX", "price": 1299, "tags": ["electronics", "computer"], "specs": {"screen": 15.6, "storage": 512}}'), ('耳机', '{"brand": "SoundMax", "price": 79, "tags": ["audio"]}');

## 基础抽取操作符

用于从 `jsonb` 中提取特定字段或路径的值，是日常查询中最常用的操作符。

### `->` 获取 jsonb 对象

通过键名返回一个 `jsonb` 对象（仍然是 JSON 类型）。

```sql
SELECT 
    name,
    info -> 'brand' AS brand_jsonb
FROM products;

结果中 brand_jsonb 列的值类似 "TechCorp"，注意它是带引号的 JSON 字符串。

->> 获取文本值

与 -> 类似，但返回的是 text 类型（去掉了 JSON 的引号）。

SELECT 
    name,
    info ->> 'brand' AS brand_text
FROM products;

结果：TechCorp，可直接用于比较或字符串操作。

访问嵌套对象

可以通过链式调用访问深层字段：

-- 获取 specs 下的 storage 值
SELECT 
    name,
    info -> 'specs' ->> 'storage' AS storage_text
FROM products;

先通过 -> 拿到内层对象，再用 ->> 取出文本。

路径抽取操作符

当需要直接通过路径访问深层次键时，路径操作符更简洁。

#> 获取指定路径的 jsonb 对象

接受一个文本数组作为路径，返回路径指向的 jsonb 值。

SELECT 
    name,
    info #> '{specs, storage}' AS storage_jsonb
FROM products;

结果：128 仍为 jsonb。

#>> 获取指定路径的文本值

与 #> 类似，但返回 text 类型。

SELECT 
    name,
    info #>> '{specs, screen}' AS screen_text
FROM products;

JSON 包含与存在性操作符

用于判断 JSON 对象之间、键或数组元素的关系，非常适用于灵活的条件筛选。

@> 判断是否包含另一个 JSON 文档

左侧包含右侧的所有内容时返回 true。

-- 查询 info 中包含 "brand": "TechCorp" 且 "price": 599 的产品
SELECT * FROM products
WHERE info @> '{"brand": "TechCorp", "price": 599}';

也可以用于数组元素：

-- 查询 tags 数组包含 "computer" 的产品
SELECT * FROM products
WHERE info @> '{"tags": ["computer"]}';

<@ 判断是否被另一个 JSON 文档包含

<@ 是 @> 的反向操作，即左侧是否被右侧包含。

SELECT '{"a":1}'::jsonb <@ '{"a":1, "b":2}'::jsonb; -- true

? 检查键或字符串是否作为顶层键存在

用于检查对象最外层是否包含某个键。

-- 查询 info 顶层存在 'brand' 键的产品
SELECT * FROM products
WHERE info ? 'brand';

?| 检查是否存在任意一个键

接受一个文本数组，对象中包含其中任意一个键时返回 true。

-- 查询 info 中是否包含 'brand' 或 'color' 中的任意一个键
SELECT * FROM products
WHERE info ?| ARRAY['brand', 'color'];

?& 检查是否存在所有键

所有给定键都存在于对象顶层时才返回 true。

-- 查询 info 中同时包含 'brand' 和 'price' 的产品
SELECT * FROM products
WHERE info ?& ARRAY['brand', 'price'];

常用的 jsonb 函数

除了操作符，PostgreSQL 还提供了一系列函数用于进一步处理数据。

jsonb_each() 展开键值对

将 jsonb 对象展开为键值对集合。

SELECT id, key, value
FROM products, jsonb_each(info);

通常与 LATERAL JOIN 或 FROM 中的逗号连用。

jsonb_array_elements() 展开数组

将 jsonb 数组展开为多行，每个元素一行。

SELECT name, tag
FROM products, jsonb_array_elements(info -> 'tags') AS tag;

jsonb_set() 更新 JSON 中的值

更新指定路径的值并返回新的 jsonb。

-- 将所有产品的价格打 9 折后更新
UPDATE products
SET info = jsonb_set(info, '{price}', (info ->> 'price')::int * 0.9)::text::jsonb;

索引与性能优化

在 jsonb 字段上使用 GIN 索引可以极大加速包含、存在性等操作。

-- 创建 GIN 索引
CREATE INDEX idx_products_info ON products USING GIN (info);

GIN 索引支持以下操作符：@>, ?, ?|, ?&。对于路径查询（如 ->）如需加速，可创建表达式索引：

CREATE INDEX idx_products_brand ON products ((info ->> 'brand'));

实战示例

1. 找出所有包含 "electronics" 标签的产品名称

SELECT name FROM products
WHERE info -> 'tags' @> '"electronics"'::jsonb;

2. 获取所有产品的屏幕尺寸，并过滤掉没有 spec 的产品

SELECT 
    name,
    info #>> '{specs, screen}' AS screen
FROM products
WHERE info ? 'specs';

3. 统计每个品牌的产品数量

SELECT 
    info ->> 'brand' AS brand,
    COUNT(*) AS cnt
FROM products
GROUP BY brand;