SQLize Online / PHPize Online  /  SQLtest Online

create table toonart_category ( id int, name varchar(64) ); insert into toonart_category values (1, 'Category1'),(2, 'Category2'); create table toonart_image ( id int, cat_id int, name varchar(64), price int ); insert into toonart_image values (1, 1, 'Prod1', 10),(2, 1, 'Prod2', 12), (3, 2, 'Prod3', 10); SELECT cat.id, cat.name, JSON_ARRAYAGG( JSON_OBJECT('pro_name',pro.name,'pro_pice',pro.price) ) prod FROM products as pro JOIN categories as cat on pro.cat_id = cat.id GROUP BY cat.id, cat.name ORDER BY cat.id;