MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE customers (
         id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
         modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
         custinfo JSON
         );

INSERT INTO customers VALUES
        (NULL, NOW(), '{"user":"Jack","user_id":37,"zipcode":["194582","94536"]}'),
        (NULL, NOW(), '{"user":"Jill","user_id":22,"zipcode":["194568","94507","94582"]}'),
        (NULL, NOW(), '{"user":"Bob","user_id":31,"zipcode":["94477","94507"]}'),
        (NULL, NOW(), '{"user":"Mary","user_id":72,"zipcode":["194536"]}'),
        (NULL, NOW(), '{"user":"Ted","user_id":56,"zipcode":["94507","582"]}');

Records: 5  Duplicates: 0  Warnings: 0


SELECT id, modified, CAST(custinfo AS CHAR) 
  FROM customers 
  WHERE '94507' MEMBER OF(custinfo->'$.zipcode');
EXPLAIN SELECT id, modified, CAST(custinfo AS CHAR) 
  FROM customers 
  WHERE '94507' MEMBER OF(custinfo->'$.zipcode');


id
modified
CAST(custinfo AS CHAR)


2
2023-11-30 07:55:07
{"user": "Jill", "user_id": 22, "zipcode": ["194568", "94507", "94582"]}

3
2023-11-30 07:55:07
{"user": "Bob", "user_id": 31, "zipcode": ["94477", "94507"]}

5
2023-11-30 07:55:07
{"user": "Ted", "user_id": 56, "zipcode": ["94507", "582"]}




id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
customers
null
ALL
null
null
null
null
5
100.00
Using where


ALTER TABLE customers
    ADD INDEX zips( (CAST(custinfo->'$.zipcode' AS CHAR(8) ARRAY)) );

Records: 0  Duplicates: 0  Warnings: 0


SELECT id, modified, CAST(custinfo AS CHAR) 
  FROM customers 
  WHERE '94507' MEMBER OF(custinfo->'$.zipcode');
EXPLAIN SELECT id, modified, CAST(custinfo AS CHAR) 
  FROM customers 
  WHERE '94507' MEMBER OF(custinfo->'$.zipcode');


id
modified
CAST(custinfo AS CHAR)


2
2023-11-30 07:55:07
{"user": "Jill", "user_id": 22, "zipcode": ["194568", "94507", "94582"]}

3
2023-11-30 07:55:07
{"user": "Bob", "user_id": 31, "zipcode": ["94477", "94507"]}

5
2023-11-30 07:55:07
{"user": "Ted", "user_id": 56, "zipcode": ["94507", "582"]}




id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
customers
null
ref
zips
zips
35
const
3
100.00
Using where

id	modified	CAST(custinfo AS CHAR)
2	2023-11-30 07:55:07	{"user": "Jill", "user_id": 22, "zipcode": ["194568", "94507", "94582"]}
3	2023-11-30 07:55:07	{"user": "Bob", "user_id": 31, "zipcode": ["94477", "94507"]}
5	2023-11-30 07:55:07	{"user": "Ted", "user_id": 56, "zipcode": ["94507", "582"]}