I have a PostgreSQL table like this one:
Table t id | keys (jsonb) ---+---------------- 1 | ["Key1", "Key2"]
My goal is to query this table to find out if one of the keys of a list is contained in the jsonb array column “keys”.
I managed to get a result using:
SELECT * FROM t WHERE keys ?| Array ['Key1', 'Key2'];
I can not find a way to make this query broader by applying a lower() on the “keys” values in the table though.
Is there a way to iterate over elements to apply the lower() on each one?
Advertisement
Answer
Thanks to the replies above I managed to find a way to do it like this:
SELECT * FROM t, jsonb_array_elements_text(keys) key WHERE lower(key) in ('key1', 'key2') ;