Can mysql json_contains be case insensitive?

问题: I want to be able to 'search' case insensitive with json_contains This is the json example: [ { "title": "foo Bar", "author": "melaine" }, { "title":...

问题:

I want to be able to 'search' case insensitive with json_contains

This is the json example:

[
  {
    "title": "foo Bar",
    "author": "melaine"
  },
  {
    "title": "incredible",
    "author": "steve"
  }
]

What I tried:

SELECT json_contains('[{"title":"foo Bar", "authour": "melaine"}, {"title":"foo barius", "authour": "steve"}]', '{"title":"foo bar"}')

Expected outcome: 1

Real outcome: 0

Becuase I look for "foo bar" and the value in the json is "foo Bar", I do not get a match. Is there a way make this case insensitive so I do get a match?


回答1:

You can convert both the JSON (haystack) and the searching block (needle) to lowercase using LOWER() function, for case-insensitive search:

SELECT json_contains(LOWER('[{"title":"foo Bar", "authour": "melaine"}, {"title":"foo barius", "authour": "steve"}]'), 
                     LOWER('{"title":"foo bar"}'))

DB Fiddle Demo

  • 发表于 2019-07-08 00:25
  • 阅读 ( 210 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除