Krdytkyu

I'm learning SQL in the middle of an internship I'm doing and just wrote my first (at least what I would call) complex query. The query has to return information from a relational DB about all product catalogs from a company and the products that they contain in a json format, which can reference products through a simple many to many relationship of:

[catalogs] <-> [products]

AND/OR through

[catalogs] <-> [product groups] <-> [products]

I'm new to SQL and using MySQL 5.7.14. I don't know if this is particularly optimized or at all good practice so those are my biggest concerns with my query.
There might also be some redundant JOINs because I'm not entirely comfortable with the scoping of subqueries.

SELECT

    cat.*,

    CONCAT('[',

        GROUP_CONCAT(CONCAT(

            '{"id":',                        p.id,

            ', "name":"',                IFNULL(p.name, ''),

            '", "image_url":"',        IFNULL(p.image_url, ''),

            '", "description":"',    IFNULL(p.description, ''),

            '"}')

        ORDER BY p.id),

    ']') AS products,

    CONCAT('[',

        GROUP_CONCAT(DISTINCT CONCAT(

            '{"id":',                        pg.id,

            ', "name":"',                IFNULL(pg.name, ''),

            '", "products": ',        IFNULL(pg.product_group_product_list, ''),

            '}')

        ORDER BY pg.id),

    ']') AS product_groups

FROM

    companies AS c

        LEFT JOIN

    catalogs AS cat ON cat.company_id = c.id

        LEFT JOIN

    catalog_products AS cpjoin ON cpjoin.catalog_id = cat.id

        LEFT JOIN

    products AS p ON p.id = cpjoin.product_id

        LEFT JOIN

    catalog_product_groups AS cpgjoin ON cpgjoin.catalog_id = cat.id

        LEFT JOIN (

            SELECT

                pg2.*,

                CONCAT('[',

                    GROUP_CONCAT(DISTINCT CONCAT(

                        '{"id":',                        p.id,

                        ', "name":"',                IFNULL(p.name, ''),

                        '", "image_url":"',        IFNULL(p.image_url, ''),

                        '", "description":"',    IFNULL(p.description, ''),

                        '"}')

                    ORDER BY p.id),

                ']') AS product_group_product_list

            FROM

                companies AS c2

                    LEFT JOIN

                catalogs AS cat2 ON cat2.company_id = c2.id

                    LEFT JOIN

                catalog_product_groups AS cpgjoin2 ON cpgjoin2.catalog_id = cat2.id

                    LEFT JOIN

                product_groups AS pg2 ON pg2.id = cpgjoin2.product_group_id

                    LEFT JOIN

                product_group_products AS pgjoin2 ON pgjoin2.product_group_id = pg2.id

                    LEFT JOIN

                products p ON p.id = pgjoin2.product_id

            GROUP BY

                pg2.id

            ) AS pg ON pg.id = cpgjoin.product_group_id

WHERE

    c.id  = 1

GROUP BY cat.id;