Grouping similar key values to an array

SQL++
1

[
{
“brand”: “Apple”,
“product”: “iPhone 12”
},
{
“brand”: “Apple”,
“product”: “iPhone 11”
},
{
“brand”: “Samsung”,
“product”: “Note 10”
},
{
“brand”: “Samsung”,
“product”: “Note20”
}
]

I have a data set like this in my bucket
I want to return like below grouping the products from the same brand , with the count
Can someone help me with this ?

[
{
“brand”: “Apple”,
“products”: [{“product”: “iPhone 12”},{“product”: “iPhone 11”}],
“count_of_products”:2
},
{
“brand”: “Samsung”,
“products”: [{“product”: “Note10”},{“product”: “Note20”}],
“count_of_products”:2
}
]

2 
SELECT  brand, ARRAY_AGG({product}) AS products, COUNT(1) AS count_of_products
FROM mybucket
WHERE .....
GROUP BY brand;
1 Like