Using collations with JSONB

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

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

StackExchange Database Administrators Q#321617, answer score: 3