Postgres Case-Insensitive search for Jsonb column tag keys I have a requirement to obtain the rows which match given tags key and value case-insensitively.
Here: Key search should be case insensitive and values may be String or Array of String.
Right Now, I am using following Query :
Database : Postgres
select * from my_table_name where jsonb_contains(lower(to_jsonb(jsonb_extract_path(tags,'key1'))::TEXT)::jsonb, to_jsonb('&quot;value1&quot;'::jsonb))
But it is searching key as case sensitive manner.
For Example:
above query should return all records having key is (key1,Key1,KEY1) and value is 'Value1'
Can some one help me on this?
You need to iterate over all key/value pairs in order to compare them using lower()
Something along the lines:
select mt.*
from my_table_name mt
where exists (select * 
              from jsonb_each_text(mt.tags) as t(tag, value) 
              where lower(t.tag) = 'key1'
                and lower(t.value) = 'value1'); 

This assumes your column tags is defined as jsonb (which it should be). If it's a json you need to use json_each_text() instead.