I have a normalized model with this data:
INSERT INTO testBucket (key, value) VALUES ("order1", {"documentType" : "order", "orderNumber" : "3434223"});
INSERT INTO testBucket (key, value) VALUES ("order2", {"documentType" : "order", "orderNumber" : "3434224"});
INSERT INTO testBucket (key, value) VALUES ("order3", {"documentType" : "order", "orderNumber" : "3434225"});
INSERT INTO testBucket (key, value) VALUES ("product1", {"documentType" : "product", "type" : "physical", "displayName" : "Server"});
INSERT INTO testBucket (key, value) VALUES ("productPart1", {"documentType" : "productPart", "name" : "Part 1", "product" : "product1"});
INSERT INTO testBucket (key, value) VALUES ("productPart2", {"documentType" : "productPart", "name" : "Part 2", "product" : "product1"});
INSERT INTO testBucket (key, value) VALUES ("product2", {"documentType" : "product", "type" : "service", "displayName" : "N1QL training"});
INSERT INTO testBucket (key, value) VALUES ("productPart3", {"documentType" : "productPart", "name" : "Part A", "product" : "product2"});
INSERT INTO testBucket (key, value) VALUES ("productPart4", {"documentType" : "productPart", "name" : "Part B", "product" : "product2"});
INSERT INTO testBucket (key, value) VALUES ("product3", {"documentType" : "product", "type" : "hybrid", "displayName" : "Remote training kit"});
INSERT INTO testBucket (key, value) VALUES ("productPart5", {"documentType" : "productPart", "name" : "Part I", "product" : "product3"});
INSERT INTO testBucket (key, value) VALUES ("productPart6", {"documentType" : "productPart", "name" : "Part II", "product" : "product3"});
INSERT INTO testBucket (key, value) VALUES ("product4", {"documentType" : "product", "type" : "physical", "displayName" : "Phablet"});
INSERT INTO testBucket (key, value) VALUES ("productPart7", {"documentType" : "productPart", "name" : "Part 1/2", "product" : "product4"});
INSERT INTO testBucket (key, value) VALUES ("productPart8", {"documentType" : "productPart", "name" : "Part 2/2", "product" : "product4"});
INSERT INTO testBucket (key, value) VALUES ("orderItem1", {"documentType" : "orderItem", "fulfillment" : "Online", "order" : "order1", "product" : "product1"});
INSERT INTO testBucket (key, value) VALUES ("orderItem2", {"documentType" : "orderItem", "fulfillment" : "Delivery", "order" : "order1", "product" : "product2"});
INSERT INTO testBucket (key, value) VALUES ("orderItem3", {"documentType" : "orderItem", "fulfillment" : "Pickup", "order" : "order1", "product" : "product3"});
INSERT INTO testBucket (key, value) VALUES ("orderItem4", {"documentType" : "orderItem", "fulfillment" : "Delivery", "order" : "order2", "product" : "product1"});
INSERT INTO testBucket (key, value) VALUES ("orderItem5", {"documentType" : "orderItem", "fulfillment" : "Pickup", "order" : "order3", "product" : "product4"});
The test model consists of orders, products, order items. Apart from this, each product has some parts. Everything is normalized. (Note: this is a fictional model, which I forced to reflect my actual relationships in a more general context).
My goal is to query the data and receive it denormalized. For example, being able to get a single order with its products (first complexity level) and each product with its parts (second complexity level).
The first complexity level was feasible to me with a query like this:
SELECT orders.orderNumber,
ARRAY_AGG( {
"productName": p.displayName,
"productType": p.type}) AS products
FROM testBucket orderItems
INNER JOIN testBucket orders
ON KEYS orderItems.`order`
INNER JOIN testBucket p
ON KEYS orderItems.product
WHERE orderItems.documentType = "orderItem"
GROUP BY orders.orderNumber
But my final goal is to retrieve each product with a “parts” property with an array of the names of its parts.
A second ARRAY_AGG
was my first choice, with no success. A subquery was next and then NEST
clause.
Any ideas on how to address this with a single query?