Using collations with JSONB

Question

I have a postgres database and a case insensitive collation. The collation was created by

CREATE COLLATION IF NOT EXISTS case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false). (I also have another collation the removes accents, so ILIKE probably can't be used)

In my table I have a JSONB column (here called "forms"). This is a jsonb array containing multiple keys. I now want to find the rows that have values with a specific string- case insensitively.

For example, the query SELECT * FROM etymologies WHERE forms @> '[{"form": "bears"}]' ; returns 3 entries. I now want to get the same 3 entries when I search for SELECT * FROM etymologies WHERE forms @> '[{"form": "Bears"}]'.

What would be the best way to achieve this? I tried SELECT * FROM etymologies WHERE forms @> '[{"form": "Bears"}]' collate case_insensitive ;, but this returns no results.

(I also tried creating an index, but was super unsure about the syntax: CREATE INDEX forms_form_case_insensitive_index ON etymologies USING GIN ((forms->'form' collate case_insensitive)); did something (it ran as slow as a usual index creation), but I am not sure what exactly.)

• user1822: To my knowledge JSON is required to be case sensitive and thus I would be very surprised if any of the JSON operators would honer such a collation.
• Sahap Asci: I haven't tried but SQL/JSON Regular Expressions may help
• Pux: @SahapAsci Thanks, although I probably can't use them due to performance reasons.
• Pux: @a_horse_with_no_name Thanks, I also suspect that this is the case. My current thinking is that I need to hack something together with generated columns.
• Sahap Asci: @Pux any use of "case-insensitivity" is somehow slow.

Answer 1

I am not sure whether a (custom) collation can be applied on a jsonb operator or not. I am guessing that you should somehow cast the value to text with ->> operator then apply collation & index.

However, you can test any jsonb value with regular expression which leads us to a solution to your original problem.

The SQL/JSON Path Language has like_regex function which allows case-insensitive match with the flag i Here is an example which returns true which means you can use it in where condition

SELECT 
 jsonb '[{"form": "Bears"}]' @? '$[*].form ? (@ like_regex "^bears$" flag "i")'; 

• @? : Does JSON path return any item for the specified JSON value
• $[*].form : take each value's form attribute from array.
• ? : filter
• @ : the value to be filtered in our case its "Bears"
• like_regex : apply regex
• "^bears$" : our regex to be applied
• flag "i" : case-insensitive

Here are some references;

• SQL/JSON Regular Expressions
• POSIX Regular Expressions

• Bergi: You can apparently drop the ? and @: "A path expression can be a Boolean predicate, although the SQL/JSON standard allows predicates only in filters. This is necessary for implementation of the @@ operator", using only @@ '$[*].form like_regex "^bears$" flag "i"'. While not documented, passing multiple values into a boolean predicate implicitly checks whether any of them match.
• Pux: Thanks, this is very helpful and works. The execution takes about a minute for me, so I might keep looking for a faster alternative.