PostgreSQL中从jsonB类型列批量查询ID的问题求助

错误原因 #

你遇到的语法错误是因为rules列是jsonB类型，直接与裸UUID字符串对比时，PostgreSQL会尝试将字符串解析为JSON，但裸UUID不属于合法JSON格式（JSON字符串必须用双引号包裹）。而转text后查询结果为空，是因为jsonB类型的字符串值转text后会自带双引号（例如存储的"2c084b4e-..."转text后变为'"2c084b4e-..."'），与你输入的无引号UUID无法匹配。

正确查询方法 #

以下是三种可行的批量查询方案：

方法1：将查询值转为jsonB类型

把每个UUID用双引号包裹后转为jsonB类型，直接与rules列对比：

SELECT * FROM rulesets
WHERE rules IN (
    '"2c084b4e-9658-4f50-afe4-a0a820d1d51d"'::jsonb,
    '"23de26ef-c3aa-4f31-81dd-1b63e1e2f699"'::jsonb,
    '"7a1af945-c5bd-4709-b5f3-24180398da7d"'::jsonb,
    '"665ecd7e-900a-4b30-b6da-19ff7a0068b3"'::jsonb
);

或使用to_jsonb()函数更直观：

SELECT * FROM rulesets
WHERE rules IN (
    to_jsonb('2c084b4e-9658-4f50-afe4-a0a820d1d51d'::text),
    to_jsonb('23de26ef-c3aa-4f31-81dd-1b63e1e2f699'::text),
    to_jsonb('7a1af945-c5bd-4709-b5f3-24180398da7d'::text),
    to_jsonb('665ecd7e-900a-4b30-b6da-19ff7a0068b3'::text)
);

方法2：去除jsonB转text后的双引号再匹配

通过trim函数去掉转text后的双引号，再用IN查询：

SELECT * FROM rulesets
WHERE trim(rules::text, '"') IN (
    '2c084b4e-9658-4f50-afe4-a0a820d1d51d',
    '23de26ef-c3aa-4f31-81dd-1b63e1e2f699',
    '7a1af945-c5bd-4709-b5f3-24180398da7d',
    '665ecd7e-900a-4b30-b6da-19ff7a0068b3'
);

方法3：使用jsonB包含操作符（简洁批量场景）

将UUID数组转为jsonB数组，用?|操作符检查rules是否包含数组中的任意元素（要求rules为单个值的jsonB）：

SELECT * FROM rulesets
WHERE rules ?| array[
    '2c084b4e-9658-4f50-afe4-a0a820d1d51d',
    '23de26ef-c3aa-4f31-81dd-1b63e1e2f699',
    '7a1af945-c5bd-4709-b5f3-24180398da7d',
    '665ecd7e-900a-4b30-b6da-19ff7a0068b3'
];

内容的提问来源于stack exchange，提问作者Asad Ahmed khan