PostgreSQL中如何筛选id存在于jsonb类型stores数组中的行

场景与数据 #

现有PostgreSQL表my_table，结构及数据如下：

id | idcm |  stores |     du     |     au     |              dtc              | 
  ----------------------------------------------------------------------------------
   1 | 20447 | [2, 5] | 2022-11-02 | 2022-11-15 | 2022-11-03 11:12:19.213799+01 | 
   2 | 20456 | [2, 5] | 2022-11-02 | 2022-11-15 | 2022-11-03 11:12:19.213799+01 | 
   3 | 20478 | [2, 5] | 2022-11-02 | 2022-11-15 | 2022-11-03 11:12:19.213799+01 | 
   4 | 20482 | [2, 5] | 2022-11-02 | 2022-11-15 | 2022-11-03 11:12:19.213799+01 | 
   5 | 20485 | [7, 5] | 2022-11-02 | 2022-11-15 | 2022-10-25 20:25:08.949996+02 | 
   6 | 20497 | [2, 6] | 2022-11-02 | 2022-11-15 | 2022-10-25 20:25:08.949996+02 |
   7 | 20499 | [5, 7] | 2022-11-02 | 2022-11-15 | 2022-10-25 20:25:08.949996+02 | 

需求 #

筛选出id值存在于该行jsonb类型字段stores数组中的记录，预期结果：

id | idcm |  stores |     du     |     au     |              dtc              | 
  ----------------------------------------------------------------------------------
   2 | 20456 | [2, 5] | 2022-11-02 | 2022-11-15 | 2022-11-03 11:12:19.213799+01 | 
   5 | 20485 | [7, 5] | 2022-11-02 | 2022-11-15 | 2022-10-25 20:25:08.949996+02 | 
   6 | 20497 | [2, 6] | 2022-11-02 | 2022-11-15 | 2022-10-25 20:25:08.949996+02 |
   7 | 20499 | [5, 7] | 2022-11-02 | 2022-11-15 | 2022-10-25 20:25:08.949996+02 | 

错误尝试分析 #

• 执行select * from my_table where stores::text ilike id::text;无结果：stores转文本后是[2,5]这类格式，和纯数字的id文本完全不匹配。
• 执行select * from my_table where stores::text ilike %id%::text;报语法错误：通配符%需要用单引号包裹，不能直接拼接；且这种字符串匹配方式存在精度问题，比如id=1时，数组里的11会被误匹配。

正确解决方法 #

方法1：使用jsonb数组包含操作符@>（推荐） #

利用PostgreSQL内置的jsonb数组检查功能，将id转换为jsonb类型后，判断stores数组是否包含该元素：

SELECT * FROM my_table WHERE stores @> to_jsonb(id);

这种方法精准且效率高，能利用jsonb字段的索引（如果已创建）。

方法2：展开数组后匹配 #

通过jsonb_array_elements展开stores数组，再匹配id：

SELECT DISTINCT t.* 
FROM my_table t, jsonb_array_elements(t.stores) s
WHERE s::integer = t.id;

此方法适合需要对数组元素做额外处理的场景，但效率略低于方法1。

修复字符串匹配方法（不推荐） #

如果一定要用字符串匹配，需要正确拼接通配符并规避误匹配：

SELECT * FROM my_table 
WHERE stores::text ilike '%,' || id::text || ',%' 
   OR stores::text ilike '[' || id::text || ',%' 
   OR stores::text ilike '%,' || id::text || ']';

这种写法能避免部分误匹配，但依然不如jsonb原生操作可靠。

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