Filter rows of one table with conditions coming from another table

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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

Method 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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply