N1QL (SQL for JSON) - Database Query Language

What is N1QL?
Is it SQL?
Try It

What is N1QL?

N1QL is a declarative query language that extends SQL for JSON. You can query data via native framework and language integration, a fluent API, or the JDBC/ODBC drivers.

N1QL enables you to query JSON documents without any limitations - sort, filter, transform, group, and combine data with a single query. That’s right. You can combine data from multiple documents with a JOIN. That flexible data model you were promised? This is it. You’re no longer limited to “single table” and “table per query” data models.

// Query Statement
SELECT * FROM databases WHERE category='NoSQL'

// Results

{
  "name": "Couchbase Server",
  "version": "4.0",
  "category": "NoSQL",
  "features": [
    {
      "name": "N1QL",
      "capabilities": [
        "JOIN", "NEST", "UNNEST"
      ]
    }
  ]
}

Query with N1QL

// Fluent API
bucket.query(select("name").from("databases").where(x(category).eq(s("NoSQL"))));

// Query Statement
bucket.query(Query.simple("SELECT name FROM databases WHERE category='NoSQL'"));

Querying with N1QL in Java

var query = from db in bucket.Queryable<DataBases>(bucket) where db.category == "NoSQL"
select db.name;

Querying with N1QL in .NET

bucket.query(couchbase.N1qlQuery.fromString("SELECT name FROM databases WHERE category='NoSQL'"));

Querying with N1QL in Node.js

$bucket->query(CouchbaseN1qlQuery::fromString("SELECT name FROM databases WHERE category='NoSQL'"));

Querying with N1QL in PHP

cmd.query = "SELECT name FROM databases WHERE category='NoSQL'";

Querying with N1QL in C/C++

bucket.n1ql_query(N1QLQuery("SELECT name FROM databases WHERE category='NoSQL'"))

Querying with N1QL in Python

You can improve query performance with secondary indexes. Want to create a bunch of indexes? Go for it. Want to create a compound index on multiple fields? Go for it. Worried about what will happen to query performance as the number of nodes increases? Don't. We implemented global secondary indexes. The key word being "global". A global secondary index is not partitioned. It's a whole index. After all, it's a lot faster to check a whole index on one node than it is to check a partial index on every node.

Query, Scale, Repeat section

Is it SQL?

Welcome to The Sci-fi Emporium.

The product catalog may be stored in JSON, but it’s queried in SQL – well, N1QL.

The data model is comprised of authors, books, reviews, and awards. An author has an ID, first name, last name, country, and more. A book has an ID, name, date published, author name (for convenience), author ID, and more. A review has a book name (for convenience), book ID, rating, and more. An award has a name, year, book ID, author ID, and more.

1SELECT

2WHERE

3ORDER BY #1

4DISTINCT

5ORDER BY #2

6GROUP BY

7JOIN

8SUBQUERY

9UNION

SELECT

Let's browse all books for sale.

SQL Statement

SELECT name, author
FROM books

SQL Results (Rows)

name	author
Ender’s Game	Orson Scott Card
Foundation	Isaac Asimov
Neuromancer	William Gibson
Consider Phlebas	Iain M. Banks
Revelation Space	Alastair Reynolds
…	…

N1QL Statement

SELECT name, author
FROM books

N1QL Results (Document)

{
  "results": [
    {"name": "Ender’s Game", "author": "Orson Scott Card"},
    {"name": "Foundation", "author": "Isaac Asimov"},
    {"name": "Neuromancer", "author": "William Gibson"},
    {"name": "Consider Phlebas", "author": "Iain M. Banks"},
    {"name": "Revelation Space", "author": "Alastair Reynolds"},
    …
  ]
}

WHERE

If you're like me, you’ll start by checking out the new releases.

SQL Statement

SELECT name, author
FROM books
WHERE YEAR(published) >= 2014

SQL Results (Rows)

name	author
Slow Bullets	Alastair Reynolds
Dark Lightning	John Varley
Coming Home	Jack McDevitt
The Peripheral	William Gibson
Armada	Ernest Cline
…	…

N1QL Statement

SELECT name, author
FROM books
WHERE DATE_PART_STR(published, "year") >= 2014

N1QL Results (Documents)

{
  "results": [
    {"name": "Slow Bullets", "author": "Alastair Reynolds"},
    {"name": "Dark Lightning", "author": "John Varley"},
    {"name": "Coming Home", "author": "Jack McDevitt"},
    {"name": "The Peripheral", "author": "William Gibson"},
    {"name": "Armada", "author": "Ernest Cline"},
    …
  ]
}

ORDER BY #1

After checking out the new releases, you’ll browse by author. However, an author’s books should be displayed in the order they were published. There’s nothing worse than reading a book out of order.

SQL Statement

SELECT name, YEAR(published) AS published
FROM  books
WHERE author = "Alastair Reynolds"
ORDER BY published

SQL Results (Rows)

name	date
Revelation Space	2000
Chasm City	2001
Redemption Ark	2002
Absolution Gap	2003
Century Rain	2004
…	…

N1QL Statement

SELECT name, DATE_PART_STR(published, "year") as published
FROM  books
WHERE author = "Alastair Reynolds"
ORDER BY published

N1QL Results (Document)

{
  "results": [
    {"name": "Revelation Space", "published": "2000"},
    {"name": "Chasm City", "published": "2001"},
    {"name": "Redemption Ark", "published": "2002"},
    {"name": "Absolution Gap", "published": "2003"},
    {"name": "Century Rain", "published": "2004"},
    …
  ]
}

DISTINCT

How about we browse by series, or saga, to discover new books and authors?

SQL Statement

SELECT DISTINCT(series), author
FROM books
WHERE series IS NOT NULL
ORDER BY series

SQL Results (Rows)

series	author
Commonwealth	Peter F. Hamilton
Culture	Iain M. Banks
Ender’s Game	Orson Scott Card
Foundation	Isaac Asimov
Revelation Space	Alastair Reynolds
…	…

N1QL Statement

SELECT DISTINCT(series), author
FROM books
WHERE series IS NOT MISSING
ORDER BY series

N1QL Results (Document)

{
   "results": [
      {"series": "Commonwealth", "author": "Peter F. Hamilton"},
      {"series": "Culture", "author": "Iain M. Banks"},
      {"series": "Ender’s Game", "author": "Orson Scott Card"},
      {"series": "Foundation", "author": "Isaac Asimov"},
      {"series": "Revelation Space", "author": "Alastair Reynolds"},
      …
   ]
}

ORDER BY #2

Now that we found a series, let’s browse its books, and yes, they should be displayed in the order they were published.

SQL Statement

SELECT name, YEAR(published) AS year
FROM books
WHERE series = “Foundation”
ORDER BY year

SQL Results (Rows)

name	year
Foundation	1951
Foundation and Empire	1952
Second Foundation	1953
Foundation’s Edge	1982
Foundation and Earth	1986
…	…

N1QL Statement

SELECT name, DATE_PART_STR(published, "year") AS year
FROM books
WHERE series = "Foundation"
ORDER BY year

N1QL Results (Document)

{
  "results": [
    {"name": "Foundation", "year": "1951"},
    {"name": "Foundation and Empire", "year": "1952"},
    {"name": "Second Foundation", "year": "1953"},
    {"name": "Foundation’s Edge", "year": "1982"},
    {"name": "Foundation and Earth", "year": "1986"},
    …
  ]
}

GROUP BY

We can discover new books by browsing customer reviews too. In this case, we’ll browse highly rated books with at least 100,000 reviews.

SQL Statement

SELECT book, AVG(rating) AS average
FROM reviews
GROUP BY book
HAVING COUNT(*) > 100000
ORDER BY average DESC

SQL Results (Rows)

book	average
Ready Player One	4.31
Ender’s Game	4.28
Foundation	4.07
Speaker for the Dead	4.01
Neuromancer	3.85
…	…

N1QL Statement

SELECT book, AVG(rating) AS average
FROM reviews
GROUP BY book
HAVING COUNT(*) > 100000
ORDER BY average DESC

N1QL Results (Document)

{
  "results": [
    {"book": "Ready Player One", "average": "4.31"},
    {"book": "Ender’s Game", "average": "4.28"},
    {"book": "Foundation", "average": "4.07"},
    {"book": "Speaker for the Dead", "average": "4.01"},
    {"book": "Neuromancer", "average": "3.85"},
    …
  ]
}

JOIN

In addition, we can discover new books by browsing award winners.

SQL Statement

SELECT b.name, YEAR(a.year) AS year, a.name AS award
FROM awards a INNER JOIN books b
ON a.book_id = b. id
WHERE a.year > 1969
ORDER BY name, year, award

SQL Results (Rows)

name	year	award
Gateway	1978	Hugo
Gateway	1978	Nebula
Neuromancer	1984	Philip
Neuromancer	1985	Hugo
Neuromancer	1985	Nebula
…	…	…

N1QL Statement

SELECT b.name, DATE_PART_STR(a.year, "year") as year, a.name as award
FROM awards a INNER JOIN books b
ON KEYS a.book_id
ORDER BY b.name, year, award

N1QL Results (Document)

{
  "results": [
    {"name": "Gateway", "year": "1978", "award": "Hugo"},
    {"name": "Gateway", "year": "1978", "award": "Nebula"},
    {"name": "Neuromancer", "year": "1984", "award": "Philip"},
    {"name": "Neuromancer", "year": "1985", "award": "Hugo"},
    {"name": "Neuromancer", "year": "1985", "award": "Nebula"},
    …
  ]
}

SUBQUERY

Let’s browse all books by British authors.

SQL Statement

SELECT name, author
FROM books
WHERE author_id IN (
  SELECT id
  FROM authors
  WHERE country = “UK”)

SQL Results (Rows)

name	author
Terminal World	Alastair Reynolds
2001: A Space Odyssey	Arthur C. Clarke
The Algebraist	Iain M. Banks
Glasshouse	Charles Stross
Great North Road	Peter F. Hamilton
…	…

N1QL Statement

SELECT b.name, b.author
FROM books b
WHERE EXISTS (
  SELECT id
  FROM authors
  USE KEYS b.author_id
  WHERE country = "UK")

N1QL Results (Document)

{
  "results": [
    {"name": "Terminal World", "author": "Alastair Reynolds"},
    {"name": "2001: A Space Odyssey", "author": "Arthur C. Clarke"},
    {"name": "The Algebraist", "author": "Iain M. Banks"},
    {"name": "Glasshouse", "author": "Charles Stross"},
    {"name": "Great North Road", "author", "Peter F. Hamilton"},
    …
  ]
}

UNION

Who said the Emporium only sold books? Let’s browse Shane’s favorite books and movies.

SQL Statement

SELECT name, "Book" as type
FROM books
WHERE favorite = "TRUE"
UNION ALL (
  SELECT name, "Movie" as type
  FROM movies
  WHERE favorite = "TRUE")
ORDER BY name

SQL Results (Rows)

name	type
Aliens	Movie
Blade Runner	Movie
Chasm City	Book
Chasm City	Movie
Ender’s Game	Book
…	…

N1QL Statement

SELECT name, "Book" as type
FROM books
WHERE favorite = TRUE
UNION ALL (
  SELECT name, "Movie" as type
  FROM movies
  WHERE favorite = TRUE)
ORDER BY name

N1QL Results (Document)

{
  "results": [
    {"name": "Aliens",  "type": "Movie"},
    {"name": "Blade Runner", "Type": "Movie"},
    {"name": "Chasm City", "average": "Book"},
    {"name": "Dark City”, "average": "Movie"},
    {"name": "Ender’s Game", "average": "Book"},
    …
  ]
}

Try It

Download, explore the reference app, see some code, and run a few queries.

Get Started

DATABASE QUERYING WITH N1QL

What is N1QL?

Is it SQL?

SELECT

SQL Statement

SQL Results (Rows)

N1QL Statement

N1QL Results (Document)

WHERE

SQL Statement

SQL Results (Rows)

N1QL Statement

N1QL Results (Documents)

ORDER BY #1

SQL Statement

SQL Results (Rows)

N1QL Statement

N1QL Results (Document)

DISTINCT

SQL Statement

SQL Results (Rows)

N1QL Statement

N1QL Results (Document)

ORDER BY #2

SQL Statement

SQL Results (Rows)

N1QL Statement

N1QL Results (Document)

GROUP BY

SQL Statement

SQL Results (Rows)

N1QL Statement

N1QL Results (Document)

JOIN

SQL Statement

SQL Results (Rows)

N1QL Statement

N1QL Results (Document)

SUBQUERY

SQL Statement

SQL Results (Rows)

N1QL Statement

N1QL Results (Document)

UNION

SQL Statement

SQL Results (Rows)

N1QL Statement

N1QL Results (Document)

Try It