Include document id on Couchbase Nest right hand side array

user_560678 0
Viewed 170

I have a nest operation that joins an array of ids with some documents.

SELECT left.*,right FROM bucket AS left 
  LEFT NEST bucket AS right 
  ON META(right).id IN left.array

Result

[
  {
    array : ["rightId1","rightId2"],
    right : [ { < rightFields1 > }, { < rightFields2 > } ]
  }
]

I need the returned documents (< rightFields >) have the id on one of its fields, how can I do this?

n1ql couchbase
2 Answers

META(item).id only works in case item is bucket alias otherwise it returns MISSING.

First 2 are right approach in this case as you have right side document keys.

SELECT left.*, 
       (SELECT META(r).id, r.* 
        FROM bucket AS r USE KEYS left.array) AS right
FROM bucket AS left;

OR

SELECT left.*, right
FROM bucket AS left
LET right = (SELECT META(r).id, r.* 
             FROM bucket AS r USE KEYS left.array);

If you need really process right document and use them as array instead of NEST (for no processing) you should Use JOIN, GROUP BY and ARRAY_AGG()

SELECT left.*, 
       ARRAY_AGG(OBJECT_ADD(right,"id",META(right).id)) AS right
FROM bucket AS left
LEFT JOIN bucket AS right ON KEYS left.array
GROUP BY left;
user_60805360

I don't know if there is an easier solution, but I solved it by iterating each item of the array with ARRAY, and adding it a new property with OBEJECT_ADD

SELECT left.*,ARRAY OBJECT_ADD(item, "id", META(item).id) FOR item IN right END 
  FROM bucket AS left 
  LEFT NEST bucket AS right 
  ON META(right).id IN left.array
user_5606780
Related