使用联接查询JSONB列，以通过引用外部查询的子查询进行筛选

Question

我需要分析调查数据(存储在记录中)，其中问题可以选择选项。我的目标是找出不在这个问题允许选项范围内的答案。然而，我的查询返回了所有内容(我怀疑是子查询)，我不知道如何修复它

架构

记录将其数据存储在dataJSONB列中。在那里，密钥是questionUID，例如uid00000006具有答案option1。option1是一个可供选择的选项。(并不是所有的问题都需要下拉列表，所以其他一些值也可以，比如42。)

{"uid00000006": {"value": "option1"}, "uid00000008": {"value": 42}}

question可选地具有对具有optionvalues(下拉列表的值)范围的optionset(下拉列表)的引用，例如option1、option2、option3等。

create table record
(
recordid bigint not null primary key,
uid varchar(11) unique,
data jsonb default '{}'::jsonb not null
);

create table question
(
questionid bigint not null primary key,
uid varchar(11) not null unique,
optionsetid bigint 
);
create table optionset
(
optionsetid bigint not null primary key,
uid varchar(11) not null unique
);
create table optionvalue
(
optionvalueid bigint not null primary key,
uid varchar(11) not null unique,
code varchar(230) not null,
optionsetid bigint
);

-- create optionset
INSERT INTO optionset (optionsetid, uid) VALUES (1, 'uid00000001');
-- insert optionvalues into optionset
INSERT INTO optionvalue (optionvalueid, uid, code, optionsetid) VALUES (100, 'uid00000002', 'option1', 1);
INSERT INTO optionvalue (optionvalueid, uid, code, optionsetid) VALUES (101, 'uid00000003', 'option2', 1);
INSERT INTO optionvalue (optionvalueid, uid, code, optionsetid) VALUES (102, 'uid00000004', 'option3', 1);
INSERT INTO optionvalue (optionvalueid, uid, code, optionsetid) VALUES (103, 'uid00000005', 'option4', 1);
-- insert questions
INSERT INTO question (questionid, uid, optionsetid) VALUES (1001, 'uid00000006', 1);
INSERT INTO question (questionid, uid, optionsetid) VALUES (1002, 'uid00000007', 1);
INSERT INTO question (questionid, uid, optionsetid) VALUES (1003, 'uid00000008', NULL);
-- insert records
INSERT INTO record (recordid, uid, data) VALUES (10001, 'uid00000009', '{"uid00000006": {"value": "option1"}, "uid00000008": {"value": 42}}'::jsonb);
INSERT INTO record (recordid, uid, data) VALUES (10002, 'uid00000010', '{"uid00000006": {"value": "option2"}}'::jsonb);
INSERT INTO record (recordid, uid, data) VALUES (10003, 'uid00000011', '{"uid00000006": {"value": "UNMAPPED"}}'::jsonb);

我的查询

我起草的查询是：

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::varchar NOT IN (SELECT DISTINCT code FROM optionvalue WHERE optionsetid = q.optionsetid)
;

DBFiddle

问题

上面的查询只返回一条记录，而返回所有记录。参考样本数据，预期结果将是仅返回值为UNMAPPED的记录(意味着它是给出了无效答案的记录)。

Answer 1

您应该将value::varchar NOT IN更改为value ->> 'value' NOT IN

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);