*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:

SELECT *
FROM tutorial
WHERE fname = ‘Dave’

We will return the data as seen here…

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.

So by running:

SELECT children
FROM tutorial
WHERE fname = ‘Dave’

We will return:

{
“resultset”: [
{
“children”: [
{
“age”: 17,
“fname”: “Aiden”,
“gender”: “m”
},
{
“age”: 2,
“fname”: “Bill”,
“gender”: “m”
}
]
}
]
}

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:

SELECT META() AS meta
FROM tutorial

And the results can be seen here…

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:

SELECT fname, age
FROM tutorial
WHERE age > 30

Which will return this result set…

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…

{
“resultset”: [
{
“email”: “harry@yahoo.com”,
“fname”: “Harry”
}
{
“email”: “dave@yahoo.com”,
“fname”: “Dave”
}
]
}

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’

This query would return the result set as you can see here…

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 get started using N1QL, you can download the Developer Preview now, by visiting the N1QL section of our Community Portal.

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!

Go ahead and dive into the N1QL world!  I’ll see you in there!

– Robin Johnson
Developer Advocate, Europe

Posted by The Couchbase Team

21 Comments

  1. Alexander Gabriel October 11, 2013 at 9:49 am

    Coooooooooooooool!

    1. It is rather, isn\’t it!

  2. Interesting. Does NIQL have any operators to construct arbitrary result documents? For instance, suppose I want to turn the \”dave\” tutorial document into something like this:

    {
    resultset: [{
    age: 17,
    fname: \”Aiden\”,
    gender: \”m\”,
    parent: {
    fname: \”Dave\”,
    email: \”dave@gmail.com\”,
    hobbies: [\”golf\”,\”surfing\”],
    lname: \”Smith\”,
    relation: \”friend\”,
    title: \”Mr.\”,
    type: \”contact\”
    }
    },{
    age: 2,
    fname: \”Bill\”,
    gender: \”f\”,
    parent: {
    fname: \”Dave\”,
    email: \”dave@gmail.com\”,
    hobbies: [\”golf\”,\”surfing\”],
    lname: \”Smith\”,
    relation: \”friend\”,
    title: \”Mr.\”,
    type: \”contact\”
    }
    }]
    }

    1. Roland, sorry for the delay in responding. Yes, N1QL allows you construct arbitrary transformations and results. In the tutorial, the following query will produce the results you wanted:

      SELECT * FROM tutorial parent OVER parent.children WHERE parent.fname = \”Dave\”

      -gerald

      1. Gerald, thanks!

        I\’m a bit confused now. This looks like exactly the same query (except for the alias) as the one you describe in the feature \”in-document joins\”. However, the resultset given for that query is different, in that there are \”child\” properties in which the child properties are contained, whereas in my sample resultset thos properties are at the top-level object.

        Am I missing or misinterpreting something?

        1. Hi Roland,

          I was trying to keep the query simple :). You can always project any subset in the result. The following query produces your precise results:

          SELECT parent, children.age, children.fname, children.gender FROM tutorial parent OVER parent.children WHERE parent.fname = \”Dave\”

          1. Thanks! I suppose I could\’ve guessed, but I\’m glad for the explanation. Great stuff!

          2. Forgot to add – I realize this blog post is already quite lengthy, but I would really appreciate a more formal / complete explanation of how the OVER operator works. Do you have a link? TIA, Roland.

          3. Roland,

            There\’s a bit here, although we can (and will) add to the documentation:

            http://docs.couchbase.com/couc

            FYI, in next Developer Preview, the FROM … OVER syntax will be chained to FROM … UNNEST, which we consider more descriptive.

            In essence, it is conceptually joining a nested array with its parent document or object, to create a list of <parent, array=\”\” element=\”\”> pairs.

            Ask away with any other questions, we\’re always happy to respond.

            Thanks for the kudos!

  3. Hi, I\’m interested in knowing more about the inner workings of the N1QL query language. Specificly I\’d like to know if it returns only docs that were persisted to disk ? Does it use Couchbase views to perform the query? What about performance issues?
    Where can I find answers to these questions?
    Thanks

    1. N1QL currently uses Couchbase views. As such, it currently returns docs that are visible to views, and its performance is based on the performance of views.

      We are working on a dedicated set of indexes for N1QL. N1QL will support both the new indexes and Couchbase views. We are also working on the performance of Couchbase views.

      You can also post questions directly at query.couchbase.com.

  4. I know this is recently released, but is N1QL available in couchbase lite? I\’ve got an app I\’m working on that allows free-form database, so I wouldn\’t be able to pre-create views, so ad-hoc query would be what I need.

    1. N1QL is not currently available in Couchbase Lite but we are looking at it for the future. As for what you should use right now it would help if we had more info on what you are trying to do. The best place to post your technical questions for Couchbase Mobile, including Couchbase Lite, would be our mobile Google group: https://groups.google.com/foru

  5. Does the engine require a particular version of Couchbase server, or can it operate against older versions – e.g. 2.5?

    1. It works with Couchbase server 2.5.1.

      1. when is release date for where developer can use it in production environment.

        1. Release plans are still being worked out, please stay tuned for announcements, tentative plan is to make it available in 1st half-2015

          1. We will provide earlier previews before that though, so do contact us since we would love to learn about your use-case and get more information about your planned usage for N1QL. Queryinfo at query.couchbase.com and you can also contact via http://forums.couchbase.com/c/

  6. Can we do iteration over arrays while performing update operation in Couchbase 4.0 and N1QL Server?. Like e.g :
    \”UPDATE mybucket SET address.location=\’Office\’ OVER child IN address WHERE child.type = \’Home\’ END\”
    (address is an array here).
    Can we perform the above query?

  7. How is the condition of the program?نهال پسته

Leave a reply