使用連接查詢JSONB列以通過參考外部查詢的子查詢進行過濾

我需要分析調查資料（存盤在記錄中），其中一個問題可以有多種選擇。我的目標是確定不在此問題允許選項范圍內的答案。但是，我的查詢回傳了所有內容（我懷疑是子查詢），但我不知道如何修復它。

架構

記錄將其資料存盤在dataJSONB 列中。在那里，鍵是questionUID，例如uid00000006has the answer option1。option1是一種選擇。（并非所有問題都需要下拉選單，因此其他一些值也可以，例如 42。）

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

甲question任選具有一個參照本發明的optionset，其具有的范圍（下拉）optionvalues（下拉的值），例如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)
;

DB小提琴

問題

上面的查詢回傳所有記錄，而不是只回傳一個。參考示例資料，預期結果將僅回傳值所在的記錄UNMAPPED（意味著給出的答案不是“有效”的記錄）。

uj5u.com熱心網友回復：

你應該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);

標籤：PostgreSQL jsonb

上一篇：即使有序列，客戶ID也為空

下一篇：有效地獲取GMPmpz_t的N個最低位。