PostgreSQL中筛选JSON嵌套数组含指定值的账户数据

问题背景 #

已创建account_details表，表结构如下：

CREATE TABLE IF NOT EXISTS account_details
(
    account_id integer,
    condition json
);

表中现有数据：

需要筛选出rootcompanyid的$in数组中包含5的行（即account_id为1和3的记录），但当前查询仅返回account_id=3的行：

SELECT *
  FROM account_details
 WHERE ((condition->0->>'conditions')::json->>'rootcompanyid')::json->>'$in' = '[5]';

问题原因 #

原查询将$in数组转为字符串后直接与'[5]'做等值匹配，仅当数组仅包含5时才会命中，无法匹配包含5的多元素数组（如[35,20,5,6]）。

解决方案 #

方法1：展开JSON数组匹配（兼容JSON类型） #

通过json_array_elements展开$in数组，匹配值为5的元素后去重：

SELECT DISTINCT ad.*
FROM account_details ad
JOIN json_array_elements(
    ((condition->0->>'conditions')::json->>'rootcompanyid')::json
) AS arr(val) ON arr.val::integer = 5;

方法2：使用JSONB包含操作符（推荐） #

将condition字段转为jsonb类型（JSONB支持更高效的JSON操作）：

ALTER TABLE account_details ALTER COLUMN condition TYPE jsonb USING condition::jsonb;

之后使用@>操作符判断数组是否包含指定元素：

SELECT *
FROM account_details
WHERE condition->0->'conditions'->'rootcompanyid'->'$in' @> '[5]'::jsonb;

方法3：无需改字段类型的JSONB函数方式 #

如果无法修改字段类型，可临时转为jsonb后使用jsonb_exists_any函数判断：

SELECT *
FROM account_details
WHERE jsonb_exists_any(
    ((condition->0->'conditions')::jsonb->'rootcompanyid'->'$in'),
    ARRAY['5'::jsonb]
);

期望输出 #

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