How to get related items by id inside of json integer array column in MySQL

Question

My goal is how to get a relation between a column that have references in a json array to other one. In a simplified way, I have two tables:

table_a
| id  | references |
|-----|------------|
| 1   |  "[1,3]"   |
| 2   |  "[2,3]"   |

Whose references is a json array of integers and table b

table_b
| id  | name     |
|-----|----------|
| 1   | "item 1" |
| 2   | "item 2" |
| 3   | "item 3" |

So, I would like to get all items of table B related to an item of table A with id, for example, 1 that have their ids in the column references integer array (as json).

Something like this:

|-----|----------|
| 1   | "item 1" |
| 3   | "item 3" |

I have been trying to achieve this with json_contains, json_extract, json_search, etc. from docs and I think that problem is in the way to match values inside of json integers array.

For example:

SELECT JSON_SEARCH((select references from table_a where id=1), 'one', '3');

must return something but always return NULL and I dont understand way. Also I tried with 3 without quotes.

¿Any idea?

My current version of MySQL is 5.7.25

Thanks in advance.

Minimal code to reproduce:

select version();

CREATE TABLE `table_a` (
  `id` int(11) NOT NULL,
  `references` json NULL
);

CREATE TABLE `table_b` (
  `id` int(11) NOT NULL,
  `name` text NULL
);


INSERT INTO `table_a` (`id`, `references`) VALUES
(1, '\"[1,3]\"'),
(2, '\"[2,3]\"');

INSERT INTO `table_b` (`id`, `name`) VALUES
(1, 'item_1'),
(2, 'item_2'),
(3, 'item_3');


SELECT * from table_a;
SELECT * from table_b;

select `references` from table_a where id=1;

SELECT JSON_SEARCH((select `references` from table_a where id=1), 'one', '3');

Sandbox to test: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=ac557666852fa94e77fdf87158c9abe0

Answer 1

Incorrect (but checked successfully by JSON_VALID function) JSON.

The solution is monstrous:

SELECT table_b.*
FROM table_a, table_b
WHERE table_a.id = 1
  AND JSON_SEARCH(REPLACE(REPLACE(REPLACE(REPLACE(table_a.references, '"', ''), '[', '["'), ']', '"]'), ',', '","'), 'one', table_b.id) IS NOT NULL

fiddle with some additional queries which explains the problem.