PostgreSQL：如何筛选jsonb数组含指定@type和extId的行？

要筛选出products数组中存在@type等于"@game"且extId等于"da32af17-fa03-4a62-bd04-f026d04d16e9"的行，有以下几种高效的实现方式：

方法一：使用@>包含操作符（推荐，适合有jsonb索引的场景） #

PostgreSQL的@>操作符用于判断jsonb对象是否包含指定内容，对于数组来说，只要数组中存在至少一个元素匹配指定对象，就会返回true。

SELECT *
FROM your_table_name
WHERE products IS NOT NULL
  AND products @> '[{"@type": "@game", "extId": "da32af17-fa03-4a62-bd04-f026d04d16e9"}]';

说明： #

• products IS NOT NULL：排除products列为空的行（匹配你的表结构中空值场景）
• @>操作符会检查products数组中是否存在同时满足@type="@game"和extId="da32af17-fa03-4a62-bd04-f026d04d16e9"的元素
• 若表数据量较大，建议给products列创建GIN索引提升查询效率：

  CREATE INDEX idx_products_jsonb ON your_table_name USING GIN (products);
  

方法二：使用jsonb_path_exists函数（PostgreSQL 12+） #

通过JSONPath表达式精确匹配数组元素，语法更灵活，适合复杂条件筛选：

SELECT *
FROM your_table_name
WHERE products IS NOT NULL
  AND jsonb_path_exists(products, '$[*] ? (@."@type" == "@game" && @.extId == "da32af17-fa03-4a62-bd04-f026d04d16e9")');

说明： #

• $[*]：遍历数组中的所有元素
• ? (@."@type" == "@game" && @.extId == "...")：筛选同时满足两个条件的元素
• 该方式支持嵌套字段、多条件组合等复杂JSON结构查询

方法三：展开数组后筛选（适合需要查看匹配元素细节的场景） #

如果需要同时获取匹配的数组元素内容，可使用jsonb_array_elements将数组展开为行再筛选：

SELECT DISTINCT t.*
FROM your_table_name t
CROSS JOIN LATERAL jsonb_array_elements(t.products) AS elem
WHERE t.products IS NOT NULL
  AND elem->>'@type' = '@game'
  AND elem->>'extId' = 'da32af17-fa03-4a62-bd04-f026d04d16e9';

说明： #

• jsonb_array_elements(t.products)：将products数组的每个元素拆分为单独行
• CROSS JOIN LATERAL：关联原表和展开后的元素行
• DISTINCT：避免原表行因数组存在多个匹配元素而被重复返回

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