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