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?

2 Answers 2


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.

  • Thank a lots !!! It works for me partially. This query will not return records having value is ["value1"]. Like my records are stored in Jsonb like : { "KEY2": [ "VALUE1" ] } Commented May 27, 2021 at 17:56

Using the JSONPATH query language, you could query

SELECT * FROM my_table_name
WHERE jsonb_path_match(
         '$.keyvalue().key like_regex "^key1$" flag "i" && $.keyvalue().value like_regex "^value1$" flag "i"'
  • Thanks for your time Laurenz!! Commented May 27, 2021 at 17:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.