subscribe: Posts | Comments

Couchbase N1QL query language

Introduction

Couchbase Query Language, known as N1QL or ‘Nickel’, helps you to find and change data in Couchbase Server. We designed the language to be human -readable and -writable; it is an extensible language designed for ad-hoc querying. The query language is a standard semantic you use to build querying ability in other programming languages.

The N1QL Developer Preview 1 is a pre-beta release of the language. It provides a subset of functionality from the full query language.

N1QL is similar to the standard SQL language for relational databases, but it is also includes some additional features which are suited for document-oriented databases. This guide describes key concepts about the language, provides tutorials describing how to use the language, discusses advanced operations you can perform with the language, and includes a language reference for language syntax and language elements.

Goals of Language

N1QL is designed to be a concise, intuitive, expressive language for building complex queries. It is designed for document-oriented databases which store application objects as well as their relationships in documents instead of tables. Application data can be expressed as atomic values or in nested structures within the document, such as objects or arrays. Relationships between documents can be expressed as values or values in nested structures. To find 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.

Who Uses It

Application developers and data analysts use N1QL to perform ad-hoc querying. Any application developer who wants to provide reports or retrieve data that is hard to find can use N1QL in their application logic. Client library developers who want to provide query functionality in their library can use N1QL as the standard to build their new APIs.

Ways to Use It

The major use cases for N1QL are the following:

  • Embed complex query requests in your application logic. Individual queries can include powerful logic such aggregation, complex filtering and result transformations. With other approaches to querying, you might have to perform multiple requests to achieve the same results.
  • Perform analytics and reporting. You can execute ad-hoc queries from your application. For example if you want a list of all users in your application who have purchased a specific item, you can use N1QL.

Where to Start Learning

There are different approaches for learning a new language. Depending on your learning style, you can start with information at one of the links below:

  • Concepts – start here if you want to understand the theories and ideas behind the language.
  • Tutorial – if you want to try out sample queries with N1QL.
  • Language Reference – to read more about language syntax and clauses.

For the full language and syntax see the N1QL language reference.

Data Modeling

Couchbase Server is a document database: you store information in documents rather store it in table rows. Couchbase has a much more flexible data format; documents generally contain all the information about a data entity, including compound data. In the case of traditional, relational databases, you may need to spread data from a single object across multiple tables, in a process known as normalization. Imagine we have an application for contacts and their families. We could store each contact in a table row, or we can choose the equivalent document-based model with an individual document per contact:rel_vs_doc_model

Instead of a table, we can represent our object as one or more documents. A document in a document-oriented database is a JSON object consisting of a number of fields that you define. An object can be one or more documents instead of one or more table rows; attributes of each object can be fields in the document. In comparison, a relational database has columns in a table which hold a type of attribute, such as name or zip code. There is no schema in document-oriented databases; every JSON document can have its own individual set of keys, although you may probably adopt one or more informal schemas for your data. Unlike the relational mode, attributes within a document do not have to have a predefined type. For instance a zip code can be an integer or a string using the document model. Even documents belonging to the same class of application objects, such as a customer document can have different attributes than other customer documents. For example you can have some customers having an extra field for shipping insurance, while other customer documents do not have that field at all.

Nested Structures and Paths

In traditional relational databases, each record is represented by a table row and attributes for a record are represented by each column in a table. In a document-database, a record is represented by one or more documents. Attributes for a business object are represented by fields in the document. For example, imagine a simple document representing a customer:

{
    "id": "7983345",
    "name": "Liam Kilpatrick",
    "type": "retail"
    ....
}

A document field can contain nested data structures such as arrays and objects; within an array or object, you can further nest data. For example, consider a more complex document representing a customer order:

{
  "type": "customer-order",
  "grand_total": 1000,
  "billTo": {
     "street": "123 foo",
     "state": "CA",
  },
  "shipTo": {
     "street": "123 foo",
     "state": "CA"
  },
  items: [
    { "productId": "coffee", "qty": 1 },
    { "productId": "tea", "qty": 1 }
  ]
}

This makes N1QL a unique querying language compared to SQL. In order to navigate nested data in document arrays and objects, N1QL supports the concept of paths. A path uses a dot notation syntax and provides the logical location of an attribute within a document. For example to get the street from a customer order, we use this path:

orders.shipTo.street

This path refers to the value for ‘street’ in the ‘billTo’ object. You use a path with a arrays or nested objects in order to get to attributes within the data structure. You can also use array syntax in your path to get to information:

orders.items[0].productId

This path will evaluate to the value ‘coffee’ as this is the ‘productID’ for the first array element under ‘items’.

Paths enable you to find and data in document structures without having to get the entire document or handle it within your application. Any document data can be requested and only the relevant information in the document will be returned to your application; this reduces the bandwidth used in for querying. N1QL also provides a query functionality known as OVER. This enables you to flatten array elements within a document. When a document contains a nested array, you can have each member of the array joined. These joined objects become input to other query operations.

Continue…