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