Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

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

Traditional relational databases weren’t designed for today’s customers. Learn about the world’s first NoSQL Engagement Database purpose-built for the new era of customer experience.

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

SQL on RDBMS

N1QL: SQL on JSON    

Objects

Tables, tuples, columns

Keyspaces, JSON Documents, key-value

Example of a document in a keyspace customer:

{

“name”: “abc”,

“address”:{

“zip”:94132,

“apt”:3

}

“contact”:[{

“phone”:8883334444

}]

}

References



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


Statements

SELECT, INSERT, UPDATE, DELETE, MERGE

SELECT, INSERT, UPDATE, DELETE, MERGE

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

Schema

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/Output

INPUT: Sets of Tuples

OUPUT: Set of Tuples

Refer Figure 1

INPUT: Sets of JSON

OUTPUT: Set of JSON

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 

ORDER BY SUM(o.Amount) DESC


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

Learn how the world’s first NoSQL Engagement Database delivers unparalleled performance at any scale for customer experience innovation that never ends.

Topics:
testing ,n1ql ,json ,database ,tutorial

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}