How can I retrieve all rows from a JSON array that match a specific country ID?

I have a table named TEST_TABLE in my MySQL database. This table contains a column called COUNTRIES_IDS, which has a data type of json. I used a json array because MySQL does not support multiple foreign keys on the same column. However, I am not sure if this is the most optimal approach, and if there is a better alternative.

Currently, there are 1,348,193 rows in this table.

Here is how the table looks:

CREATE TABLE TEST_TABLE (
    ID INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    COUNTRIES_IDS JSON NULL
) ENGINE = INNODB DEFAULT CHARSET = UTF8 COLLATE = UTF8_UNICODE_CI;

Here is the data that is inside the table:

The expected output of the query should be like this:

My requirements are as follows:

1. How can I fetch all the rows that contain country ID 15?
2. How can I optimize the query to retrieve results faster without having to read all 1,348,193 rows, as this would significantly slow down the query?

CREATE TABLE test (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    countries_ids JSON NULL,
-- create an index which will improve searching queries
    INDEX idx_countries_ids ((CAST(countries_ids AS UNSIGNED ARRAY)))
) ENGINE = INNODB DEFAULT CHARSET = UTF8 COLLATE = UTF8_UNICODE_CI;


INSERT INTO test VALUES
(1,	'[41, 9, 38, 15, 22, 5, 7, 32]'),
(2,	'[39, 30, 6]'),
(3,	'[20, 38, 35, 13, 32, 31, 2, 47, 46, 24, 1, 5, 9]'),
(4,	'[7]'),
(5,	'[14, 22, 15, 30, 36, 20]');

SELECT id, CAST(countries_ids AS CHAR)
FROM test
WHERE JSON_CONTAINS(countries_ids, '[15]')

SELECT id, CAST(countries_ids AS CHAR)
FROM test
WHERE JSON_OVERLAPS(countries_ids, '[15]')

SELECT id, CAST(countries_ids AS CHAR)
FROM test
WHERE 15 MEMBER OF (countries_ids)

EXPLAIN   -- an example which shows that the index is used in the query execution
SELECT id, CAST(countries_ids AS CHAR)
FROM test
WHERE 15 MEMBER OF (countries_ids)

fiddle

See: Functions That Search JSON Values, Multi-Valued Indexes