MySQL 8.0

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

CREATE TABLE t1 (id INT,   json_field JSON);
INSERT INTO t1 VALUES
(1,    '[1,2,3]'),
(2,    '[2,3]'),
(3,    '[1,3]');
SELECT id, CAST(json_field AS CHAR) FROM t1;

Records: 3  Duplicates: 0  Warnings: 0




id
CAST(json_field AS CHAR)


1
[1, 2, 3]

2
[2, 3]

3
[1, 3]


CREATE TABLE t2 (id INT,   name VARCHAR(255));
INSERT INTO t2 VALUES
(1,    'bob'),
(2,    'sam'),
(3,    'mary');
SELECT * FROM t2;

Records: 3  Duplicates: 0  Warnings: 0




id
name


1
bob

2
sam

3
mary


SELECT t1.id, CAST(t1.json_field AS CHAR), t2.id, t2.name
FROM t1
JOIN t2 ON JSON_OVERLAPS(t1.json_field, CAST(t2.id AS JSON))
WHERE t2.name = 'sam'


id
CAST(t1.json_field AS CHAR)
id
name


1
[1, 2, 3]
2
sam

2
[2, 3]
2
sam