29

I have a database with one column that is JSON of strings (ex. ["ART","LIT"], etc.). I want to search it using json_contains.

However, when I try: 

json_contains(\`column_name`,"ART")

It errors saying:

Invalid JSON text in argument 2 to function json_contains: "Invalid value." at position 0 in 'ART'.

Note that json_contains doesn't error with numbers in the place of "ART", just with strings. Any idea what I can do to fix/get around this?

Kanmaniselvan
  • 522
  • 1
  • 8
  • 23
Alex Beals
  • 1,965
  • 4
  • 18
  • 26

3 Answers3

73

Apparently, it treats integers differently from strings. While json_contains(`column_name`,"1") is a valid call, to check if it contains "ART", you must use json_contains(`column_name`,'"ART"') (note the double quoting).

That resolved my issue!

Alex Beals
  • 1,965
  • 4
  • 18
  • 26
  • 2
    https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html val is also a JSON object type ' "ART" ' – odedsh Nov 04 '15 at 04:28
  • do you know how to using this in spring jpa nativequery? – GeekLei Nov 22 '18 at 02:35
  • 4
    You can use [JSON_QUOTE](https://dev.mysql.com/doc/refman/8.0/en/json-creation-functions.html#function_json-quote) for quoting strings. Example: `json_contains(\`column_name\`, json_quote("ART"))` – nonylene Oct 22 '20 at 19:20
11

If the column name store tags only (one level array), like ["ART","LIT","SPORTS"]

JSON_CONTAINS(column_name, 'ART', '$')

If the column name store an key-value array like {"tag":"ART","other":"NONE"}

JSON_CONTAINS(column_name, 'ART', '$.tag')

Finally if tag value is inside a parent array, you need to use path like this:

JSON_CONTAINS(column_name, 'ART', '$.parent.tag')

https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-contains

Benjamin
  • 558
  • 7
  • 15
4

If your candidate type is string, just add double quotes on your candidate then try again.

json_contains(\`column_name`,'"ART"')

JSON_CONTAINS(target, candidate[, path])

Joe
  • 131
  • 5