SQLize Online / PHPize Online  /  SQLtest Online

with combine_trees as ( with make_tree as ( WITH RECURSIVE category_path AS ( SELECT id, title, parent_id FROM category WHERE parent_id IS NULL UNION ALL SELECT c.id, c.title, c.parent_id FROM category_path AS cp JOIN category AS c ON cp.id = c.parent_id ) SELECT cp.title, cp.id, if(cp.id = category.id, json_arrayagg(json_object('item_name', it.name)), json_object(cp.title, json_object('items',json_arrayagg(json_array(json_object('item_name', it.name)))))) as tree FROM category_path cp INNER JOIN items it ON it.cat_id = cp.id join category on category.id = ifnull(cp.parent_id, cp.id) group by cp.title, cp.id, category.id ) select json_arrayagg(json_object(title, json_array('items', tree))) output_json from make_tree group by id ) select json_object('menu',group_concat(output_json)) as output from combine_trees; SET FOREIGN_KEY_CHECKS = 1;