1

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?

Salman A
  • 262,204
  • 82
  • 430
  • 521
Vaks
  • 15
  • 4
  • What is the version of your MySQL and can you please add your table structure definition? – Jorge Campos May 31 '23 at 20:24
  • 2
    You're trying to compare a json datatype with a string, that's why the result is empty. To solve it, you either need a function that returns a json, or use a cast. – lemon May 31 '23 at 20:26
  • MySQL version is 8.0.33. Table definition: `CREATE TABLE mytable (id int, name varchar(255), data json)` P. S; Sorry, I can't add this to question, stackoverflow show me "not properly formatted code" error even If I'm just trying to save question without adding any text to it. – Vaks May 31 '23 at 20:45
  • BTW, do you also want `["b", "a"]` to match? – Salman A May 31 '23 at 20:48
  • No, I want only `["a", "b"]` to match. Ideally I just want to compare JSON column like if it was varchar column. – Vaks May 31 '23 at 20:50
  • See barmars answer. You can't compare the string representations e.g. `["b", "a"]`, `["b","a"]` and `["\u0061", "\u0062"]` are all the same. – Salman A May 31 '23 at 21:03
  • If you really want to compare strings, instead of dealing with jsons, you need to cast your json column to string (which although looks as an antipattern). – lemon May 31 '23 at 21:17

2 Answers2

2

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

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

DEMO

Barmar
  • 741,623
  • 53
  • 500
  • 612
2

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)
Salman A
  • 262,204
  • 82
  • 430
  • 521