Over a million developers have joined DZone.

Testing N1QL: SQL for JSON (Part 1)

DZone's Guide to

Testing N1QL: SQL for JSON (Part 1)

Testing Couchbase's N1QL can be a challenge, thanks to its ability to manipulate persistent data. This comprehensive guide will walk you through what you need to know.

· Database Zone ·
Free Resource

Download the Scale-Out and High Availability whitepaper. Learn why leading enterprises choose the Couchbase NoSQL database over MongoDB™ after evaluating side by side.

Couchbase N1QL is SQL for JSON. The goal of N1QL is to give developers and enterprises an expressive, powerful, and complete language for querying, transforming, and manipulating JSON data. JSON is self-describing, so we avoid declaring the type for each attribute statically. The N1QL query and index service interprets each document, understands the schema, and executes queries on it.

Like SQL, N1QL is a declarative language. Just like SQL, it allows unlimited variation of queries to be executed. If you want to learn N1QL using an interactive tutorial, you can check it out here. And full documentation is here.

N1QL not only executes the query instructions, it manipulates the persistent data. So, testing such a language is a challenge. We need to test for all the varieties of the syntax, and we need to verify that the results are accurate.

While designing the test framework for N1QL, we had two goals:

  1. Create the largest variety of test queries.

  2. Avoid manual result verification.

This series covers the following topics:

  1. Randgen by MySQL.

  2. How RQG was written in Couchbase to generate N1QL queries.

  3. How we generate two equivalent queries from a single template.

  4. Execution Environment

  5. Result set retrieval and comparison

Random query generators have been used to generate a variety of queries for databases. The MySQL community has created randgen to test it. We used randgen as the starting point and created the Random Query Generator (RQG) to meet our goals above.

This series will explain the full framework and its usage at Couchbase quality engineering.

My next article will cover testing of Couchbase JDBC driver with RQG and how we can enhance RQG to test new features like subquery expressions being added to N1QL.

We chose MySQL for our comparison because it is a well-tested database and randgen works on it. Hence we just had to work on the differences between MySQL and N1QL and the mapping aspect as explained in the table below:

Query Features


N1QL: SQL on JSON    


Tables, tuples, columns

Keyspaces, JSON Documents, key-value

Example of a document in a keyspace customer:


“name”: “abc”,










Flat references:Table.column

select name, address

from customer

where zip = 94132

Flat and Nested: keyspace.keyvalue

select customer.name, customer.address.zip,

customer.contact[0].phone from customer




Query Operations

  • Select, Join, Project, Subqueries
  • Strict Schema
  • Strict Type checking
  • Select, Join, Project, Subqueries
  • Nest & Unnest
  • Flexible json schema
  • JSON keys act as columns


Predetermined Columns

  • Fully addressable JSON
  • Flexible document structure

Data Types

SQL Data types:Character,Varchar,Binary, Boolean,Varbinary,Integer,Smallint,BigInt,Decimal,Numeric,Float,Real, Double Precision,Date,Time,Timestamp, Interval,Array,Multiset,Xml

Conversion Functions

  • JSON Data types: Strings, Number Boolean,  Objects, Arrays, null
  • Conversion Functions


INPUT: Sets of Tuples

OUPUT: Set of Tuples

Refer Figure 1



Refer Figure 1

Figure 1: Differences in Input/Output for MySQL vs. N1QL

Example SQL statement for the relational model above:

SELECT c.CustID, Customer.Name, SUM(o.Amount)

FROM Purchases o   INNER JOIN Connections l ON (o.CustID = l.CustID)   

INNER JOIN Customer c (o.CustID = c.CustID)

GROUP BY c.CustID, c.Name HAVING SUM(o.Amount) > 10000 


Example N1QL statement for the JSON model above:

SELECT Customer.ID, Customer.Name, SUM(OrderLine.Amount) 

FROM Orders UNNEST Orders.LineItems AS OrderLine

INNER JOIN Customer ON KEYS Orders.CustID

GROUP BY Customer.ID, Customer.Name

HAVING SUM(OrderLine.Amount) > 10000

ORDER BY SUM(OrderLine.Amount) DESC

The Forrester Wave™: Big Data NoSQL report. See how the top NoSQL providers stack up. Download now.

testing ,n1ql ,json ,database ,tutorial

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}