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 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.
For Couchbase, the criteria in choosing the language for SQL++ User Defined Functions is quite clear– 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 JavaScript.
SQL++ UDF/JS
Couchbase is a document database that natively stores its data in JSON format (JavaScript Object Notation). Its query language, SQL++ , is SQL for JSON. The JavaScript language is therefore the most natural way to access and manipulate JSON data.
Please refer to Couchbase documentation for more details on the full implementation of JavaScript in Couchbase.
The JavaScript UDF to traverse trees
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.
UDF: traverseTree
The UDF performs a recursive search on a collection using the two connect fields (to and from) for the recursion.
Parameters are shown in the following table:
| # | Name | Description |
| 1 | kSpace | The key space for the query. This could be a collection or query. |
| 2 | startWith | Start the search with this value for the connectToFld. If empty, the search will be performed for all connectTo values |
| 3 | connectTo | The name of the field in the collection where the connectTo field will be used |
| 4 | connectFrom | The name of the field in the collection where the connectFrom field will be used |
| 5 | reportHier | The field name for the hierarchy array |
| 6 | logKSpace | The key space where optional logging for UDF is written to. Please note this parameter can only run when run with EXECUTE FUNCTION. |
Examples of hierarchy
Let’s consider an organizational hierarchy structure as below.
The emp collection has the following documents:
|
1 2 3 4 5 6 7 8 |
[  { "empid": 1, "name": "JeffC"  },  { "empid": 2, "name": "SteveA", "reportsTo": "JeffC"  },  { "empid": 3, "name": "AmitG",  "reportsTo": "JeffC"  },  { "empid": 4, "name": "BrendaM", "reportsTo": "SteveA" },  { "empid": 5, "name": "WillG",  "reportsTo": "SteveA" },  { "empid": 6, "name": "PaulD",  "reportsTo": "BrendaM" } ] |
|
1 |
SELECT e.* FROM traverseTree('traversal.hierarchy.emp','','name','reportsTo','ReportHierarchy','') e; |
The query produces the following results:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
[ Â { Â Â "ReportHierarchy": [], Â Â "name": "JeffC" Â }, Â { Â Â "ReportHierarchy": [ { "level": 1,"name": "JeffC" } ], Â Â "name": "SteveA", Â Â "reportsTo": "JeffC" Â }, Â { Â Â "ReportHierarchy": [ { "level": 1,"name": "JeffC" } ], Â Â "name": "AmitG", Â Â "reportsTo": "JeffC" Â }, Â { Â Â "ReportHierarchy": [ { "level": 1,"name": "SteveA","reportsTo": "JeffC" }, Â Â Â Â Â Â Â Â Â Â Â Â { "level": 2,"name": "JeffC" }Â ], Â Â Â "name": "BrendaM", Â Â "reportsTo": "SteveA" Â }, Â { Â Â "ReportHierarchy": [ { "level": 1,"name": "SteveA","reportsTo": "JeffC" }, Â Â Â Â Â Â Â Â Â Â Â Â { "level": 2,"name": "JeffC" }Â ], Â Â "name": "WillG", Â Â "reportsTo": "SteveA" Â }, Â { Â Â "ReportHierarchy": [ { "level": 1,"name": "BrendaM","reportsTo": "SteveA" }, Â Â Â Â Â Â Â Â Â Â Â Â { "level": 2,"name": "SteveA", "reportsTo": "JeffC"Â }, Â Â Â Â Â Â Â Â Â Â Â Â { "level": 3,"name": "JeffC" }Â ], Â Â "name": "PaulD", Â Â "reportsTo": "BrendaM" Â } ] |
Create the traverseTree user-defined function
In Couchbase SQL++, a UDF can be defined in a number of ways.Â
UDF as a scalar function
|
1 2 3 4 5 |
CREATE FUNCTION to_meters(...) { args[0] * 0.3048 }; SELECT airportname, ROUND(to_meters(geo.alt)) AS mamsl FROM `travel-sample`.inventory.airport LIMIT 5; |
As an inline function with a subquery
|
1 2 3 4 5 6 7 8 |
CREATE FUNCTION locations(vActivity) { (  SELECT id, name, address, city  FROM `travel-sample`.inventory.landmark  WHERE activity = vActivity) }; SELECT l.name, l.city FROM locations("eat") AS l WHERE l.city = "Gillingham"; |
Defined as an external function
The traverseTree 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.
Here’s how to create a JavaScript function library:
1- Create the function library – In this example, we use Query Workbench to create the JavaScript library.Â
2 – Add and Edit the JavaScript code
Refer to this [link] for the complete traverseTree JavaScript library code.
3 – Create the SQL++ user-defined functionÂ
|
1 2 3 |
CREATE FUNCTION traverseTree(kSpace,startWith, connectTo,                               connectFrom,reportHier, debugKSpace)  LANGUAGE JAVASCRIPT AS "traverseTree" AT "tree"; |
Note, you can also create the UDF using the Query Workbench.
Important notes
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.
Disclaimer – 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 their needs.
