Search code examples
sqlarrayspostgresqlsubqueryjsonb

How to use LOWER() on elements of a jsonb column in PostgreSQL?

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?

Solution

  • 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') ;