PostgreSQL中查询jsonb类型数组包含指定值的方法

问题 #

我有一张包含ProductID（int类型）和ProductGroups（jsonb类型）的表，其中ProductGroups是无键名的纯值数组，示例数据如下：

ProductID   ProductGroups
125481      [134, 83]
128166      [134, 83]
128175      [134, 83]
128172      [134, 83]
131492      [69, 134]
131489      [69, 134]
131860      [128, 131, 133, 100, 71]
128142      [134, 83]

我需要查询出ProductGroups包含69的所有ProductID。此前在查询带键名的jsonb数组时，我使用过如下SQL语句：

SELECT * 
FROM trans."TxnHeader" mpt, jsonb_array_elements(mpt."ExtensionProperty") as ext 
where 1=1
and jsonb_typeof(mpt."ExtensionProperty") = 'array'
and ext->>'Name' = 'posTranId' and ext->>'Value' = '8539'

现在需要针对纯值jsonb数组的场景实现目标查询。

解决方案 #

针对PostgreSQL的纯值jsonb数组，有两种实用的实现方式：

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

这是效率最高的写法，PostgreSQL原生支持用@>判断jsonb数组是否包含指定值，无需展开数组：

SELECT ProductID
FROM 你的表名
WHERE ProductGroups @> '[69]'::jsonb;

@>操作符会校验左侧jsonb数组是否包含右侧数组的所有元素，这里右侧是仅含69的数组，刚好匹配需求。

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

如果习惯用数组展开的方式，可调整代码直接匹配纯值，注意添加DISTINCT避免重复结果：

SELECT DISTINCT ProductID
FROM 你的表名, jsonb_array_elements(ProductGroups) as ext
WHERE ext::int = 69;

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