PostgreSQL通过JSONB列关联查询非法问卷答案返回全量数据问题排查

问题根因 #

你现有查询的判断条件存在逻辑错误：
value::varchar是将JSONB对象（完整结构为{"value": "xxx"}）强制转为字符串，得到的结果包含大括号、引号等JSON语法符号，和optionvalue表中存储的纯选项编码（如option1）完全不匹配，导致所有关联了下拉选项的问题回答都被误判为非法。

修复后的查询语句 #

仅需将WHERE条件中的value::varchar替换为value ->> 'value'，即可正确提取回答的实际值做判断：

SELECT r.uid             AS record_uid,
       key               AS question_uid,
       os.uid            AS optionset_uid,
       value ->> 'value' AS value
FROM record r, JSONB_EACH(r.data)
JOIN question q ON q.uid = key
JOIN optionset os ON q.optionsetid = os.optionsetid
WHERE q.optionsetid IS NOT NULL
AND value ->> 'value' NOT IN (SELECT DISTINCT code FROM optionvalue WHERE optionsetid = q.optionsetid);

执行效果 #

基于你提供的测试数据执行该查询，仅会返回record_uid为uid00000011、值为UNMAPPED的非法回答记录，符合预期。

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