PostgreSQL中JSON嵌套对象与数组的WHERE过滤查询问题

表结构与数据 #

首先，我们有一张存储JSON数据的表：

CREATE TABLE js.orders (
    id serial NOT NULL PRIMARY KEY,
    info json NOT NULL
);

表中包含6行数据：

SELECT * FROM js.orders;

查询结果：

id |                                                info                                 
----+----------------------------------------------------------------------------------------------------
  1 | { "customer": "Kapil", "items": {"product": "Heineken","qty": 6}}
  2 | { "customer": "Satyen", "items": {"product": "Heineken","qty": 18}}
  3 | { "customer": "Rekha", "items": {"product": "Carlsberg","qty": 24}}
  4 | { "customer": "Madhuri", "items": {"product": "Kalyani","qty": 12}}
  5 | { "customer": "Srinivas", "items": {"product": "Kingfisher Strong","qty": 12}}
  6 | { "customer": "Saina", "items": [{"product": "Bira91","qty": 6},{"product": "Kalyani","qty": 6} ]}
(6 rows)

问题场景 #

查询购买"Heineken"的客户时，语句返回正确结果：

SELECT info ->> 'customer' AS customer 
FROM js.orders 
WHERE info -> 'items' ->> 'product' = 'Heineken';

返回：

customer 
----------
 Kapil
 Satyen
(2 rows)

但查询购买"Kalyani"的客户时，本该返回Madhuri和Saina两行，却只返回一行：

SELECT info ->> 'customer' AS customer 
FROM js.orders 
WHERE info -> 'items' ->> 'product' = 'Kalyani';

返回：

customer     
-----------------
 Madhuri
(1 row)

原因是Saina的items是数组而非单个对象，原查询仅能处理items为单个对象的情况。

解决方案 #

方案1：修改查询适配混合结构 #

通过json_typeof判断items的类型，将单个对象转换为数组后统一展开，再筛选目标产品：

SELECT info ->> 'customer' AS customer
FROM js.orders
WHERE EXISTS (
    SELECT 1
    FROM json_array_elements(
        CASE json_typeof(info -> 'items')
            WHEN 'array' THEN info -> 'items'
            ELSE json_build_array(info -> 'items')
        END
    ) AS item
    WHERE item ->> 'product' = 'Kalyani'
);

这个查询用EXISTS判断该行是否存在匹配的产品，无需去重，会返回Madhuri和Saina两行正确结果。

另一种写法（使用LATERAL JOIN+DISTINCT）：

SELECT DISTINCT info ->> 'customer' AS customer
FROM js.orders
LEFT JOIN LATERAL (
    SELECT * FROM json_array_elements(
        CASE json_typeof(info -> 'items')
            WHEN 'array' THEN info -> 'items'
            ELSE json_build_array(info -> 'items')
        END
    ) AS item
) AS items ON true
WHERE item ->> 'product' = 'Kalyani';

方案2：统一数据结构（推荐） #

如果业务允许，建议将所有items字段统一为数组格式，避免混合结构带来的查询复杂度：

-- 将单个对象的items转换为数组
UPDATE js.orders
SET info = jsonb_set(
    info::jsonb,
    '{items}',
    jsonb_build_array(info -> 'items'),
    false
)
WHERE json_typeof(info -> 'items') = 'object';

修改后，所有items都是数组，查询可简化为：

SELECT DISTINCT info ->> 'customer' AS customer
FROM js.orders,
     json_array_elements(info -> 'items') AS item
WHERE item ->> 'product' = 'Kalyani';

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