CODEWITHSUNDEEP
mysql
Sajad
Sajad

Reputation: 3827

Achieving nested group_concat

I have this query:

select  p.id,
        p.price,
        concat('[',group_concat(concat('{',concat_ws(',', concat('color:', pc.color),concat('sizeName:',cs.size_name)),'}')),']') as variety

        from tbl_products as p

    inner join tbl_product_colors as pc on p.id = pc.product_id
    left join tbl_color_sizes as cs on pc.id = cs.product_color_id

group by p.id;

Query above outputs:

Id | price  | variety
1  | 1000  | [ {color:FFFFFF, sizeName:XL} , {color:FFFFFF, sizeName:X} ]

But my desired output is:

Id | price  | variety
1  | 1000  | [ {color:FFFFFF, sizes: [ {sizeName: XL} , {sizeName: L} ] } ]

Question: How can I use group_concat in this context or any other function (besides JSON function) to achieve desired output?

Upvotes: 0

Views: 493

Answers (1)

Uueerdo
Uueerdo

Reputation: 15961

In almost all cases results needing nest aggregation require "nested" queries. You would do the "size list" in a subquery grouped on the outer grouping dimensions AND the field(s) to be grouped together in the outer query; and then in the outer query aggregate the field(s) with the "size list" concatenated to them.

Something like this, but I omitted most of the extra CONCATs for readability.

SELECT subQ.id, subQ.price
   , GROUP_CONCAT(CONCAT(subQ.color, ': ', subQ.sizeList)) AS variety
FROM (
   SELECT p.id, p.price, pc.color
      , GROUP_CONCAT(cs.size_name) AS sizeList
   FROM tbl_products AS p
   INNER JOIN tbl_product_colors AS pc ON p.id = pc.product_id
   LEFT JOIN tbl_color_sizes AS cs ON pc.id = cs.product_color_id
   GROUP BY p.id, p.price, pc.color
) AS subQ
GROUP BY subQ.id, subQ.price
;

Upvotes: 3

Related Questions