Find matching integers in JSON.

Question

I want to find 123 in a JSON string in MySQL.

This is my query:

SELECT *
  FROM `reports`
 WHERE JSON_CONTAINS(json_data, 123, "$.respCode");

When I run this code I get the following error:

Error Code: 3146. Invalid data type for JSON data in argument 1 to
function json_contains; a JSON string or JSON type is required. 0.020 sec

My json looks like this in the json_data column:

<code>{ "bla":"", "bla2":"", "bla3":"", "bla4":"", "respCode ":"123", "bla5":"" }
</code>

I also tried this

WHERE JSON_UNQUOTE(JSON_EXTRACT('json_data', "$.respCode")) = '123'

Answer 1

This is the syntax of the MySQL JSON_CONTAINS() function:

JSON_CONTAINS(target_json, candidate_json)
JSON_CONTAINS(target_json, candidate_json, path)

You can try

SELECT * FROM `reports` 
WHERE JSON_CONTAINS(json_data, '{"respCode":"123"}');

DB Fiddle Demo