*Ba Dum Tschhh*\u00a0 \u2026See what I did there? Makes cents? Get it? Haha.<\/p>\n
So\u2026 N1QL (pronounced Nickel)\u2026 Couchbase\u2019s new next-generation query language; what is it?\u00a0 Well, it\u2019s a rather genius designed, human readable \/ writable, extensible language designed for ad-hoc and operational querying within Couchbase.\u00a0 For those already familiar with querying within Couchbase, that blurb will probably make sense to you.\u00a0 If not \u2013 well, probably not, so let me clear it up a little more.<\/p>\n
But before I do that, I must inform you that this blog article isn\u2019t the best place for you to go if you want to dive in and get started learning N1QL.\u00a0 It is a view into N1QL from a developer\u2019s perspective including why I am so excited about it, and the features I am proud to point out.\u00a0 If you want to get started learning about N1QL, click here<\/a>.\u00a0 Or alternatively, go and have a go of the Online Tutorial<\/a>.\u00a0 Anyway, back to clearing up what I mean when I say N1QL\u2026<\/p>\n \u201cN1QL is similar to the standard SQL language for relational databases, but also includes additional features; which are suited for document-oriented databases.\u201d\u00a0\u00a0 N1QL has been designed as an intuitive Query Language for use on databases structured around Documents instead of tables.\u00a0 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.<\/p>\n Before we move on with N1QL, let\u2019s talk quickly about document modeling within Couchbase.\u00a0 As you probably know; within Couchbase we model our documents primarily in JSON.\u00a0 We\u2019re all familiar with JSON, so I won\u2019t 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.\u00a0 Contrary to SQL though, N1QL has the ability to navigate nested data because it supports the concept of paths.<\/em><\/strong>\u00a0 This is very<\/strong> cool.\u00a0 We can use paths<\/em> by using a dot-notation syntax to give us the logical location of an attribute within a document.\u00a0 For example; if we had an e-commerce site with documents containing customers\u2019 orders, we could look inside those documents, to an Nth nested level for attributes. So if we wanted to look for the customer\u2019s shipping street:<\/p>\n Very cool, right?\u00a0 This is also one of the big differentiators that set this query language apart from SQL.<\/p>\n Like most of you reading this, I too come from a SQL background.\u00a0 I believe I am safe to assume that 99% of you do too, right?\u00a0 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.<\/p>\n Now let\u2019s run some queries and see what happens!\u00a0 For these queries, I shall be running them against the sample \u2018tutorial\u2019 data bucket as seen on the online tutorial \/ Developer Preview tutorial.\u00a0 First, let\u2019s have a look at how queries are formed:<\/p>\n A basic query has three parts to it:<\/p>\n The query really only requires the SELECT clause.\u00a0 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:<\/p>\n We will return the data as seen here\u2026<\/a><\/p>\n 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 \u2018children\u2019 <\/em><\/strong>instead of the * wildcard, we can return from our query just a fragment of the document.<\/p>\n So by running:<\/p>\n We will return:<\/p>\n Now remember, we have NO FIXED SCHEMA <\/strong>in Couchbase so we don\u2019t lose any of the amazing flexibility that we love about Couchbase.\u00a0 Now when I say we have no fixed schema, I mean that Couchbase doesn\u2019t enforce a schema; but our documents do of course have structure, given that they are JSON documents.<\/p>\n N1QL querying works against documents, not rows or columns contrary to relational databases.\u00a0 As documents can have nested attributes and embedded arrays, a few additional operators\u00a0<\/em><\/strong>are needed. \u00a0In N1QL, we have a \u2018.\u2019 <\/strong>operator which is used to refer to children, and a \u2018[ ]\u2019<\/em><\/strong> operator which is used to refer to an element in an array.\u00a0 We can actually use a combination of the operators to access data at any depth in a document.<\/p>\n For example; if we ran the query:<\/p>\n Here we are fetching the first child\u2019s name and explicity aliasing it to \u2018cname\u2019.\u00a0 Attributes from child documents can be explicitly aliased using the AS <\/em><\/strong>clause.<\/p>\n The result we return from that query is:<\/p>\n Document databases such as Couchbase often store meta-data about a document outside of the document.\u00a0 In N1QL, we use the \u2018META()\u2019 <\/em><\/strong>function to access the meta-data for each document in the sample tutorial database.\u00a0 From this query, the only fields we are going to return are the document metadata.\u00a0 The query is as follows:<\/p>\n And the results can be seen here\u2026<\/a><\/p>\n In the previous queries, we used the \u2018WHERE\u2019 <\/em><\/strong>clause to match a single document, but we could also use other comparison operators to match multiple documents.\u00a0 Let\u2019s say for instance, we want to find everyone in our database whose age is over 30; we can run the following query:<\/p>\n Which will return this result set\u2026<\/a><\/p>\n All of the standard comparison operators are supported such as (>, >=, <, <=, = and !=).\u00a0 All of these comparisons also consider the value\u2019s type so score > 8<\/em><\/strong>will return documents containing a numeric score that is greater than 8.<\/p>\n One of the coolest features, in my opinion, is the way we can do pattern matching by using the \u2018LIKE\u2019 <\/em><\/strong>operator in the WHERE clause.\u00a0 Let\u2019s say, for instance, we need to find everyone in our database who has an email address with yahoo.com.\u00a0 We can run a query using the \u2018LIKE\u2019<\/em><\/strong> operator to match the email addresses.\u00a0 In this query, we will use the \u2018%\u2019 as a wildcard which will match 0 or more characters.\u00a0 We could also use the \u2018_\u2019 if we needed to match exactly 1 character.\u00a0 So, the query to find everyone in our database who uses a yahoo.com email address is:<\/p>\n As you can see, we have included the \u2018LIKE\u2019 <\/em><\/strong>operator in our WHERE clause to match the email addresses.\u00a0 The result set that query will return is like this\u2026<\/p>\n Personally, I think this is one of the most handy features of N1QL!\u00a0 But let\u2019s say we want to do the opposite of what we\u2019ve just done, and we want to list all people who don\u2019t <\/strong>match the yahoo.com email address.\u00a0 Another wicked feature is the \u2018NOT LIKE\u2019<\/em><\/strong> operator which we can use to find documents that don\u2019t match the pattern.<\/p>\n We can of course, combine multiple conditions by using the \u2018AND\u2019<\/em> <\/strong>operator.\u00a0 For example, if we wanted to return people who have atleast one child, and a gmail email address, we would run:<\/p>\n We could also replace this \u2018AND\u2019 <\/em><\/strong>operator with the \u2018OR\u2019 <\/em><\/strong>operator to match on multiple conditions.<\/p>\n The similarities to SQL querying continue as we move onto ordering and pagination of our query results.\u00a0 N1QL has included the familiar \u2018ORDER BY\u2019 <\/em><\/strong>clause to allow us to order our query result set.\u00a0 Queries can produce a lot of results if we have a very large data set, so we might want to paginate our results.\u00a0 Good news!\u00a0 We can do that too!\u00a0 A query including an \u2018ORDER BY\u2019<\/em><\/strong> clause and a \u2018LIMIT\u2019 <\/em><\/strong>paginator might look like the following:<\/p>\n We can create data aggregates on our data set by utilising commands such as the \u2018COUNT()\u2019 <\/em><\/strong>function; which would tell us how many documents are in our bucket.\u00a0 We can also group our data by using the familiar \u2018GROUP BY\u2019 <\/em><\/strong>clause.\u00a0 If we wanted to filter the result set on which groups are returned, we can use the \u2018HAVING\u2019 <\/em><\/strong>clause, where we would have used the \u2018WHERE\u2019 <\/em><\/strong>clause to filter documents.<\/p>\n If we wanted to return only groups who have more than one member, we can write a query like the following:<\/p>\n One last feature I\u2019d like to mention is the fact that we can do in-document joins (also called unnesting or flattening).<\/em><\/strong>\u00a0 This means we can take the contents of nested arrays and join them with the parent object.\u00a0 So, for instance; if we wanted to join Dave with each of his 2 children, we can write a query like the following:<\/p>\n This query would return the result set as you can see here\u2026<\/a><\/p>\n Well, that pretty much wraps up the features of N1QL that I really wanted to show off.\u00a0 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.<\/p>\n If you want to get started using N1QL, you can download the Developer Preview now, by visiting the N1QL section of our Community Portal.<\/a><\/p>\n If you want to walk through the 15-minute online tutorial for a deeper dive, you can do so here.<\/a><\/p>\n And if you want a more in-depth Developer Preview guide to N1QL, you can see the official docs by clicking here.<\/a><\/p>\n\n
\nFROM tutorial
\nWHERE fname = ‘Dave’<\/div>\n<\/div>\n
\nFROM tutorial
\nWHERE fname = ‘Dave’<\/div>\n<\/div>\n
\n“resultset”<\/span>:<\/span> [<\/span>
\n{<\/span>
\n“children”<\/span>:<\/span> [<\/span>
\n{<\/span>
\n“age”<\/span>:<\/span> 17<\/span>,<\/span>
\n“fname”<\/span>:<\/span> “Aiden”<\/span>,<\/span>
\n“gender”<\/span>:<\/span> “m”<\/span>
\n}<\/span>,<\/span>
\n{<\/span>
\n“age”<\/span>:<\/span> 2<\/span>,<\/span>
\n“fname”<\/span>:<\/span> “Bill”<\/span>,<\/span>
\n“gender”<\/span>:<\/span> “m”<\/span>
\n}<\/span>
\n]<\/span>
\n}<\/span>
\n]<\/span>
\n}<\/span><\/div>\n<\/div>\n
\nFROM tutorial
\nWHERE fname=’Dave’<\/div>\n<\/div>\n
\n“resultset”<\/span>:<\/span> [<\/span>
\n{<\/span>
\n“fname”<\/span>:<\/span> “Aiden”<\/span>
\n}<\/span>
\n]<\/span>
\n}<\/span><\/div>\n<\/div>\n
\nFROM tutorial<\/div>\n<\/div>\n
\nFROM tutorial
\nWHERE age > 30<\/div>\n<\/div>\n
\nFROM tutorial
\nWHERE email LIKE ‘%@yahoo.com’<\/div>\n<\/div>\n
\n“resultset”<\/span>:<\/span> [<\/span>
\n{<\/span>
\n“email”<\/span>:<\/span> “harry@yahoo.com”<\/span>,<\/span>
\n“fname”<\/span>:<\/span> “Harry”<\/span>
\n}<\/span>
\n{<\/span>
\n“email”<\/span>:<\/span> “dave@yahoo.com”<\/span>,<\/span>
\n“fname”<\/span>:<\/span> “Dave”<\/span>
\n}<\/span>
\n]<\/span>
\n}<\/span><\/div>\n<\/div>\n
\nFROM tutorial
\nWHERE LENGTH(children) > 0 AND email LIKE ‘%@gmail.com’<\/div>\n<\/div>\n
\nFROM tutorial
\nORDER BY age
\nLIMIT 2<\/div>\n<\/div>\n
\nFROM tutorial
\nGROUP BY relation
\nHAVING COUNT(*) > 1<\/div>\n<\/div>\n
\nFROM tutorial AS contact
\nOVER child IN contact.children
\nWHERE contact.fname = ‘Dave’<\/div>\n<\/div>\n