N1QL - It Makes Cents!

Robin Johnson
of
Couchbase
Published
October 10, 2013

*Ba Dum Tschhh* …See what I did there? Makes cents? Get it? Haha.

So… N1QL (pronounced Nickel)… Couchbase’s new next-generation query language; what is it? Well, it’s a rather genius designed, human readable / writable, extensible language designed for ad-hoc and operational querying within Couchbase. For those already familiar with querying within Couchbase, that blurb will probably make sense to you. If not – well, probably not, so let me clear it up a little more.

But before I do that, I must inform you that this blog article isn’t the best place for you to go if you want to dive in and get started learning N1QL. It is a view into N1QL from a developer’s perspective including why I am so excited about it, and the features I am proud to point out. If you want to get started learning about N1QL, click here. Or alternatively, go and have a go of the Online Tutorial. Anyway, back to clearing up what I mean when I say N1QL…

“N1QL is similar to the standard SQL language for relational databases, but also includes additional features; which are suited for document-oriented databases.” N1QL has been designed as an intuitive Query Language for use on databases structured around Documents instead of tables. To locate and utilise information in a document-oriented database, you need the correct logic and expressions for navigating documents and document structures. N1QL provides a clear, easy-to-understand abstraction layer to query and retrieve information in your document-database.

Before we move on with N1QL, let’s talk quickly about document modeling within Couchbase. As you probably know; within Couchbase we model our documents primarily in JSON. We’re all familiar with JSON, so I won’t go into it in detail, but one thing we need to bear in mind is the fact that: our JSON documents can have complex nested data structures, nested arrays and objects which ordinarily would make querying a problem. Contrary to SQL though, N1QL has the ability to navigate nested data because it supports the concept of paths. This is very cool. We can use paths by using a dot-notation syntax to give us the logical location of an attribute within a document. For example; if we had an e-commerce site with documents containing customers’ orders, we could look inside those documents, to an Nth nested level for attributes. So if we wanted to look for the customer’s shipping street:

orders.shipTo.street

Very cool, right? This is also one of the big differentiators that set this query language apart from SQL.

Like most of you reading this, I too come from a SQL background. I believe I am safe to assume that 99% of you do too, right? If you do, you will notice that N1QL not only looks familiar, but feels familiar too, which gives us an instantaneous sense of worth due to the fact we almost know the syntax without learning anything new.

Now let’s run some queries and see what happens! For these queries, I shall be running them against the sample ‘tutorial’ data bucket as seen on the online tutorial / Developer Preview tutorial. First, let’s have a look at how queries are formed:

A basic query has three parts to it:

SELECT - Parts of document to return

FROM - The data bucket, or data store to work with

WHERE - Conditions the document must satisfy

The query really only requires the SELECT clause. If we run the SELECT clause with a * wildcard; we will be selecting all parts of the document. So if we run the following query:

Bearing in mind what we learned earlier about nested data structures, if we changed that * wildcard to use one of the nested attributes of the document ‘children’ instead of the * wildcard, we can return from our query just a fragment of the document.

Now remember, we have NO FIXED SCHEMA in Couchbase so we don’t lose any of the amazing flexibility that we love about Couchbase. Now when I say we have no fixed schema, I mean that Couchbase doesn’t enforce a schema; but our documents do of course have structure, given that they are JSON documents.

N1QL querying works against documents, not rows or columns contrary to relational databases. As documents can have nested attributes and embedded arrays, a few additional operators are needed. In N1QL, we have a ‘.’ operator which is used to refer to children, and a ‘[ ]’ operator which is used to refer to an element in an array. We can actually use a combination of the operators to access data at any depth in a document.

For example; if we ran the query:

SELECT children[0].fname AS cname FROM tutorial WHERE fname='Dave'

Here we are fetching the first child’s name and explicity aliasing it to ‘cname’. Attributes from child documents can be explicitly aliased using the AS clause.

The result we return from that query is:

{"resultset":[{"fname":"Aiden"}]}

Document databases such as Couchbase often store meta-data about a document outside of the document. In N1QL, we use the ‘META()’ function to access the meta-data for each document in the sample tutorial database. From this query, the only fields we are going to return are the document metadata. The query is as follows:

In the previous queries, we used the ‘WHERE’ clause to match a single document, but we could also use other comparison operators to match multiple documents. Let’s say for instance, we want to find everyone in our database whose age is over 30; we can run the following query:

All of the standard comparison operators are supported such as (>, >=, <, <=, = and !=). All of these comparisons also consider the value’s type so score > 8will return documents containing a numeric score that is greater than 8.

One of the coolest features, in my opinion, is the way we can do pattern matching by using the ‘LIKE’ operator in the WHERE clause. Let’s say, for instance, we need to find everyone in our database who has an email address with yahoo.com. We can run a query using the ‘LIKE’ operator to match the email addresses. In this query, we will use the ‘%’ as a wildcard which will match 0 or more characters. We could also use the ‘_’ if we needed to match exactly 1 character. So, the query to find everyone in our database who uses a yahoo.com email address is:

SELECT fname, email FROM tutorial WHERE email LIKE '%@yahoo.com'

As you can see, we have included the ‘LIKE’ operator in our WHERE clause to match the email addresses. The result set that query will return is like this…

Personally, I think this is one of the most handy features of N1QL! But let’s say we want to do the opposite of what we’ve just done, and we want to list all people who don’t match the yahoo.com email address. Another wicked feature is the ‘NOT LIKE’ operator which we can use to find documents that don’t match the pattern.

We can of course, combine multiple conditions by using the ‘AND’operator. For example, if we wanted to return people who have atleast one child, and a gmail email address, we would run:

SELECT fname, email, children FROM tutorial WHERE LENGTH(children) > 0 AND email LIKE '%@gmail.com'

We could also replace this ‘AND’ operator with the ‘OR’ operator to match on multiple conditions.

The similarities to SQL querying continue as we move onto ordering and pagination of our query results. N1QL has included the familiar ‘ORDER BY’ clause to allow us to order our query result set. Queries can produce a lot of results if we have a very large data set, so we might want to paginate our results. Good news! We can do that too! A query including an ‘ORDER BY’ clause and a ‘LIMIT’ paginator might look like the following:

SELECT fname, age FROM tutorial ORDER BY age LIMIT 2

We can create data aggregates on our data set by utilising commands such as the ‘COUNT()’ function; which would tell us how many documents are in our bucket. We can also group our data by using the familiar ‘GROUP BY’ clause. If we wanted to filter the result set on which groups are returned, we can use the ‘HAVING’ clause, where we would have used the ‘WHERE’ clause to filter documents.

If we wanted to return only groups who have more than one member, we can write a query like the following:

SELECT relation, COUNT(*) AS count FROM tutorial GROUP BY relation HAVING COUNT(*) > 1

One last feature I’d like to mention is the fact that we can do in-document joins (also called unnesting or flattening). This means we can take the contents of nested arrays and join them with the parent object. So, for instance; if we wanted to join Dave with each of his 2 children, we can write a query like the following:

SELECT * FROM tutorial AS contact OVER child IN contact.children WHERE contact.fname = 'Dave'

Well, that pretty much wraps up the features of N1QL that I really wanted to show off. Personally, I think this is one of the most exciting Couchbase projects, I absolutely love the syntax of N1QL and think the project itself is fantastic.

If you want to walk through the 15-minute online tutorial for a deeper dive, you can do so here.

And if you want a more in-depth Developer Preview guide to N1QL, you can see the official docs by clicking here.

I hope this blog has inspired you to get started using N1QL, as it is incredibly fun to use and a very cool piece of technology. If you have any questions on your adventures into the N1QL world, I encourage you to ask away in our N1QL Community Portal and get them answered by the pros!