Postgres Case-Insensitive search for Jsonb column tag keys

Question

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('"value1"'::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?

Answer 1

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.

Answer 2

Using the JSONPATH query language, you could query

SELECT * FROM my_table_name
WHERE jsonb_path_match(
         tags,
         '$.keyvalue().key like_regex "^key1$" flag "i" && $.keyvalue().value like_regex "^value1$" flag "i"'
      );