N1QL, SQL for JSON

"(SQL++) can handle additional kinds of data, with additional expressive power."

Don Chamberlin
Co-Inventor of SQL

Does SQL have a role to play in a post-relational world?

SQL++, an SQL-compatible query language developed at the University of California, San Diego, extends SQL with a few simple features that enable it to query data in the popular JSON format. The goal of SQL++ is to empower developers to create a new generation of applications, leveraging their existing SQL-based knowledge and tools.

 

This tutorial, written for developers who have a working knowledge of SQL, introduces SQL++ by a series of examples that have been tested on Couchbase Analytics Service. This page provides all the resources you need to load example data into Couchbase and run the example queries.

To run the example queries, you'll need to install Couchbase Server, Enterprise Edition, which you can download freely from couchbase.com/downloads. Couchbase Server provides multiple services, two of which are the Query Service and the Analytics Service. Make sure you enable both of these services in your Couchbase installation. After installing Couchbase Server, proceed with the following steps:

1 - Start the Web Console and look at the Dashboard display. On the left side of the display you will see a list of services that can be invoked from the Dashboard, including Buckets, Query, and Analytics.

2 - Click on Buckets and use the Add Buckets feature to create two new buckets called customers and orders. For each bucket, set Bucket Type to Couchbase and set the Memory Quota to the minimum (100MB).

3 - Click on Query to invoke the Query Editor. Copy and paste an SQL INSERT statement into the Query editor and click Execute to load the customers data into Couchbase.

4 - Next, copy and paste an SQL INSERT statement from here into the Query Editor and click Execute to load the orders data into Couchbase.

5 - The example data is now loaded into the Query Service, which is used for operational data. We need the data to be replicated in the Analytics Service, where we can query it using N1QL for Analytics, the Couchbase implementation of SQL++. To make this replication happen, click Analytics on the left side of the Dashboard, and type the following commands into the Analytics Query Editor (click Execute after each):

create dataset on customers;
create dataset on orders;
connect link Local;


6 - Now you are ready to run some example queries. Simply copy a query from here, paste it into the Analytics Query Editor, and click Execute. The result may not be exactly the same as shown in the Tutorial, because the order of fields inside objects may not match the order of fields in the SELECT clause. Try some experiments by modifying the example queries and making up some queries of your own!

7 - This is an optional step. The example data is stored in both the Query Service and the Analytics Service. You've been running queries on the Analytics Service, using N1QL for Analytics, an implementation of SQL++. To query the same data on the Query Service, click Query on the left side of the Dashboard to return to the Query Editor, and execute the following commands to create two indexes:

create primary index on customers;
create primary index on orders;


Now you can type queries against the example data into the Query Editor and execute them. The Query Service and the Analytics Service support two slightly different query languages, called N1QL for Query and N1QL for Analytics. Because of differences between these languages, some of the example queries will not run on the Query Service. To run queries on the Query Service, use N1QL for Query, documented elsewhere on the Couchbase website.

8 - If you wish to delete the example data from your system, simply retrace your steps. Execute the following commands on the Analytics Service:

disconnect link Local;
drop dataset customers;
drop dataset orders;


Then click Buckets on the left side of the Dashboard, click on each of the example buckets (customers and orders) and click Delete for each bucket.




Query Example Data
                                                      (Q1)
SELECT custid, name, address.zipcode, rating
FROM customers
ORDER BY custid;

(Q2)
SELECT c.name, c.rating	
FROM customers AS c
WHERE c.custid = "C41";

(Q3)
SELECT name
FROM customers
WHERE rating > 650;

(Q4)
SELECT VALUE name
FROM customers
WHERE rating > 650;

(Q5)
SELECT VALUE [name, rating]
FROM customers
WHERE rating > 650;

(Q6)
SELECT VALUE 
  {"high-rated customers, ordered by rating":
      (SELECT c.rating, c.custid, c.name
       FROM customers AS c
       WHERE c.rating > 650
       ORDER BY c.rating DESC),
  "high-rated customers, ordered by zipcode":
      (SELECT c.address.zipcode, c.custid, c.name
       FROM customers AS c
       WHERE c.rating > 650
       ORDER BY c.address.zipcode)
  };

(Q7)
SELECT name
FROM customers
WHERE rating = 
  (SELECT MAX(rating) FROM customers);
  
(Q8)
SELECT c1.name
FROM customers AS c1
WHERE c1.rating = 
   (SELECT MAX(c2.rating)
    FROM customers AS c2);
  
(Q9)
SELECT c1.name
FROM customers AS c1
WHERE c1.rating IN
   (SELECT VALUE MAX(c2.rating) FROM customers AS c2);

(Q10)
SELECT c1.name
FROM customers AS c1
WHERE c1.rating >
   (SELECT VALUE AVG(c2.rating) FROM customers AS c2)[0];

(Q11)
SELECT VALUE c1.name
FROM customers AS c1
WHERE c1.rating = 
   (SELECT VALUE MAX(c2.rating) FROM customers AS c2)[0];

(Q12)
SELECT VALUE c1.name
FROM customers AS c1
WHERE EVERY r IN
   (SELECT VALUE c2.rating FROM customers AS c2)
SATISFIES c1.rating >= r;

(On N1QL, add END after SATISFIES clause)

(Q13)
SELECT VALUE c1.name
FROM customers AS c1
WHERE EVERY r IN
   (SELECT VALUE c2.rating
    FROM customers AS c2
    WHERE c2.rating IS KNOWN)
SATISFIES c1.rating >= r;

(On N1QL, add END after SATISFIES clause)

(Q14)
FROM customers
WHERE address.zipcode = "63101"
SELECT custid AS customer_id, name
ORDER BY customer_id;

(Q15) 
FROM customers AS c 
WHERE c.address.zipcode = "63101"
SELECT c.custid AS customer_id, c.name
ORDER BY customer_id;
									        
                                                      (Q16)
FROM customers AS c, orders AS o
WHERE c.custid = o.custid
AND o.orderno = 1001
SELECT o.orderno,
       c.name AS customer_name, 
       c.address,
       o.items AS items_ordered;

(Q17)
FROM customers AS c JOIN orders AS o 
  ON c.custid = o.custid
WHERE o.orderno = 1001
SELECT o.orderno, 
       c.name AS customer_name,
       c.address,
       o.items AS items_ordered;

(Q18)
FROM customers AS c 
     LEFT OUTER JOIN orders AS o ON c.custid = o.custid
SELECT c.custid, c.name, o.orderno, o.order_date
ORDER BY c.custid, o.order_date;

(Q19) 
FROM orders AS o, o.items AS i
WHERE i.qty > 100
SELECT o.orderno, o.order_date, i.itemno AS item_number,
       i.qty AS quantity
ORDER BY o.orderno, item_number;
       
(Q20)
FROM orders AS o UNNEST o.items AS i
WHERE i.qty > 100
SELECT o.orderno, o.order_date, i.itemno AS item_number,
       i.qty AS quantity
ORDER BY o.orderno, item_number;
       
(Q21a)
FROM orders AS o, o.items AS i, customers AS c
WHERE o.custid = c.custid
AND i.itemno = 680
SELECT c.custid, c.name, o.order_date AS date
ORDER BY c.custid, date;  
       
(Q21b) 
FROM orders AS o, customers AS c
WHERE o.custid = c.custid
AND EXISTS
     (SELECT i.itemno
	  FROM o.items AS i
	  WHERE i.itemno = 680)
SELECT c.custid, c.name, o.order_date AS date
ORDER BY c.custid, date;   
 
(Q22)
FROM orders AS o
LET days = DATE_DIFF_STR(o.ship_date, o.order_date, "day")
WHERE days > 2
SELECT o.orderno, days - 2 AS days_late
ORDER BY days_late DESC;

(Q23)
FROM orders AS o
GROUP BY o.custid
SELECT o.custid, COUNT(o.orderno) AS `order count`
ORDER BY o.custid;

(Q24)
FROM customers AS c 
     LEFT OUTER JOIN orders AS o ON c.custid = o.custid
GROUP BY c.custid, c.name
SELECT c.custid, c.name, COUNT(o.orderno) AS `order count`
ORDER BY c.custid;

(Q25)
FROM orders AS o
WHERE DATE_PART_STR(o.order_date, "year") = 2017
GROUP BY DATE_PART_STR(o.order_date, "month") AS month
SELECT month, COUNT(*) AS order_count
ORDER BY month;

(Q26)
FROM orders AS o, o.items as i
GROUP BY o.orderno
SELECT o.orderno, SUM(i.qty * i.price) AS revenue
ORDER BY o.orderno;

(Q27)
FROM orders AS o, o.items as i
GROUP BY o.orderno
LET revenue = SUM(i.qty * i.price)
HAVING revenue > 1000
SELECT o.orderno, revenue
ORDER BY revenue DESC;

(Q28)
FROM orders AS o
LET revenue = 
   (FROM o.items AS i
   SELECT VALUE SUM(i.qty * i.price))[0]
WHERE revenue > 1000
SELECT o.orderno, revenue
ORDER BY revenue DESC;

(Q29)
FROM orders AS o, o.items AS i, customers AS c
WHERE o.custid = c.custid
GROUP BY o.orderno AS `order no.`, 
         o.order_date AS date, c.name, c.address
SELECT `order no.`, date, c.name, c.address,
   	SUM(i.qty * i.price) AS `amount due`
ORDER BY `order no.`;

(Q30)
FROM orders AS o, o.items AS i
GROUP BY o.orderno, o.order_date
HAVING SUM(i.qty * i.price) > 10000
SELECT o.orderno AS order_number, o.order_date, 
       SUM(i.qty * i.price) AS revenue
ORDER BY revenue DESC;
									        
                                                      (Q31)
FROM orders AS o, o.items AS i
GROUP BY o.orderno, o.order_date
LET revenue =  SUM(i.qty * i.price) 
HAVING revenue > 10000
SELECT o.orderno AS order_number, o.order_date, revenue
ORDER BY revenue DESC;

(Q32)
FROM customers AS c
GROUP BY c.address.zipcode AS zip
SELECT zip, AVG(c.rating) AS `avg credit rating`
ORDER BY zip;

(Q33)
FROM customers AS c
SELECT AVG(c.rating) AS `avg credit rating`;

(Q34)
SELECT ARRAY_AVG(
   (SELECT VALUE c.rating
    FROM customers AS c) ) AS `avg credit rating`;
    
(Q35)
FROM orders AS o
LET revenue = ARRAY_SUM(
       (FROM o.items AS i
        SELECT VALUE i.qty * i.price) )
WHERE revenue > 10000
SELECT o.orderno AS order_number, o.order_date, revenue
ORDER BY revenue DESC;

(Q36)
FROM orders AS o
WHERE DATE_PART_STR(o.order_date, "year") = 2017
AND DATE_PART_STR(o.order_date, "month") = 9
SELECT o.orderno, ARRAY_COUNT(o.items) AS line_items
ORDER BY o.orderno; 

(Q37) 
FROM orders AS o, o.items AS i
WHERE DATE_PART_STR(o.order_date, "year") = 2017
AND DATE_PART_STR(o.order_date, "month") = 9
GROUP BY o.orderno
SELECT o.orderno, COUNT(i) AS line_items
ORDER BY o.orderno; 

(Q38a) 
FROM customers AS c
GROUP BY c.address.zipcode AS zip
GROUP AS g
SELECT zip, AVG(c.rating) AS `avg credit rating`,
   (FROM g AS gi
    SELECT gi.c.custid, gi.c.name
    ORDER BY gi.c.custid) AS `local customers`
ORDER BY zip;

(Q38b)
FROM customers AS c
GROUP BY c.address.zipcode AS zip
GROUP AS g
SELECT zip, AVG(c.rating) AS `avg credit rating`,
   (FROM g 
    SELECT c.custid, c.name
    ORDER BY c.custid) AS `local customers`
ORDER BY zip;

(Q39) 
FROM customers AS c
GROUP BY c.address.zipcode AS zip
GROUP AS g
LET `best rating` = MAX(c.rating)
SELECT zip, `best rating`,
   (FROM g AS gi
    WHERE gi.c.rating = `best rating`
    SELECT gi.c.custid, gi.c.name
    ORDER BY gi.c.custid) AS `best customers`
ORDER BY zip;

(Q40) 
FROM customers AS c
GROUP BY c.address.zipcode AS zip
GROUP AS g
LET `best rating` = MAX(c.rating),
    `best customers` =
       (FROM g AS gi
        WHERE gi.c.rating = `best rating`
        SELECT gi.c.custid, gi.c.name
        ORDER BY gi.c.custid)
SELECT zip, `best rating`, `best customers`
ORDER BY zip;

(Q41) 
FROM customers AS c
GROUP BY c.address.zipcode AS zip 
GROUP AS g
SELECT zip, 
   ARRAY_AVG((FROM g AS gi SELECT VALUE gi.c.rating)) 
       AS `avg credit rating`
ORDER BY zip;

(Q42)
FROM orders AS o, o.items AS i
GROUP BY o.order_date
GROUP AS g
LET revenue = SUM(i.price * i.qty)
HAVING revenue > 1000.00
SELECT o.order_date AS `good day`, revenue,
   (FROM g AS gi 
    WHERE gi.i.price > 100.00
    SELECT gi.i.itemno, gi.i.price 
    ORDER BY gi.i.itemno) AS `expensive items`
ORDER BY o.order_date;

(Q43)
FROM customers AS c, orders AS o, o.items AS i
WHERE c.custid = o.custid
GROUP BY c.custid, c.name
GROUP AS g
SELECT c.custid, c.name,
	(FROM g AS gi
 	 SELECT gi.o.order_date, gi.i.itemno, gi.i.qty
 	 ORDER BY gi.o.order_date, gi.i.itemno) AS recent_items
ORDER BY c.custid;

(Q44)
FROM customers AS c LEFT OUTER JOIN
   /* This subquery unnests the items in each order, 
  	 returning an array of results named sq */
   (FROM orders AS o, o.items AS i
    SELECT o.custid, o.order_date, i.itemno, i.qty) AS sq
ON c.custid = sq.custid
GROUP BY c.custid, c.name
GROUP AS g
SELECT c.custid, c.name,
      (FROM g AS gi
       SELECT gi.sq.order_date, gi.sq.itemno, gi.sq.qty
       ORDER BY gi.sq.order_date, gi.sq.itemno) 
       AS recent_items
ORDER BY c.custid;

(Q45)
FROM orders AS o, o.items AS i
GROUP BY i.itemno
GROUP AS g
LET total_on_order = SUM(i.qty)
SELECT i.itemno, total_on_order,
    (FROM g AS gi
     SELECT gi.o.order_date, gi.o.custid
     ORDER BY gi.o.order_date) AS purchasers
ORDER BY i.itemno;
									        
                                                      (Q46) 
SELECT DISTINCT VALUE i.itemno
FROM orders AS o, o.items AS i
WHERE o.custid IN 
    (SELECT VALUE c.custid 
     FROM customers AS c 
     WHERE c.name = "T. Cruise");
   
(Q47) 
FROM customers AS c
GROUP BY c.address.zipcode AS zip
SELECT zip, ROUND(AVG(c.rating)) AS avg_rating
ORDER BY zip;
   
(Q48)
SELECT VALUE { "Average credit rating by zipcode" :
   (FROM customers AS c 
    WHERE c.address.zipcode IS KNOWN
    GROUP BY c.address.zipcode AS zip
    SELECT VALUE { zip : ROUND(AVG(c.rating)) }
    ORDER BY zip 
   )
};

(Q49)
FROM customers AS c
WHERE c.address.zipcode = "02115"
SELECT *;

(Q50)
FROM customers AS c
WHERE c.address.zipcode = "02115"
SELECT c.*;

(Q51)
FROM customers AS c, orders AS o
WHERE c.custid = o.custid
AND c.address.zipcode = "02115"
SELECT *;

(Q52)
FROM customers AS c
WHERE c.address.zipcode = "02115"
SELECT c.*, "Northeast" AS region;

(Q53)
FROM customers AS c
LET credit =
    CASE 
      WHEN c.rating > 650 THEN "Good"
      WHEN c.rating BETWEEN 500 AND 649 THEN "Fair"
      ELSE "Poor" 
    END
WHERE c.address.zipcode = "02115"
SELECT c.*, credit; 
      
(Q54) 
WITH order_revenue AS
  (FROM orders AS o, o.items AS i
   WHERE DATE_PART_STR(o.order_date, "year") = 2017 
   GROUP BY o.orderno
   SELECT SUM(i.qty * i.price) AS revenue
  )
FROM order_revenue
SELECT ROUND(MIN(revenue)) AS minimum,
       ROUND(MAX(revenue)) AS maximum,
       ROUND(AVG(revenue)) AS average;
   
(Q55) 
SELECT c.custid, "Unknown zipcode" AS reason
FROM customers AS c
WHERE c.address.zipcode IS NOT KNOWN

UNION ALL

SELECT o.custid, "Big order" AS reason
FROM orders AS o
WHERE ARRAY_COUNT(o.items) > 3
ORDER BY reason;
									        
Example Data
                                                      insert into customers (key, value)
values ("C13",

  { "custid": "C13",
    "name": "T. Cruise",
    "address": 
       { "street": "201 Main St.",
         "city": "St. Louis, MO",
         "zipcode": "63101"
       },
    "rating": 750
  }
  
), ("C25", 

  { "custid": "C25",
    "name": "M. Streep",
    "address": 
       { "street": "690 River St.",
         "city": "Hanover, MA",
         "zipcode": "02340"
       }, 
    "rating": 690
  }
  
), ("C31",

  { "custid": "C31",
    "name": "B. Pitt",
    "address": 
       { "street": "360 Mountain Ave.",
         "city": "St. Louis, MO",
         "zipcode": "63101"
       }
  }
  
), ("C35",

  { "custid": "C35",
    "name": "J. Roberts",
    "address": 
       { "street": "420 Green St.",
         "city": "Boston, MA",
         "zipcode": "02115"
       },
    "rating": 565 
  }
  
), ("C37", 

  { "custid": "C37",
    "name": "T. Hanks",
    "address": 
       { "street": "120 Harbor Blvd.",
         "city": "Boston, MA",
         "zipcode": "02115"
       }, 
    "rating": 750
  }
  
), ("C41", 

  { "custid": "C41",
    "name": "R. Duvall",
    "address": 
       { "street": "150 Market St.",
         "city": "St. Louis, MO",
         "zipcode": "63101"
       },
    "rating": 640
  }

), ("C47", 

  { "custid": "C47",
    "name": "S. Lauren",
    "address": 
       { "street": "17 Rue d'Antibes",
         "city": "Cannes, France"
       },
    "rating": 625
  }
       
);
									        
                                                      
insert into orders (key, value)
values ( "1001",

  { "orderno": 1001,
    "custid": "C41",
    "order_date": "2017-04-29",
    "ship_date": "2017-05-03",
    "items": [ { "itemno": 347,
                 "qty": 5,
                 "price": 19.99
               },
               { "itemno": 193,
                 "qty": 2,
                 "price": 28.89
               }
             ]
  }
  
), ("1002", 
   
  { "orderno": 1002,
    "custid": "C13",
    "order_date": "2017-05-01",
    "ship_date": "2017-05-03",
    "items": [ { "itemno": 460,
                 "qty": 95,
                 "price": 100.99
               },
               { "itemno": 680,
                 "qty": 150,
                 "price": 8.75
               }
             ]
  }
  
), ("1003",

  { "orderno": 1003,
    "custid": "C31",
    "order_date": "2017-06-15",
    "ship_date": "2017-06-16",
    "items": [ { "itemno": 120,
                 "qty": 2,
                 "price": 88.99
               },
               { "itemno": 460,
                 "qty": 3,
                 "price": 99.99
               }
             ]
  }
  
), ("1004", 

  { "orderno": 1004,
    "custid": "C35",
    "order_date": "2017-07-10",
    "ship_date": "2017-07-15",
    "items": [ { "itemno": 680,
                 "qty": 6,
                 "price": 9.99
               },
               { "itemno": 195,
                 "qty": 4,
                 "price": 35.00
               }
             ]
  }
  
), ("1005", 

  { "orderno": 1005,
    "custid": "C37",
    "order_date": "2017-08-30",
    "items": [ { "itemno": 460,
                 "qty": 2,
                 "price": 99.98
               },
               { "itemno": 347,
                 "qty": 120,
                 "price": 22.00
               },
               { "itemno": 780,
                 "qty": 1,
                 "price": 1500.00
               },
               { "itemno": 375,
                 "qty": 2,
                 "price": 149.98
               }
             ]
  }
  
), ("1006", 

  { "orderno": 1006,
    "custid": "C41",
    "order_date": "2017-09-02",
    "ship_date": "2017-09-04",
    "items": [ { "itemno": 680,
                 "qty": 51,
                 "price": 25.98
               },
               { "itemno": 120,
                 "qty": 65,
                 "price": 85.00
               },
               { "itemno": 460,
                 "qty": 120,
                 "price": 99.98
               }
             ]
  }

), ("1007",

  { "orderno": 1007,
    "custid": "C13",
    "order_date": "2017-09-13",
    "ship_date": "2017-09-20",
    "items": [ { "itemno": 185,
                 "qty": 5,
                 "price": 21.99
               },
               { "itemno": 680,
                 "qty": 1,
                 "price": 20.50
               }
             ]
  }
  
), ("1008",

  { "orderno": 1008,
    "custid": "C13",
    "order_date": "2017-10-13",
    "items": [ { "itemno": 460,
                 "qty": 20,
                 "price": 99.99
               }
             ]
  }


);