{"id":13258,"date":"2022-05-26T12:05:50","date_gmt":"2022-05-26T19:05:50","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=13258"},"modified":"2025-06-13T23:34:35","modified_gmt":"2025-06-14T06:34:35","slug":"traverse-hierarchy-user-defined-functions-in-7-1","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/ko\/traverse-hierarchy-user-defined-functions-in-7-1\/","title":{"rendered":"SQL++ \uc0ac\uc6a9\uc790 \uc815\uc758 \ud568\uc218(UDF)\ub97c \uc0ac\uc6a9\ud55c \ud2b8\ub798\ubc84\uc2a4 \uacc4\uce35 \uad6c\uc870"},"content":{"rendered":"

User-defined functions (UDFs) are a feature that exist in most RDBMS. Whether it is Oracle PL\/SQL (Procedural Language for SQL), SQL Server T-SQL (Transact-SQL), PL\/pgSQL (Procedural Language\/PostgreSQL), or other variants, all these languages have the general characteristics of providing<\/span> a block structure, condition control, iteration loop, and error handling. These building blocks allow the development of complex tasks, which can be isolated to improve maintenance and application integrity.<\/span><\/p>\n

For Couchbase, the criteria in choosing the language for SQL++<\/a> User Defined Functions is quite clear\u2013 they must be able to support all the capabilities that exist in the current RDBMS implementations or to the same level that Couchbase Query service can, and also reflect the preference of today’s developers. According to Stack Overflow developer surveys, the most commonly used programming language in the world as of 2020 is <\/span>JavaScript<\/b>.<\/span><\/p>\n

SQL++ UDF\/JS<\/span><\/h2>\n

Couchbase is a document database that natively stores its data in JSON format (<\/span>J<\/b>ava<\/span>S<\/b>cript <\/span>O<\/b>bject <\/span>N<\/b>otation). Its query language, SQL++ , is SQL for JSON<\/em>. The JavaScript language is therefore the most natural way to access and manipulate JSON data.<\/span><\/p>\n

Please refer to Couchbase documentation for more details on the full implementation of JavaScript in Couchbase<\/a>.<\/span><\/p>\n

The JavaScript UDF to traverse trees<\/span><\/h3>\n

JavaScript is powerful, flexible, and relatively easy to start using. To show its versatility, I created a Javascript UDF for this article that traverses a tree structure, such as an organizational structure.<\/span><\/p>\n

UDF: traverseTree<\/em><\/p>\n

The UDF performs a recursive search on a collection using the two connect<\/em> fields (to and from) for the recursion.<\/span><\/p>\n

Parameters are shown in the following table:<\/span><\/p>\n\n\n\n\n\n\n\n\n\n
#<\/strong><\/td>\nName<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
1<\/span><\/td>\nkSpace<\/span><\/i><\/td>\nThe key space for the query. This could be a collection or query.<\/span><\/td>\n<\/tr>\n
2<\/span><\/td>\nstartWith<\/span><\/i><\/td>\nStart the search with this value for the connectToFld. If empty, the search will be performed for all connectTo values<\/span><\/td>\n<\/tr>\n
3<\/span><\/td>\nconnectTo<\/span><\/i><\/td>\nThe name of the field in the collection where the connectTo field will be used<\/span><\/td>\n<\/tr>\n
4<\/span><\/td>\nconnectFrom<\/span><\/i><\/td>\nThe name of the field in the collection where the connectFrom field will be used<\/span><\/td>\n<\/tr>\n
5<\/span><\/td>\nreportHier<\/span><\/i><\/td>\nThe field name for the hierarchy array<\/span><\/td>\n<\/tr>\n
6<\/span><\/td>\nlogKSpace<\/span><\/i><\/td>\nThe key space where optional logging for UDF is written to. Please note this parameter can only run when run with EXECUTE FUNCTION.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Examples of hierarchy<\/span><\/h4>\n

Let’s consider an organizational hierarchy structure as below.<\/span><\/p>\n

\"traverse<\/p>\n

The <\/span>emp<\/span><\/em> collection has the following documents:<\/span><\/p>\n

[\r\n\u00a0 { \"empid\": 1, \"name\": \"JeffC\"\u00a0 \u00a0 },\r\n\u00a0 { \"empid\": 2, \"name\": \"SteveA\",\u00a0 \"reportsTo\": \"JeffC\" \u00a0 },\r\n\u00a0 { \"empid\": 3, \"name\": \"AmitG\", \u00a0 \"reportsTo\": \"JeffC\" \u00a0 },\r\n\u00a0 { \"empid\": 4, \"name\": \"BrendaM\", \"reportsTo\": \"SteveA\"\u00a0 },\r\n\u00a0 { \"empid\": 5, \"name\": \"WillG\", \u00a0 \"reportsTo\": \"SteveA\"\u00a0 },\r\n\u00a0 { \"empid\": 6, \"name\": \"PaulD\", \u00a0 \"reportsTo\": \"BrendaM\" }\r\n]<\/pre>\n
SELECT e.* FROM traverseTree('traversal.hierarchy.emp','','name','reportsTo','ReportHierarchy','') e;<\/pre>\n
The query produces the following results:<\/p>\n
[\r\n\u00a0 {\r\n\u00a0 \u00a0 \"ReportHierarchy\": [],\r\n\u00a0 \u00a0\"name\": \"JeffC\"\r\n\u00a0 },\r\n\u00a0 {\r\n\u00a0 \u00a0 \"ReportHierarchy\": [ { \"level\": 1,\"name\": \"JeffC\" } ],\r\n\u00a0 \u00a0\"name\": \"SteveA\",\r\n\u00a0 \u00a0 \"reportsTo\": \"JeffC\"\r\n\u00a0 },\r\n\u00a0 {\r\n\u00a0 \u00a0 \"ReportHierarchy\": [ { \"level\": 1,\"name\": \"JeffC\" } ],\r\n\u00a0 \u00a0\"name\": \"AmitG\",\r\n\u00a0 \u00a0 \"reportsTo\": \"JeffC\"\r\n\u00a0 },\r\n\u00a0 {\r\n\u00a0 \u00a0 \"ReportHierarchy\": [ { \"level\": 1,\"name\": \"SteveA\",\"reportsTo\": \"JeffC\" },\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 { \"level\": 2,\"name\": \"JeffC\" }\u00a0 ],\r\n\u00a0 \u00a0\u00a0\"name\": \"BrendaM\",\r\n\u00a0 \u00a0 \"reportsTo\": \"SteveA\"\r\n\u00a0 },\r\n\u00a0 {\r\n\u00a0 \u00a0 \"ReportHierarchy\": [ { \"level\": 1,\"name\": \"SteveA\",\"reportsTo\": \"JeffC\" },\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 { \"level\": 2,\"name\": \"JeffC\" }\u00a0 ],\r\n\u00a0 \u00a0 \"name\": \"WillG\",\r\n\u00a0 \u00a0 \"reportsTo\": \"SteveA\"\r\n\u00a0 },\r\n\u00a0 {\r\n\u00a0 \u00a0 \"ReportHierarchy\": [ { \"level\": 1,\"name\": \"BrendaM\",\"reportsTo\": \"SteveA\" },\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 { \"level\": 2,\"name\": \"SteveA\", \"reportsTo\": \"JeffC\"\u00a0 },\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 { \"level\": 3,\"name\": \"JeffC\" }\u00a0 ],\r\n\u00a0 \u00a0\"name\": \"PaulD\",\r\n\u00a0 \u00a0 \"reportsTo\": \"BrendaM\"\r\n\u00a0 }\r\n]\r\n<\/pre>\n
Create the <\/span>traverseTree<\/span><\/em> user-defined\u00a0 function<\/span><\/h3>\n
In Couchbase SQL++, a UDF can be defined in a number of ways.\u00a0<\/span><\/p>\n
UDF as a scalar function<\/h5>\n
CREATE FUNCTION to_meters(...) { args[0] * 0.3048 };\r\n\r\nSELECT airportname, ROUND(to_meters(geo.alt)) AS mamsl\r\nFROM `travel-sample`.inventory.airport\r\nLIMIT 5;<\/pre>\n
 <\/p>\n
As an inline function with a subquery<\/h5>\n
CREATE FUNCTION locations(vActivity) { (\r\n\u00a0 SELECT id, name, address, city\r\n\u00a0 FROM `travel-sample`.inventory.landmark\r\n\u00a0 WHERE activity = vActivity) };\r\n\r\nSELECT l.name, l.city\r\nFROM locations(\"eat\") AS l\r\nWHERE l.city = \"Gillingham\";<\/pre>\n
 <\/p>\n
Defined as an external function<\/h5>\n
The traverseTree<\/em> UDF uses the external function mechanism. But in this case, we provide the external code library for the function. Refer to the Couchbase documentation for more details on External Function<\/a>.<\/span><\/p>\n
Here\u2019s how to create a JavaScript function library:<\/span><\/p>\n
1- Create the function library<\/b> – In this example, we use Query Workbench to create the JavaScript library.\u00a0<\/span><\/p>\n
\"Managing<\/p>\n
2 – Add and Edit the JavaScript code<\/b><\/p>\n
\"Editing<\/p>\n
Refer to this [<\/span>link<\/span><\/a>] for the complete traverseTree JavaScript library code.<\/span><\/p>\n
3 – Create the SQL++ user-defined function<\/b>\u00a0<\/span><\/p>\n
CREATE FUNCTION traverseTree(kSpace,startWith, connectTo,\u00a0\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0connectFrom,reportHier, debugKSpace)\r\n\u00a0 LANGUAGE JAVASCRIPT AS \"traverseTree\" AT \"tree\";<\/pre>\n
Note, you can also create the UDF using the Query Workbench.<\/p>\n
\"Add<\/p>\n
Important notes<\/span><\/h3>\n
Couchbase 7.0 added SQL++ UDFs for JavaScript. To Couchbase 7.1 we added the ability to execute SQL++ DMLs from within the JavaScript code. There are also several Query Workbench UI enhancements for UDF management.<\/span><\/p>\n
Disclaimer <\/b>– Please note that the JavaScript code provided in this article is not part of the Couchase product. It is provided here only to illustrate the capabilities of SQL++ UDF using JavaScript. Users are encouraged to verify its correctness, and to make modifications to suit\u00a0 their needs.<\/span><\/p>\n
Continue learning<\/h3>\n