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.
- 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
2 Answers
Reset to default 1You 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
版权声明:本文标题:sql - How can I perform a case-insensitive search of array members in a JSON column? - Stack Overflow 内容由网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:http://www.betaflare.com/web/1736627554a1945707.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论