N1QL query with second level nesting

SQL++
11

Ok. You can do:

SELECT s.orderNumber, ARRAY_AGG(s.prod) AS products
FROM
(
    SELECT orders.orderNumber, { "productName": p.displayName, "productType": p.type, "parts": ARRAY_AGG(productPart) } AS prod
    FROM testBucket orders
    JOIN testBucket orderItem ON KEY orderItem.`order` FOR orders
    JOIN testBucket product p ON KEYS orderItem.product
    JOIN testBucket productPart ON KEY productPart.product FOR p
    WHERE orders.orderNumber = "XYZ"
    GROUP BY orders.orderNumber, p
) AS s
GROUP BY s.orderNumber;
1 Like