PostgreSQL 16：如何基于JSONB字段值实现正确的SELECT查询？

针对org_test.orders表（包含payment、products两个JSONB字段），以下是三个目标查询的正确写法：

1. 筛选products中存在name为'Iphone'的记录 #

SELECT *
FROM org_test.orders
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(products) AS product
    WHERE product->>'name' = 'Iphone'
)
LIMIT 50;

说明：通过jsonb_array_elements展开products数组，用EXISTS判断是否存在符合条件的元素，->>直接返回文本类型，无需额外转换。

2. 筛选products中存在customFields里Color为'Red'的记录 #

SELECT *
FROM org_test.orders
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(products) AS product
    WHERE product->'customFields'->>'Color' = 'Red'
)
LIMIT 50;

说明：先定位到product的customFields对象，再提取Color字段值匹配；若部分product无customFields，条件会自动忽略这类元素，不影响判断逻辑。

3. 筛选payment中sum大于等于500的记录 #

SELECT *
FROM org_test.orders
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(payment) AS pay
    WHERE (pay->>'sum')::numeric >= 500
)
LIMIT 50;

说明：避免硬取数组索引->0（仅适用于payment数组只有一个元素的场景），用jsonb_array_elements展开所有payment元素，确保检查到数组中任意符合条件的记录。

原SQL的问题修正说明 #

你提供的原SQL存在两个明显问题：

1. 末尾多余的AND关键字导致语法错误；
2. 硬取payment->0->>'sum'仅能检查payment数组的第一个元素，若数组有多个元素会遗漏符合条件的记录，改用EXISTS+jsonb_array_elements的方式更通用。

若需同时满足三个条件的组合查询，写法如下：

SELECT *
FROM org_test.orders
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(products) AS product
    WHERE product->>'name' = 'Iphone'
)
AND EXISTS (
    SELECT 1
    FROM jsonb_array_elements(products) AS product
    WHERE product->'customFields'->>'Color' = 'Red'
)
AND EXISTS (
    SELECT 1
    FROM jsonb_array_elements(payment) AS pay
    WHERE (pay->>'sum')::numeric >= 500
)
LIMIT 50;

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