MySQL 8.0

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

create table A (id int, json_field JSON);

create table B (a_id int, value int);

insert into A values(1, null),(2, null);

insert into B values (1,100),(1,101),(2,200),(2,201);

Records: 2  Duplicates: 0  Warnings: 0



Records: 4  Duplicates: 0  Warnings: 0


select a_id, json_arrayagg(value) as json_field
from B
group by a_id


a_id
json_field


1
[100, 101]

2
[200, 201]


update A
set A.json_field =
(
  select json_arrayagg(value) 
  from B
  where A.id = B.a_id
) 

Rows matched: 2  Changed: 2  Warnings: 0


select id, json_pretty(json_field) as json_field from A


id
json_field


1
[
  100,
  101
]

2
[
  200,
  201
]