构造Postgres子查询的值数组，并在WHERE子句中使用

Question

这些是我的两个表格的示例:

表1

material_id (int)        codes (jsonb)        
---------------------    -------------------------------      
1                  ['A-12','B-19','A-14','X-22']           
2                  ['X-106','A-12','X-22','B-19']        
.
.

表2

user_id        material_list (jsonb)
-----------    --------------------
1                 [2,3]
2                 [1,2]
.
.

表1包含材料id和与该材料相关联的代码数组。

表2包含用户id。每个用户都有一个与其相关联的材料列表，并将其保存为材料id数组

我想获取用户id的所有材料具有一定的代码列表。这是我尝试的查询，但它抛出了一个语法错误:

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

我想不出怎么修理它。

Answer 1

查询失败，有多种原因

首先，['A-12','B-19]不是一个有效的Postgres文本数组。使用数组常量或数组构造函数:

'{A-12,B-19}'
ARRAY['A-12','B-19']

:

• 如何将自定义类型数组传递给Postgres函数
• 传递数组文字给PostgreSQL函数

接下来，操作员?|要求text[]向右，而您提供int[]。

最后，它无论如何也不能工作，因为操作符?|检查JSON字符串，而不是数字。手动:

文本数组中的字符串是否作为顶级键或数组元素存在?

将JSON数组转换为Postgres整数数组，然后使用数组重叠操作符&&

SELECT user_id
FROM   tbl2
WHERE  ARRAY(SELECT jsonb_array_elements_text(material_list)::int)
&& ARRAY(SELECT material_id FROM tbl1 where codes ?| array['A-12','B-19']);

我强烈建议改变你的表转换JSON数组在material_list到一个Postgres整数数组(int[])为好。看到:

• 将JSON列更改为INTEGER[] ARRAY
如何将JSON数组转换为Postgres数组?

然后查询变得更简单:

SELECT user_id
FROM   tbl2
WHERE  material_list && ARRAY(SELECT material_id FROM tbl1 where codes ?| '{A-12,B-19}');

db<此处小提琴>

或者——我敢这么说吗?-正确的规范化你的关系设计。看到:

• 如何实现PostgreSQL中的多对多关系?

Answer 2

这看起来像是json数组的解嵌套过程:

select t2.user_id
from table2 t2
where exists (select 1
from table1 t1  join 
jsonb_array_elements_text(t2.material_list) j(material_id)
on t1.material_id = j.material_id::int join
jsonb_array_elements_text(t1.codes) j2(code)
on j2.code in ('A-12', 'B-19')
);

这是一个db<>小提琴