Postgres Case-Insensitive search for Jsonb column tag keys

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Method 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"'
      );

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply