KEEP CALM and QUERY JSON

[reposted from my dzone article: http://dzone.com/articles/keep-calm-and-query-json]

Trying to break from SQL? Here's how JSON and the platforms that use it can lend a hand organizing and querying data.

You've seen why and how to model data in JSON. Once you have the data in JSON, you can store and retrieve the JSON documents in any simple key-value databases. If your application only has to set and get the documents based on the key, use any key-value store. you're all set.

That's seldom the case.

You don't use a database just to store and retrieve JSON. You're trying to solve a real business problem. You're developing a business application. For a business application, the questions, operations, and reports don't change just because your database doesn't support query. Business applications have to do many things requiring multi-object operation:

• Process Order Checkout.
• Search stores data for the shoe customer is looking for.
• How many new customers did we get last month?
• Generate the outstanding list of shipments due for today.
• Retrieve the customer order using case insensitive customer name.
• Load the new inventory data.
• Merge customer lists.

For doing each of these tasks, you need to search your database efficiently. Do the select-join-group-project-aggregate-order processing of the data to produce a report. Similarly, you'll have to insert, update, delete, and merge to represent the real-world business processes. 

Now, the question is, how would you go about implementing all these for a NoSQL key-value systems with GET and SET APIs? You do this by writing a program to do each task. Of course, writing the application is expensive. Scaling, debugging, and maintaining it makes it even more expensive.

Imagine a use case: Find high-value customers with orders > $10,000. With a NoSQL API approach, you write a program to retrieve the objects and do the filtering, grouping, aggregation in your program. 

Now, the question is, how would you go about implementing all these in a NoSQL key-value systems with GET and SET APIs?   You do this by writing a program to do each task.  Of course, writing the application is expensive, scaling, debugging and maintaining it makes it even more expensive.

This is the reason RDBMS and SQL were invented. SQL does set processing naturally. On the relational model, each of these tasks can be done via a series of statements. Each SQL statement takes set of tuples, does the processing, and produces another set of tuples.

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

FROM Orders o   INNER JOIN LineItems l ON (o.CustID = l.CustID)   

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

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

ORDER BY SUM(o.Amount) DESC

As the NoSQL key-value databases evolved, they added the map-reduce frameworks so developers can use and cascade them to do complex work. Cassandra, MongoDB, and Couchbase introduced SQL-like languages on top distributed NoSQL database to make the query easier.  MongoDB API looks different, but you'll still see something like SQL.

Couchbase N1QL (Non-First Normal Form Query Language) provides all of the select-join-project operations in SQL on the flexible JSON data model. N1QL defines a four-valued boolean logic  for boolean expressions that include the new MISSING value in a flexible schema and the collation sequence for data types. In addition, N1QL extends SQL  to access and manipulate all parts of nested JSON structure. 

N1QL takes sets of JSON documents as input, processes them and gives you a set of JSON documents. Just replace tuples with JSON in SQL definition, you get N1QL.

Let's see how we can write the previous query in N1QL. Pretty close to SQL, but it has access to nested data and does the UNNESTING of arrays within ORDERS document.

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

FROM Orders UNNEST Orders.LineItems AS OrderLine

            INNER JOIN Customers ON KEYS Orders.CustID

GROUP BY Customers.ID, Customers.Name

HAVING SUM(OrderLine.Amount) > 10000

ORDER BY SUM(OrderLine.Amount) DESC

In addition to SELECT, Couchbase N1QL supports the traditional SQL statements: INSERT, UPDATE, DELETE, and MERGE. Here are some examples:

INSERT INTO ORDERS (KEY, VALUE)

VALUES ("1.ABC.X382", {"O_ID":482, "O_D_ID":3, "O_W_ID":4});

UPDATE ORDERS SET O_CARRIER_ID = ”ABC987” WHERE O_ID = 482 AND O_D_ID = 3 AND O_W_ID = 4

DELETE FROM NEW_ORDER WHERE NO_D_ID = 291 AND NO_W_ID = 3482 AND NO_O_ID = 2483

MERGE INTO CUSTOMER USING

      (SELECT CID FROM NEWCUST WHERE STATE = 'CA') as NC

ON KEY NC.CID  

     WHEN MATCHED THEN UPDATE SET CUSTOMER.STATE= 'CA';

Here's the comparison between SQL based RDBMS and N1QL:  

Query Features	SQL on RDBMS	N1QL: SQL on JSON
Objects	Tables, tuples, columns	Keyspaces, JSON Documents, key-value
References	Flat references:Table.column E.g. customer.name, customer.zip	Flat & Nested: keyspace.keyvalue E.g. customer.name, customer.address.zip, Customer.contact[0].phone.mobile
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 Look Ma! No Type Mismatch Errors! JSON keys act as columns
Schema	Predetermined Columns	Fully addressable JSON Flexible document structure
Data Types	SQL Data types Conversion Functions	JSON Data types: Strings, Number Boolean,  Objects, Arrays, null Conversion Functions
Query Processing	INPUT: Sets of Tuples OUPUT: Set of Tuples	INPUT: Sets of JSON OUTPUT: Set of JSON

Summary:

NoSQL databases give you the ability to read and write massive amounts of data at a substantially low latency and substantially low cost compared to RDBMS.Not having a query is like having a great engine without a steering wheel. With query on JSON from Couchbase, you can ask important questions like -- where can I find my favorite beer?.  

SELECT          {"Mybeer": "My Beer is " || beer.name || "."}, 
     Array_agg({"name":brewery.name})  brewery,
     Array_agg({"name":brewery.name, "state":brewery.state,                   "city":brewery.city, "location":brewery.geo})    locations, 
      Array_count(Array_agg(brewery.NAME))  AS brewery_count
FROM            `beer-sample` beer 
LEFT OUTER JOIN `beer-sample` brewery 
ON              keys beer.brewery_id 
WHERE           beer.type = 'beer' 
AND             brewery.type = 'brewery' 
AND             brewery.state = 'California' 
GROUP BY        beer.NAME 
ORDER BY        array_count(array_agg(brewery.NAME)) DESC, 
                                 beer.NAME ASC limit 5 ;

You have your steering wheel back, now!