couchbase n1ql simple inner join using non-key elements

Question

I have a couchbase bucket named 'test' which has 2 documents. The key for those docs are hash value of their respective creation epoch time.

Doc 1:

Key - 3ddd743fd229063273fc1db076e0c81e7b5077909abcba51088febf119c3233c

value -

{
  "empId": 1,
  "e_name": "abc",
  "type": "m",
  "managerId": null
}

Doc 2:

key - 33d57d98b68a270ac972b1f392d21d435bbdb1f5cf5a7bbabb1bfacb1215eaac

value -

{
  "empId": 2,
  "e_name": "def",
  "type": "r",
  "managerId": 1
}

I'm trying to perform an inner join so that after the join the query would return something like

{
    "empName": "def",
    "managerName": "abc"
}

I'm not able to perform the inner join here in this case because the join isn't on the key. My question is how do I achieve this.

Matthew Groves Matthew Groves · Accepted Answer · 2018-04-20T13:47:35

Until Couchbase Server 5.5, the only join you can do in N1QL has to have the document key on one side of the join.

With Couchbase Server 5.5 (currently in beta), you can use an "ANSI join" to join on any field in a document (more information in this blog post: New Querying Features in Couchbase Server 5.5) but essentially, you could write a join in the same way you do in relational databases. It's not clear to me on your sample which fields you actually want to join on (managerId I guess?), but the syntax would look like this:

SELECT emp.*, man.*
FROM test emp
INNER JOIN test man ON emp.managerId = man.managerId

If you aren't ready to use 5.5 yet, then there might be an alternate way to structure your data to do this kind of join, or maybe think about an alternate approach (what you're doing here seems very relational, when it might not need to be).

Since it looks like 'manager' is a type of 'employee', then maybe a manager document could contain an array of document IDs for the employees that manager is managing. Then you can join that array to the employee document keys. E.g.:

{
  "empId": 1,
  "e_name": "abc",
  "type": "m",
  "managerId": null,
  "employeesOfThisManager" : [
     "33d57d98b68a270ac972b1f392d21d435bbdb1f5cf5a7bbabb1bfacb1215eaac",
     "33d57d98b68a270ac972b1f392d21d435bbdb1f5cf5a7bbabb1foobarbaz1234"
  ]
}

couchbase n1ql simple inner join using non-key elements

1 Answers