Filtering table for column_name/value pair

Question

I have got a very large table which I want to filter for blacklisted combinations of column and value. The blacklist is a table (much smaller) with the column name (called the "key") in one column and the value to be filtered out in another. I do not want to hard code any column names or values as the blacklist will be used for several tables.

I'm working with a postgreSQL database. I'm a mere analyst so I cannot change the tables I'm working with. I have been trying left join my main table with the blacklist and filtering out non-null values but exactly how to do that given the circumstances is beyond me. I have also played with getting the distinct intersection of blacklist "key" and main table column names but how to use that in a WHERE clause is also too complicated.

Since the problem is rather easy to describe I'm thinking there must be a pretty simple solution but may be wrong.

Blacklist table:

"client"   "key"      "value"
jamba      version    app
jamba      country    DE
jamba      version    mobile

Main table:

"client"   "gender"   "version"   "country"
jamba      m          desktop     DK
jamba      m          desktop     SE
jabma      f          mobile      DE
jamba      m          desktop     CH
jamba      f          desktop     DE
jamba      f          app         GB

Wanted result:

"client"   "gender"   "version"    "country"
jamba      m          desktop      DK
jamba      m          desktop      SE
jamba      m          desktop      CH

Anything from Germany or with version mobile/app is filtered. As specified in the blacklist.

Answer 1

You can do this with JSON:

select m.*
from main m
where not exists (select 1
                  from blacklist bl
                  where bl.client = m.client and
                        bl.value = to_jsonb(m.*)->>bl.key
                 );

This assumes that client is known to be in the table you are referencing. If not, you can of course use json functions for that as well.