JSONB复杂查询示例

在前面的课里我们已经学了JSONB的基础：怎么创建、修改和提取数据。现在轮到真正的挑战了——复杂查询，让你见识一下这种数据类型到底有多强。

想象一下有个网店，里面有商品目录。每个商品有基础信息（名字、ID），但属性可能完全不一样：笔记本有RAM和CPU，衣服有尺码和材质，书有作者和类型。要是全都放在不同的表里？太麻烦了。用JSONB？完美！ 但怎么查找某个品牌的所有商品，按价格排序，或者统计每个分类的数量？怎么搞定那些不是普通字段、而是藏在JSON结构里的数据？

今天我们就来搞点实际的：从简单过滤到分组聚合的复杂查询。你会看到，JSONB让PostgreSQL变成了处理各种数据的超级灵活工具。

在JSONB里过滤数据

过滤数据就像用筛子泡茶：只留下你想要的，把多余的都去掉。用JSONB更有意思，因为你不仅能按普通字段过滤，还能查JSON结构深处的数据。

JSONB过滤操作符：

• @> — “JSONB-包含”。检查JSONB对象里是不是有指定的子集。
• ? — “键存在”。检查JSONB对象里有没有指定的键。
• ?| — “任意键存在”。检查给定的键里至少有一个存在。
• ?& — “所有键都存在”。检查所有指定的键是不是都在。

例子：按键和值过滤。 假设我们有个products表，details列里存着商品的JSONB信息：

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    details JSONB
);

数据示例：

INSERT INTO products (name, details) VALUES
('Laptop', '{"brand": "Dell", "price": 1200, "specs": {"ram": "16GB", "cpu": "i7"}}'),
('Smartphone', '{"brand": "Apple", "price": 800, "specs": {"ram": "4GB", "cpu": "A15"}}'),
('Tablet', '{"brand": "Samsung", "price": 500, "specs": {"ram": "8GB", "cpu": "Exynos"}}');

结果：

要查找所有brand是"Apple"的商品：

SELECT *
FROM products 
WHERE details @> '{"brand": "Apple"}';

结果：

如果你想查所有有specs键的商品，用?操作符：

SELECT *
FROM products 
WHERE details ? 'specs';

结果：

所有行都包含details字段和specs键。

在JSONB里排序数据

有时候你想按JSONB里的值排序，而不是普通字段。你可以用->>（提取文本值）和CAST把文本转成你想要的类型。

例子：按价格排序商品：

SELECT *
FROM products 
ORDER BY (details->>'price')::INTEGER;

结果：

在JSONB里分组数据

分组可以聚合数据，输出统计信息。比如你可以查每个品牌有多少商品。

例子：统计每个品牌的商品数量：

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

结果：

实战例子

过滤和分组。 统计每个品牌价格大于600的商品数量：

SELECT
    details->>'brand' AS brand,
    COUNT(*) AS product_count
FROM products
WHERE (details->>'price')::INTEGER > 600
GROUP BY details->>'brand';

结果：

分组后排序。 现在按商品数量排序品牌：

SELECT
    details->>'brand' AS brand,
    COUNT(*) AS product_count
FROM products
GROUP BY details->>'brand'
ORDER BY product_count DESC;

复杂查询：过滤、排序、分组

假设你想找出有价格大于600的商品的品牌，并且为每个品牌选出最便宜的那一款。可以这样搞：

WITH filtered_products AS (
    SELECT *
    FROM products
    WHERE (details->>'price')::INTEGER > 600
)
SELECT
    details->>'brand' AS brand,
    MIN((details->>'price')::INTEGER) AS min_price
FROM filtered_products
GROUP BY details->>'brand'
ORDER BY min_price;

结果：

常见错误和建议

错误：操作符用错。 别搞混->和->>：前者返回对象，后者返回文本值。

错误：性能问题。 如果你经常做复杂查询，给JSONB列建个GIN索引吧。

错误：类型问题。 从JSONB里取出来的都是字符串，记得用CAST转类型。

建索引的例子：

CREATE INDEX idx_products_details ON products USING GIN (details);

这样像details @> '{"brand": "Apple"}'这种过滤会快很多。