Question 210207 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Find matching integer in JSON

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

Here is my query :

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

When I run this code I get this 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 in the json_data column looks like this

{ "bla":"", "bla2":"", "bla3":"", "bla4":"", "respCode":"123", "bla5":"" }

i also tried

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

but still no luck

What am I doing wrong here? Thank you.

Tags:

2

Answers


  1. 0

    Here 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

    Login or Signup to reply.
  2. 0

    Based on the provided JSON data, it seems like the "respCode" value is stored as a string (enclosed in double quotes) in your JSON object. However, in your query, you are trying to search for the number 123 in the JSON object.

    To fix this issue, you need to search for the string representation of the number 123 instead of the numeric value. You can do this by enclosing 123 in double quotes in your query. Here’s the corrected query:

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

    By doing this, the query will correctly find the JSON objects that contain the string "123" in the "respCode" field.

    Let me know if this works!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search