MySQL 8.0

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

CREATE TABLE space_id (
id varchar(10));

INSERT INTO space_id VALUES
('spa_1'),
('spa_2'),
('spa_3'),
('spa_4'),
('spa_5');


CREATE TABLE batch_ids (
batch_id int ,
request_id varchar(255));

INSERT INTO batch_ids VALUES
(1,'task_spa_1_task'),
(2,'task_spa_2_task'),
(3,'task_spa_3_task'),
(4,'task_spa_4_task'),
(5,'task_spa_5_task'),
(6,'task_spa_6_task'),
(7,'task_spa_2_task'),
(8,'task_spa_3_task'),
(9,'task_spa_9_task'),
(10,'task_spa_8_task');
select s.*,b.*
from space_id s 
INNER JOIN batch_ids b ON b.request_id LIKE CONCAT('%',s.id,'%');


id
batch_id
request_id


spa_1
1
task_spa_1_task

spa_2
2
task_spa_2_task

spa_3
3
task_spa_3_task

spa_4
4
task_spa_4_task

spa_5
5
task_spa_5_task

spa_2
7
task_spa_2_task

spa_3
8
task_spa_3_task


select concat('[',group_concat(b.batch_id),']') as batch_id 
from space_id s 
INNER JOIN batch_ids b ON b.request_id LIKE CONCAT('%',s.id,'%');


batch_id


[1,2,3,4,5,7,8]

id	batch_id	request_id
spa_1	1	task_spa_1_task
spa_2	2	task_spa_2_task
spa_3	3	task_spa_3_task
spa_4	4	task_spa_4_task
spa_5	5	task_spa_5_task
spa_2	7	task_spa_2_task
spa_3	8	task_spa_3_task