Data access using N1QL
N1QL (pronounced "nickel") is the first query language that leverages the complete flexibility of JSON with the full power of SQL. Developed by Couchbase for use with the Couchbase Server, N1QL provides a common query language and JSON-based data model for distributed document-oriented databases.
Couchbase Server stores data in JSON documents, where each document is a JSON object consisting of a number of defined fields. An object can be one or more documents and object attributes can be fields in the document. You can store data as key-value pairs, using the key as you would a primary key in SQL.
Every JSON document has its own individual set of keys, although one or more informal schemas could be adopted for data, and each document has its own implicit schema that represents how you organize and nest information in your documents. Your document structure can evolve over time as your application grows and adds new features.
N1QL enables you to access data in the Couchbase Server using its SQL-like language constructs. N1QL is based on SQL. With a familiar data definition language (DDL), data manipulation language (DML), and query language statements, it works on NoSQL database features such as key-value stores, multi-value attributes, and nested objects. If you know SQL, you can easily learn N1QL, in fact, you’re already more than halfway there.
Key features of N1QL
N1QL provides a rich set of features that let you retrieve, manipulate, transform, and create JSON document data. The key features include:
- SELECT Statement: The SELECT statement in N1QL extends the functionality of the SQL SELECT statement to work with JSON documents. You can use your knowledge of SQL to work with the powerful NoSQL features of Couchbase, which in turn enable you to work with big data, big data users, and cloud computing.
- Data Manipulation Language (DML): N1QL provides the DELETE, INSERT, UPDATE, and UPSERT statements to create, delete, and modify the data stored in JSON documents. You can perform these operations by specifying and executing simple commands.
Note: DML statements are beta in Couchbase Server 4.0.
- Indexes: N1QL provides the CREATE and DROP INDEX statements to easily create and delete indexes.
- Primary Key Access: Data in the Couchbase Server can be stored in key-value pairs, providing primary key access to any data.
- Aggregation: N1QL provides the standard SQL aggregation operators such as MIN, MAX, COUNT, as well as the grouping operators, the GROUP BY clause, and the group filter, HAVING.
- Joins: N1QL lets you retrieve data from multiple documents by specifying joins in the FROM clause.
- Nesting: Nests enable you to retrieve sub-records for a record. That is, for each left hand input, the matching right hand inputs are collected into an array, which is then embedded in the result. For example, you can nest each item in a customer’s order. Nests can be performed on multiple documents.
- Unnesting: Unnesting is the opposite of nesting. The UNNEST function extracts nested sub-records from a record, making each extraction a separate record. Note that unnesting can be performed on a single document.
- Subqueries: N1QL supports multi-part queries through subqueries. You can use a subquery with the SELECT statement or nest it within another subquery, thereby refining the results of the preceding queries.
- UNION, INTERSECT, and EXCEPT: N1QL provides the UNION, INTERSECT, and EXCEPT operations to compare two or more queries. UNION combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. EXCEPT returns any distinct values from the left query that are not found on the right query. INTERSECT returns any distinct values that are returned by the queries on both sides of the INTERSECT operand.
See N1QL reference for information about N1QL language reference and N1QL REST API.