PostgreSQL中查询购买全部指定JSON键对应商品的客户

嘿，我来帮你搞定这个问题！你用的是PostgreSQL，现在要从包含JSON字段的订单表里找出买过苹果、梨、橙子全部三种商品的客户，其实用PostgreSQL自带的JSON处理工具就能轻松实现，我给你两种实用的方法，一步步讲清楚：

方法一：拆分JSON商品键并统计 #

这种方法适合需要扩展到更多商品的场景，思路是先把每个订单里的商品拆成单独行，再统计每个客户的商品种类数：

SELECT customer_name
FROM (
    -- 把每个订单的JSON商品键拆成单独行，同时去重避免重复统计同一商品
    SELECT DISTINCT customer_name, json_object_keys(products_ordered) AS product
    FROM orders
) AS customer_product_list
-- 按客户分组，统计不同商品的数量
GROUP BY customer_name
-- 筛选出刚好拥有3种目标商品的客户
HAVING COUNT(DISTINCT product) = 3;

代码解释： #

• json_object_keys(products_ordered)：这个函数会把每个订单的JSON对象里的键（也就是商品名，比如"apples"）拆成单独的行。如果你的products_ordered字段是jsonb类型，就换成jsonb_object_keys()。
• 子查询里的DISTINCT：确保同一个客户的同一种商品只被统计一次，不管他买过多少次。
• 外层的GROUP BY和HAVING：按客户分组后，统计每个客户的商品种类数，等于3的就是三种商品都买过的客户。

方法二：用JSON存在性检查直接判断 #

如果只需要检查这三种商品，这种方法更直观，思路是分别验证客户是否买过每一种商品：

SELECT DISTINCT customer_name
FROM orders o
-- 检查客户是否买过苹果
WHERE EXISTS (
    SELECT 1 FROM orders o1
    WHERE o1.customer_name = o.customer_name
      AND o1.products_ordered::jsonb ? 'apples'
)
-- 检查客户是否买过梨
AND EXISTS (
    SELECT 1 FROM orders o1
    WHERE o1.customer_name = o.customer_name
      AND o1.products_ordered::jsonb ? 'pears'
)
-- 检查客户是否买过橙子
AND EXISTS (
    SELECT 1 FROM orders o1
    WHERE o1.customer_name = o.customer_name
      AND o1.products_ordered::jsonb ? 'oranges'
);

代码解释： #

• ?是PostgreSQL中jsonb类型的专属操作符，用来快速检查JSON对象里是否存在指定的键。如果你的字段是普通json类型，记得先转成jsonb（就是::jsonb这部分），或者用json_exists(products_ordered, '$.apples')来替代。
• 三个EXISTS子查询分别验证客户是否有购买对应商品的订单，只有三个条件都满足的客户才会被筛选出来。
• DISTINCT确保同一个客户的名称只输出一次。

小提示 #

如果以后需要检查更多商品，方法一只要修改HAVING里的数字就行；方法二则需要增加对应的EXISTS条件，你可以根据实际需求选择。

内容的提问来源于stack exchange，提问作者falidoro