{"id":6386,"date":"2019-02-17T19:35:55","date_gmt":"2019-02-18T03:35:55","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=6386"},"modified":"2025-06-13T17:22:10","modified_gmt":"2025-06-14T00:22:10","slug":"n1ql-query-with-self-referencing-hierarchy","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/n1ql-query-with-self-referencing-hierarchy\/","title":{"rendered":"N1QL query with Self Referencing Hierarchy"},"content":{"rendered":"

A data construct that often appears in business application is the hierarchical data structure. Hierarchy captures the parent-child relationship often between the same object. For instance a company structure captures the reporting line between employees. Business organization captures the relationship between parent companies and subsidiaries. Territory hierarchies in Sales. Book of accounts in financial applications.<\/p>\n

Due the self referencing nature of hierarchy, querying the structure efficiently along with its associated data can be a challenge for RDBMS, particularly from a performance perspective. In this article, I will discuss how traditional RDBMS handles hierarchical queries. The challenges that it has to deal with, and how this issue can be similarly addressed with Couchbase N1QL and Couchbase GSI.<\/p>\n

Hierarchy Structure in Applications<\/h2>\n

The main reason for gathering information in a hierarchical structure is to improve the understanding of the information. Company reporting structure is designed, not only to help with how the organization is managed, but also provide a structure to measure and optimize the effectiveness of each group. In a large organization, sales performance is often assessed, not only at the individual level, but at sales team. In short, businesses organize information in a hierarchical structure so that they can get a better understanding of the business performance. In order to achieve that goal, business needs an efficient means to query the hierarchical data.<\/p>\n

Representation of company hierarchy<\/h3>\n

\"\"<\/p>\n

While database data model is capable of efficiently capturing the hierarchical structure, the difficulty arises when you need to query the hierarchical data, and the related information.<\/p>\n

Consider this requirement:\u00a0<\/strong><\/span>Get a total sales order value for all sales reps who report up to ThomasH-Sales VP<\/strong><\/p>\n

\"\"<\/p>\n

While the data model is relatively simple. The hierarchical nature of the sales organization suggests an inherent dynamic structure in the reporting hierarchy. AjayW, who heads the Region1 territory, also manages the sales team members directly. Whereas, in Region2, Liz L manages two Managers, who then manages the sales team. This is typical in most application data hierarchies.<\/p>\n

RDMBS approach<\/h4>\n

In order to query hierarchical data, more established RDBMS such as Oracle and SQLSever provide the CONNECT BY \/ START WITH construct to allow for a single query to recursively traverse the hierarchy employee structure.<\/p>\n

Query: Sales orders generated by members who report up to ThomasH-Sales VP<\/strong><\/span>\r\n\r\nSELECT e.EmpID, e.Name, e.ManagerID, sum(o.orderVal)\r\n   FROM employee e\r\n   INNER JOIN sales_order o ON o.EmpID = o.EmpId\r\n   START WITH EmpID = 101\r\n   CONNECT BY PRIOR EmpID = ManagerID\r\n   GROUP BY  e.EmpID, e.Name, e.ManagerID;\r\n\r\n<\/pre>\n
While the above query may look simple, the query performance is difficult to improve with indexes due to the recursive nature of the CONNECT BY implementation. For this reason, this technique is not popular in application development for systems with large volume of data. Instead, enterprise applications rely on a pre-flattened object structure for a more predictable query performance. The flattened hierarchy technique is described in the Couchbase N1QL section below.<\/p>\n
Couchbase N1QL<\/h4>\n
In order to get the best query performance, N1QL applications should use the flattened hierarchy structure. The approach\u00a0 provides more predictable performance as well as better at leveraging Couchbase GSI. The diagram below shows an example of the flattening transformation of a self-referencing hierarchical structure, such as the employee object. I also include a python code snippet below that you can use to flatten the hierarchical structure.\u00a0 The flatten_hierarchy<\/em><\/strong> function takes a self referencing hierarchical JSON object and generates two new objects in the same keyspace but with different type values<\/p>\n
\"\"<\/p>\n