Grouping similar key values to an array in N1Ql couchbase query

Question

[
    {
        "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 the above in my bucket.

How can I query (like below) grouping the products from the same brand, with the count?

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

Answer 1

Use GROUP BY and ARRAY_AGG

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