admin管理员组

文章数量:1124541

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.

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.

Share Improve this question asked 2 days ago hackelhackel 1,2821 gold badge15 silver badges22 bronze badges 2
  • 2 JSON is binary datatype, and any string comparison for this datatype uses binary collation which is CS. If you need in CI compare then you must convert JSON data value or extracted component value to string datatype with according function (JSON_EXTRACT, JSON_TABLE and so on) then compare using any string (not JSON) function specifying CI collation explicitly if needed. – Akina Commented 2 days ago
  • 1 The point when you start needing to look inside json data for this kind of thing is the point where you should also look at extracting these fields at INSERT/UPDATE time to include in the core table schema. This can improve performance by multiple orders of magnitude! – Joel Coehoorn Commented 2 days ago
Add a comment  | 

2 Answers 2

Reset to default 1

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');

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

本文标签: sqlHow can I perform a caseinsensitive search of array members in a JSON columnStack Overflow