Filter rows of one table with conditions coming from another table

Question

I have a table that stores conditions

CREATE TABLE conditions (field_id TEXT, value BOOLEAN)

With entries like

field_id	value
f1	true
f2	false

And another table that stores users with field values in a jsonb column

CREATE TABLE users (id BIGINT, fields JSON)

With entries like

id	values
1	{"f1":true, "f2":false}
2	{"f1":true, "f2":true}
3	{"f1":false, "f2":false}

I'd like to write a query that returns only the user with 1 in my example.

Answer 1

Personally I'd suggest to use jsonb type instead of json (which is essentially a string). And then you can do something like this:

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

But you can also expand json and match it like this:

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-fiddle

Answer 2

This is a classic Relational Division question, you just need to unpivot the JSON into separate key/value rows

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<>fiddle