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

Answer 2

You can use JSON_SEARCH, checking for a non-NULL result to see if the code value is contained in @codes:

SET @codes := JSON_ARRAY('CA' , 'NY' , 'TX');
SELECT code, JSON_SEARCH(@codes, 'one', code) IS NOT NULL AS found
FROM state;

Output (for my demo)

code    found
CA      1
NY      1
TX      1
AK      0
WA      0

Or, if you just want a list of code within @codes:

SELECT code
FROM state
WHERE JSON_SEARCH(@codes, 'one', code) IS NOT NULL

Output

code
CA
NY
TX

Demo on dbfiddle