5

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

Improve this question
5
  • 1
    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.
    – user1822
    Commented Jan 1, 2023 at 18:52
  • 1
    I haven't tried but SQL/JSON Regular Expressions may help
    – Sahap Asci
    Commented Jan 1, 2023 at 18:55
  • @SahapAsci Thanks, although I probably can't use them due to performance reasons.
    – Pux
    Commented Jan 1, 2023 at 19:56
  • @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.
    – Pux
    Commented Jan 1, 2023 at 20:02
  • @Pux any use of "case-insensitivity" is somehow slow.
    – Sahap Asci
    Commented Jan 1, 2023 at 20:39

1 Answer 1

Reset to default
4

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;

Improve this answer
2
  • 1
    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.
    – Bergi
    Commented Jan 2, 2023 at 4:05
  • Thanks, this is very helpful and works. The execution takes about a minute for me, so I might keep looking for a faster alternative.
    – Pux
    Commented Jan 2, 2023 at 10:00

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.