How to map array values in one document to another and display in result

SQL++
1

Hi, I am new to Couchbase and have been trying this for several hours. Have looked through the documentation but haven’t been able to locate this specific scenario,
My bucket has documents:
{
“type”:“order”,
“order_id”:“1”,
“products”:[{“product_id”:“1”,“qty”:10},{“product_id”:“2”,“qty”:20}]
},
{
“type”:“product”,
“id”:“1”,
“name”:“Product one”
},
{
“type”:“product”,
“id”:“2”,
“name”:“Product two”
}

The product_ids in the “order” type document map to the id field in the “product” type document.
I just need to enhance the order document and get the result as below:
{
“type”:“order”,
“order_id”:“1”,
“products”:[{“product_id”:“1”,“qty”:10,“product_name”:“Product one”},{“product_id”:“2”,“qty”:20,“Product two”}]
}
What N1QL should I use?

Thanks in advance.

2

Checkout ANSI JOIN Support in N1QL | The Couchbase Blog

CREATE INDEX ix1 ON Orders(order_id) WHERE type = "order";
CREATE INDEX ix2 ON Orders(id, name) WHERE type = "product";

SELECT o.*, ARRAY_AGG(OBJECT_ADD(op,"name",p.name)) AS products
FROM Orders AS o
LEFT UNNEST o.products AS op
LEFT JOIN Orders AS p ON p.type = "product" AND op.product_id = p.id
WHERE o.type = "order" AND o.order_id IS NOT NULL
GROUP BY o;
3

Hi vsr1,

thank you very much for this explanation as it helped me fix a problem I have been wrestling with for a couple of days. It is crazy how powerful N1QL can be.
One further clarification though: how would you do the same thing if the array one wants to add a property to is inside another array?
Leveraging the example above, what if I change it to this:

{
  “type”:“order”,
  “order_id”:“1”,
  “products”:[
    {“product_id”:“1”,“qty”:10, "ingredients":[{"ingid": "1"},{"ingid":"2"}]},
    {“product_id”:“2”,“qty”:20, "ingredients":[{"ingid": "3"},{"ingid":"4"}]}
  ]
},
{
  “type”:“product”,
  “id”:“1”,
  “name”:“Product one”
},
{
  “type”:“product”,
  “id”:“2”,
  “name”:“Product two"
},
{
  "type" : "ingredient",
  "ingid": "1",
  "name": "flour"
},
{
  "type" : "ingredient",
  "ingid": "2",
  "name": "water"
},
{
  "type" : "ingredient",
  "ingid": "3",
  "name": "milk"
},
{
  "type" : "ingredient",
  "ingid": "4",
  "name": "butter"
},

and this is the result I want:

{
  “type”:“order”,
  “order_id”:“1”,
  “products”:[
    {
      “product_id”:“1”,
      “qty”:10,
      “product_name”:“Product one”, 
      "ingredients":[
        {"ingid":"1", "ing_name":"flour"}, {"ingid":"2", "ing_name":"water"}
      ]
    },
    {
      “product_id”:“2”,
      “qty”:20,
      "product_name": “Product two”, 
      "ingredients":[
        {"ingid":"3", "ing_name":"milk"}, {"ingid":"4", "ing_name":"butter"}
      ]
    }
  ]
}

I am sorry for the trivial modifications to the example, but this is all I could come up with :slight_smile: , but I basically want to add the ingredient’s name in the nested array.

How should I change the query?
Thanks a lot,
Bertrand.

4

Is it possible post the document key of product, ingredient so that if there is match for id there is better way to do that

CREATE INDEX ix1 ON Orders(order_id) WHERE type = "order";
CREATE INDEX ix2 ON Orders(id, name) WHERE type = "product";
CREATE INDEX ix3 ON Orders(ingid, name) WHERE type = "ingredient";

SELECT d.o.*, ARRAY_AGG(OBJECT_CONCAT(d.op, {d.name, d.ingredients})) AS products 
FROM (
       SELECT o,  op, p.name, ARRAY_AGG(OBJECT_ADD(ui,"name",i.name)) AS ingredients
       FROM Orders AS o 
       LEFT UNNEST o.products AS op
      LEFT JOIN Orders AS p ON p.type = "product" AND op.product_id = p.id
      LEFT UNNEST op.ingredients AS ui
     LEFT JOIN Orders AS i ON i.type = "ingredient" AND ui.ingid = i.ingid
     WHERE o.type = "order" AND o.order_id IS NOT NULL
     GROUP BY o, op ) AS d
GROUP BY d.o;

if document keys matches id field

SELECT o.*,
       (SELECT op.*, name,
               (SELECT oi.*, name
                FROM op.ingredients AS oi
                LET name = (SELECT RAW i.name FROM Orders AS i USE KEYS oi.ingid WHERE i.type = "ingredient")[0]
               ) AS ingredients
        FROM o.products AS op
        LET name = (SELECT RAW p.name FROM Orders AS p USE KEYS op.product_id WHERE p.type = "product")[0]
       ) AS products
FROM Orders AS o
WHERE o.type = "order" AND o.order_id IS NOT NULL
5

Hi vsr1,

thank you very much for the really quick turn-around. It will take me a little bit of time to digest the query though . :slight_smile: