我需要分析调查数据(存储在记录中),其中问题可以选择选项。我的目标是找出不在这个问题允许选项范围内的答案。然而,我的查询返回了所有内容(我怀疑是子查询),我不知道如何修复它
架构
记录将其数据存储在data
JSONB列中。在那里,密钥是question
UID,例如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
的记录(意味着它是给出了无效答案的记录)。
您应该将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);