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

Can MySQL compare a JSON-typed column with a specific string value?

I have following table:

CREATE TABLE mytable (
    id     int, 
    name   varchar(255), 
    data   json
);
id name data
1 John ["a", "b"]
2 Pete ["a", "b", "c"]
3 Mike ["a", "b"]

Where column data has JSON type, and I want to select rows where field data equal to string ["a", "b"].

Naive approach I’m currently using:

SELECT * FROM mytable WHERE data = '["a", "b"]'

Yet it always returns empty set. What is right way to compare JSON fields with strings?

Tags:

2

Answers


  1. 0

    Use the JSON_ARRAY() function to create a JSON array, and compare with this.

    SELECT * FROM mytable WHERE data = JSON_ARRAY('a', 'b');

    DEMO

    Login or Signup to reply.
  2. 0

    You can use CAST(... AS JSON) to convert the JSON-as-string to JSON data type for comparison:

    SELECT *
FROM t
WHERE data = CAST('[
  "a",
  "b"
]' AS JSON)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search