MYSQL query to get array list with each row having different children

Question

Lets say we have three tables

category_level_one

id	cat_name
1	Physics
2	Chemistry

category_level_two

id	sub_cat_name	cat_l1_id
1	Organic	2
2	Inorganic	2
3	Physical	2
4	Mechanics	1
5	Electricity and Magnetism	1

category_level_three

id	sub_cat_name	cat_l1_id	cat_l2_id
1	Atoms and Molecules	2	3
2	Chemical Equilibrium	2	3
3	Alcohols	2	1
4	Ethers	2	1
5	P Block Element	2	2
6	Kinematics	1	4
7	Work Power and Energy	1	4
9	Current Electricity	1	5

SELECT category_level_two.id, category_level_two.sub_cat_name, 
JSON_OBJECT("id" , category_level_one.id, 'name', category_level_one.cat_name ) AS "categotyOne", 
(select json_arrayagg(JSON_OBJECT(
                                'id', category_level_three.id,
                                'name', category_level_three.sub_sub_cat_name))
                            from category_level_two
                            INNER JOIN category_level_three  
                            ON category_level_three.cat_l2_id = category_level_two.id
                            WHERE category_level_two.id = 1)  as "categotyThree",
FROM category_level_two
JOIN category_level_one 
ON category_level_two.cat_l1_id = category_level_one.id  
WHERE category_level_two.id WHERE = 1

It will give data as

id	name	categotyOne	categotyThree
1	Organic	{ "id" : 1 , "name" : "Chemistry" }	[ {...} , {...}]

How can I get the multiple unique rows for category_l2 with each categotyThree list that belongs to that catl2 ?

score 0 · Answer 1 · answered Aug 25 '21 at 16:20

SELECT JSON_OBJECT('data', JSON_ARRAYAGG(output)) results
FROM (
    SELECT JSON_OBJECT(
        'name', p2.sub_cat_name, 
        'cat_l2_id', p2.id,
        'cat_l1_id', p1.id,
        'namel1' , p1.cat_name,
        'cat1', JSON_OBJECT("id" , p1.id, 'name', p1.cat_name ), 
        'catl2', JSON_ARRAYAGG(
            JSON_OBJECT(
                'cat_l2_id', p2.id, 
                'name', p3.sub_sub_cat_name, 
                'cat_l3_id', p3.id
            )
        )
    ) output,
    p1.id as 'cat1',
    p2.id as 'cat2',
    p3.id as 'cat3' 
    FROM category_level_two  p2
    JOIN category_level_three p3
    ON p2.id = p3.cat_l2_id  
    JOIN category_level_one p1
    ON p2.cat_l1_id = p1.id  
    WHERE p2.id IN (2,4)
    GROUP BY p2.id
) x

MYSQL query to get array list with each row having different children

1 Answers1