Search code examples
sqlmysqljsonmysql-json

Comparing JSON-typed column with 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?

Solution

  • 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)