MySQL: How to use JSON_ARRAY in WHERE..IN clause?

Question

Is there a way to use the JSON_ARRAY in WHERE..IN clause?

For example, the following is the JSON_ARRAY.

SET @codes := JSON_ARRAY('CA' , 'NY' , 'TX');

And I need to do something like the following:

SELECT * FROM `state` WHERE `code` IN (@codes);

Answer 1

If you mysql version higher than 8.0 you can try to use JSON_TABLE, let your JSON array to data row then do in logic.

Schema (MySQL v8.0)

CREATE TABLE `state`  (
   `code` VARCHAR(50)
);

INSERT INTO `state` VALUES ('C1');
INSERT INTO `state` VALUES ('CA');

---

Query #1

SET @codes := JSON_ARRAY('CA' , 'NY' , 'TX');

SELECT * 
FROM `state` 
WHERE `code` IN (
     SELECT val
     FROM JSON_TABLE(
      @codes,
      "$[*]"
      COLUMNS(
       val varchar(50) PATH "$"
      )
   ) data
);

| code |
| ---- |
| CA   |

---

View on DB Fiddle