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.


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.

traverse hierarchical data example

The emp collection has the following documents:

The query produces the following results:

Create the traverseTree user-defined  function

In Couchbase SQL++, a UDF can be defined in a number of ways. 

UDF as a scalar function


As an inline function with a subquery


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. 

Managing JavaScript function libraries

2 – Add and Edit the JavaScript code

Editing Couchbase JavaScript code

Refer to this [link] for the complete traverseTree JavaScript library code.

3 – Create the SQL++ user-defined function 

Note, you can also create the UDF using the Query Workbench.

Add UDF function in SQL++

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.

Continue learning


Posted by Binh Le

Binh Le is a Principal Product Manager for Couchbase Query service. Prior to Couchbase, he worked at Oracle and led the product management team for Sales Clould Analytics and CRM OnDemand. Binh holds a Bachelor's Degree in Computer Science from the University of Brighton, UK.

Leave a reply