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

KEEP CALM and QUERY JSON

DZone's Guide to

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.

· Database Zone
Free Resource

Learn NoSQL for free with hands-on sample code, example queries, tutorials, and more.  Brought to you in partnership with Couchbase.

Keep Calm and Query JSON


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. 

Image title

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.

Image title

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.

Image title


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. CassandraMongoDB, 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.

Image title

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!

Image title


The Getting Started with NoSQL Guide will get you hands-on with NoSQL in minutes with no coding needed. Brought to you in partnership with Couchbase.

Topics:
sql ,nosql ,json ,rdbms ,database ,query ,appdev ,couchbase ,mongodb ,schema

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

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

{{ parent.tldr }}

{{ parent.urlSource.name }}