Postgres 13.10中如何检查JSON列内指定开胃菜ID的存在性？

一、基于现有JSON类型的查询 #

如果暂时不修改列类型，可通过展开JSON数组来匹配目标ID：

WITH target_ids AS (
    SELECT unnest(ARRAY[3,5]) AS id
)
SELECT DISTINCT t.id
FROM target_ids t
JOIN menus m
  ON EXISTS (
    SELECT 1
    FROM json_array_elements(m.dishes->'appetizers') AS appetizer
    WHERE (appetizer->>'id')::int = t.id
  );

• 用CTEtarget_ids定义要检查的ID集合，后续直接替换数组里的数值即可
• json_array_elements会把每行的appetizers数组拆分成单独的JSON对象
• 通过EXISTS判断该ID是否在任意一行的开胃菜列表中，最后用DISTINCT去重得到结果

二、改为JSONB类型后的优化方案 #

JSONB比原生JSON更适合查询场景，建议先修改列类型，再利用索引提升性能：

1. 修改列类型为JSONB #

ALTER TABLE menus ALTER COLUMN dishes TYPE jsonb USING dishes::jsonb;

2. 创建GIN索引（可选但推荐） #

如果频繁查询开胃菜ID，创建索引能大幅提升查询速度：

CREATE INDEX idx_menus_dishes_appetizers ON menus USING GIN ((dishes->'appetizers'));

3. 高效查询语句 #

利用JSONB的@>包含操作符，结合索引快速匹配：

WITH target_ids AS (
    SELECT unnest(ARRAY[3,5]) AS id
)
SELECT DISTINCT t.id
FROM target_ids t
WHERE EXISTS (
    SELECT 1
    FROM menus m
    WHERE m.dishes->'appetizers' @> jsonb_build_array(jsonb_build_object('id', t.id))
);

也可以用更简洁的写法直接提取存在的ID：

SELECT DISTINCT (jsonb_array_elements(m.dishes->'appetizers')->>'id')::int AS existing_id
FROM menus m
WHERE (jsonb_array_elements(m.dishes->'appetizers')->>'id')::int = ANY(ARRAY[3,5]);

• jsonb_build_object和jsonb_build_array生成与数组元素结构一致的JSONB值，@>操作符会判断数组是否包含该元素
• 有GIN索引时，这个判断会走索引，比展开数组的方式性能更好

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