PostgreSQL中JSONB数组项查询WHERE子句失效问题排查

你这三段SQL都没达到预期效果，问题出在两个关键点：JSONB类型转文本会自带双引号，还有别名使用混淆。

第一段查询问题解析 #

SELECT elem
FROM jsonb_array_elements('["*", "12/05/2020"]'::jsonb) AS elem
WHERE elem::text != '*';

这里的elem是结果集的表别名，不是列名。jsonb_array_elements默认返回的列叫value，类型是JSONB。当你把elem（整个行）转成文本时，得到的是带双引号的字符串：'"*"'和'"12/05/2020"'。

条件里elem::text != '*'其实是'"*"' != '*'，这个判断为真，所以*那条记录没被过滤掉，结果自然包含它。

第二段查询问题解析 #

SELECT * FROM (
SELECT elem::text 
FROM jsonb_array_elements('["*", "12/05/2020"]'::jsonb) AS elem
) elems WHERE elem != '*';

子查询里同样把elem当表别名，SELECT elem::text是把整个行转成带双引号的文本，而且这个列没指定别名，默认叫?column?。

外层的WHERE elem != '*'里的elem是子查询的表别名，PostgreSQL会把它转成带双引号的文本和'*'对比，结果还是不相等，过滤失效。

第三段查询问题解析 #

SELECT * FROM (
SELECT elem::text 
FROM jsonb_array_elements('["*", "12/05/2020"]'::jsonb) AS elem
) elems WHERE elem::text != '*';

和第二段逻辑一致：外层的elem是子查询的表别名，转成文本后依然带双引号，和'*'对比仍成立，所以*没被过滤。

正确写法示例 #

要拿到预期结果，得正确引用列，或者用合适的方式提取JSONB的文本值：

-- 方法1：用->>直接提取无引号的文本
SELECT value->>'$' AS elem
FROM jsonb_array_elements('["*", "12/05/2020"]'::jsonb)
WHERE value->>'$' != '*';

-- 方法2：显式指定列别名，去除双引号
SELECT elem
FROM jsonb_array_elements('["*", "12/05/2020"]'::jsonb) AS t(elem)
WHERE trim(elem::text, '"') != '*';

-- 方法3：用jsonb_text函数转换
SELECT jsonb_text(elem)
FROM jsonb_array_elements('["*", "12/05/2020"]'::jsonb) AS t(elem)
WHERE jsonb_text(elem) != '*';

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