skip to Main Content

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?

2

Answers


  1. 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. 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