JSONSEARCH Case insensitivity

General
1

Hello,

I’m trying to run a query that utilizes JSONSEARCH in order to implement a search filter for our app.

We’re using the following query, which works, but is case-sensitive:

`JSONSEARCH(properties.value, 'one', '%${filterSearch.value}%') IS NOT NULL`

Changing it to ignore cases does not work however:

`JSONSEARCH(LOWER(properties.value), 'one', '%${filterSearch.value.toLowerCase()}%') IS NOT NULL`

Error: Where clause is invalid

Is it not possible to use string manipulation on JSON columns? If not, is there another method of doing case-insensitive json_search?

Thank you!

2

The properties.value points not to the text but to the JSON struct, thus you cannot apply sql function to that struct (as you can do with ordinary sql records).
The only column name or json syntax (when you use json-path) is applicable for now.

3

I’ve created the inner task [BKNDLSS-28846] for deeper investigation whether it is possible to implement such a behaviour.

4

Thank you, @oleg-vyalyh !

I’ve worked around it by creating a generated json column: LOWER(value), then use that against JSONSEARCH. Not the most elegant solution but it looks like it should work for now