Couchbase N1QL Query - Include outer document as parent

Gurmeet Published at Dev

Gurmeet

I am new to Couchbase so need a little help in below n1ql query:

company:1
{
  'companyName':'A',
  'employees':[{
     'empId':101,
     'name':'Test',
     'deptId':'dept:1'
   },
   {
     'empId':102,
     'name':'Test2',
     'deptId':'dept:1'
   }]
}

dept:1
{
 'deptName':'Dept One',
 'location':'First Floor'
}

Output I need is like :

 company:1
    {
      'companyName':'A',
      'departments':[{
         'deptName':'Dept One',
         'location':'First Floor'
         'employees:[{
            'empId':101,
            'name':'Test',
            'deptId':'dept:1'
           },
           {
            'empId':102,
            'name':'Test2',
            'deptId':'dept:1'
           }]
       }],          
    }

I tried using Unnest and then using sub query but could not achieve the desired result. May be NEST can help but I dont have much idea, how to use NEST in n1ql.

Please help to achive the desired output.

vsr

First you unnest the employees and then group by company, department. Second you do group by company

SELECT d1.companyName, ARRAY_AGG(OBJECT_ADD(dep,"employees", d1.employees)) AS departments
FROM ( SELECT d.companyName, e.deptId, ARRAY_AGG(e) AS employees
       FROM default AS d
       UNNEST d.employees AS e
       WHERE d.type = "company"
       GROUP BY d.companyName, e.deptId) AS d1
LET dep = (SELECT RAW d3 FROM default AS d3 USE KEYS d1.deptId)[0]
GROUP BY d1.companyName;

Collected from the Internet

Please contact [email protected] to delete if infringement.