How to write Nest of Nest query

SQL++
1

Hi,
I am confused with all the possible options (NEST, JOIN, ARRAY_AGG) offered by N1QL.
Should be a very common use case but now I cannot figure out which way to go. I could only do one level of nesting using NEST and ON KEY Thank you for your help.

Here is my data, all in one bucket:

{'type':'company', 'name':'company A'}, {'type':'company', 'name':'company B'}, ... {'type':'department', 'name':'department 1', 'company_id':'company_A_id'}, {'type':'department', 'name':'department 2', 'company_id':'company_A_id'}, ... {'type':'employee', 'name':'employee X', 'department_id':'department_1_id'}, {'type':'employee', 'name':'employee Y', 'department_id':'department_1_id'}, {'type':'employee', 'name':'employee Z', 'department_id':'department_2_id'}

Output that I need:

[
{
    'name':'company A',
    'departments':
    [ 
        {
                'name':'department 1',
                "employees":
                [
                    {
                        'name': 'employee X'
                    },
                    {
                        "name": "employee Y"
                    }
                ]
        },
        {
                "name":"department 2",
                "employees":
                [
                    {
                        "name": "employee Z"
                    }
                ]
        }
    ]
},...
]
2 
INSERT INTO default VALUES ('company_A_id',{'type':'company', 'name':'company A'});
INSERT INTO default VALUES ('department_1_id',{'type':'department', 'name':'department 1', 'company_id':'company_A_id'});
INSERT INTO default VALUES ('department_2_id',{'type':'department', 'name':'department 2', 'company_id':'company_A_id'});
INSERT INTO default VALUES ('employee_1_id',{'type':'employee', 'name':'employee X', 'department_id':'department_1_id'});
INSERT INTO default VALUES ('employee_2_id',{'type':'em[ANSI JOIN query CB 5.50](https://blog.couchbase.com/ansi-join-support-n1ql/)ployee', 'name':'employee Y', 'department_id':'department_1_id'});
INSERT INTO default VALUES ('employee_3_id',{'type':'employee', 'name':'employee Z', 'department_id':'department_2_id'});

ANSI JOIN CB 5.50

SELECT c.name, ARRAY_AGG({t.name, t.employees}) AS departments
FROM ( SELECT d.name, d.company_id, ARRAY_AGG({e.name}) AS employees
       FROM default AS e
       JOIN default AS d ON e.department_id = META(d).id
       WHERE e.type = "employee" AND d.type = "department"
       GROUP BY d) AS t
JOIN default AS c ON t.company_id = META(c).id
WHERE c.type = "company"
GROUP BY c;


CREATE INDEX dix1 ON default(company_id) WHERE type = "department";
CREATE INDEX eix1 ON default(department_id) WHERE type = "employee";

SELECT t.c.name, ARRAY_AGG({t.d.name, t.employees}) AS departments
FROM ( SELECT c, d, ARRAY_AGG({e.name}) AS employees
       FROM default AS c
       JOIN default AS d ON d.company_id = META(c).id AND d.type = "department"
       JOIN default AS e ON e.department_id = META(d).id AND e.type = "employee"
       WHERE c.type = "company"
       GROUP BY c, d) AS t
GROUP BY t.c;

LOOK UP Join query

SELECT c.name, ARRAY_AGG({t.name, t.employees}) AS departments
FROM ( SELECT d.name, d.company_id, ARRAY_AGG({e.name}) AS employees
       FROM default AS e
       JOIN default AS d ON KEYS e.department_id
       WHERE e.type = "employee" AND d.type = "department"
       GROUP BY d) AS t
JOIN default AS c ON KEYS t.company_id
WHERE c.type = "company"
GROUP BY c;

NOTE: The relation is through document key, First Level NEST will not keep the document key on right side it is not possible to do another level NEST it depends on second level nest document key.

1 Like
3

This is a good example of the flexibility of N1QL. NEST is an elegant way to get the result back with an array. But it does have some limitations when multi level NESTing is needed. As @vsr1 pointed out, JOIN+GROUP BY+ARRAY_AGG() provides more flexibility for the same goal.

4

UNNEST on ARRAY is self join of document and each element of ARRAY.
NEST is opposite of UNNEST

If you want create ARRAY on whole document on right side and project (Example 17 in ANSI JOIN link). NEST will be good.
If you want create ARRAY on partial document or want to include anything else. JOIN, GROUP BY, ARRAY_AGG() will be better.

5

Thank you so much for your fast respond, @vsr1, your code works great.

Moving from the SQL world (for 10+ years), I am still much impressed on how N1QL works on NoSQL data. I feel like riding a wild horse (N1QL), either you fall face down (bad) or it will get to where you want, FAST, even without having to build a road (data structure).

6

@binh.le: thank you for your insights.

7

@function1983, The following resources will help you with N1QL . cc @keshav_m