使用來自另一個表的條件過濾一個表的行

我有一個儲存條件的表

CREATE TABLE conditions (field_id TEXT, value BOOLEAN)

像這樣的條目

另一個表將使用者的欄位值儲存在 jsonb 列中

CREATE TABLE users (id BIGINT, fields JSON)

像這樣的條目

我想編寫一個查詢，在我的範例中只返回 1 的使用者。

**這是一個經典的關係除法問題，**您只需要將 JSON 還原為單獨的鍵/值行

SELECT u.*
FROM users u
WHERE EXISTS (SELECT 1
   FROM conditions c
   LEFT JOIN json_each_text(u.fields) j ON c.field_id = j."key" and c.value = j.value::BOOL
   HAVING COUNT(*) = COUNT(j.value)  -- every condition is matched
);

db<>小提琴

我個人建議使用jsonbtype 而不是json（本質上是一個字元串）。然後你可以做這樣的事情：

select *
from users as u
where
   fields @> (select jsonb_object_agg(c.field_id, c.value) from conditions as c);

但是你也可以擴展 json 並像這樣匹配它：

select *
from users as u
where
   exists (
       select
       from conditions as c
           left join jsonb_each(u.fields) as f on
               f.key = c.field_id and
               f.value::boolean = c.value
       having
           count(*) = count(f.value)
   );

db小提琴

引用自：https://dba.stackexchange.com/questions/314134