主页 / dba / 问题 / 314134
Accepted
mravey
Asked: 2022-07-07 14:16:19 +0800 CST

使用来自另一个表的条件过滤一个表的行

  • 772

我有一个存储条件的表

CREATE TABLE conditions (field_id TEXT, value BOOLEAN)

像这样的条目

field_id 价值
f1 真的
f2 错误的

另一个表将用户的字段值存储在 jsonb 列中

CREATE TABLE users (id BIGINT, fields JSON)

像这样的条目

ID 价值观
1 {“f1”：真，“f2”：假}
2 {“f1”：真，“f2”：真}
3 {“f1”：假，“f2”：假}

我想编写一个查询，在我的示例中只返回 1 的用户。

postgresql relational-division

2 个回答

  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<>小提琴

    • 3
  2. Best Answer

    我个人建议使用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小提琴

    • 1

相关问题