Postgres从子查询构造数组用于WHERE子句的语法错误修复

• 表1筛选条件语法错误：?| 是jsonb类型的操作符，用于判断是否包含右侧数组中的任意字符串元素，要求右侧必须是标准text数组，不能直接写['A-12','B-19']这种非标准数组字面量。
• 类型不匹配错误：material_list是存储数字的jsonb数组，?|操作符仅支持匹配字符串元素，子查询返回的int数组无法直接和jsonb中的数字元素匹配。

写法1：关联查询（可读性高，适合数据量中等场景） #

SELECT DISTINCT t2.user_id
FROM table2 t2
JOIN LATERAL jsonb_array_elements_text(t2.material_list) AS m(material_id) ON true
JOIN table1 t1 ON t1.material_id = m.material_id::int
WHERE t1.codes ?| ARRAY['A-12','B-19']::text[];

写法2：子查询写法（兼容原写法逻辑） #

SELECT user_id 
FROM table2
WHERE material_list ?| array(
    SELECT material_id::text 
    FROM table1 WHERE codes ?| ARRAY['A-12','B-19']::text[]
)::text[];

如果需要筛选同时包含两个指定编码的物料，将?|替换为?&即可。

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