Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How can I perform a case-insensitive search of array members in a JSON column?

I have a situation where we are storing tags in a JSON array, and the client started using this as a key-value store. I now need to add a way to search this data case-insensitively.

As an example, if the JSON column contains

{
  "tags": ["Key1:value1", "Key2:value2"]
}

How can I successfully search for "Key1:VaLuE1" in the array?

I’m currently querying it like this, where meta is the JSON column:

select * from events where "Key1:value1" member of (meta->"$.tags");

I’ve tried adding collate utf8mb4_general_ci to both sides of the where condition, but it doesn’t work. I’ve also tried doing a LIKE comparison, but that won’t work since I need to make sure it only retrieves the complete string (e.g. not "Key1:val"). I would also like to avoid altering the table to change the collation for the entire JSON column, but will consider this if it’s the only option.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Following Muhammad Danial Shauket's thinking, I realized I could lower just the JSON array that I was extracting from the column, like so.

    select * from events where lower("Key1:VaLuE1") member of(lower(meta->"$.tags"));

    This works perfectly! Thanks to samhita for the fiddle: https://dbfiddle.uk/k8lT4DRg


  2. 0

    You can solve your issue of case-insensitive search within a JSON array while you can ensure that the string comparison checks the exact matches (and not partial matches), so you can use a combination of JSON_EXTRACT and LOWER functions

    try this

    SELECT * 
FROM events 
WHERE LOWER(JSON_UNQUOTE(JSON_EXTRACT(meta, '$.tags'))) = LOWER('Key1:VaLuE1');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search