N1QL enhancements in 4.5.1 – Part2

Simplify Transitioning from RDBMS

Couchbase Server 4.5.1 release brings multiple functionality, usability and performance improvements in N1QL to address needs of enterprise applications. These enhancements address many of our customer critical issues, and simplify Transitioning from RDBMS.

Try Couchbase Server 4.5.1.  Its ready for production, field tested by partners & customers. See what’s new for full list of enhancements and improvements.

Don’t miss part1 of the blog.

New query setting pretty

Pretty!! Pretty!! pretty!!  Yes, pretty is super impressive new query setting in N1QL that can enable or disable pretty formatting of query results. You might ask, what’s so great about it?? And, why anybody may want to disable the beautiful pretty formatting of the JSON output.

As the old adage goes, there are no free lunches. Pretty formatting query results comes with its own expense:

1. First, a quick fact is that the white space (tabs, spaces, newlines) characters in a pretty-formatted JSON document consume almost a third of it’s size.
   • So, simply cutting down on the beautification will save all the raw bytes flowing over the network.
   • Moreover, consider the corresponding saving on the memory and processing resources of N1QL service.
   • Altogether, savings are pretty significant.
2. The pretty output format is good for human readable scenarios, with human manageable result sizes.
   • However, real world applications and computer programs run queries much more often than humans, and process much bigger query results.
   • For these, what matters is performance and efficiency; not pretty formatting. In fact, such formatting is an overhead to the JSON parser and application, and is usually discarded. Typically, applications have their own presentation layer to format the data appropriately for respective users.
   • For example, consider a travel reservation website, where you are searching for flights. I am sure, no travel site dumps available flights as bunch of JSON documents.

The new query parameter pretty in 4.5.1 allows to enable/disable formatting a query result.  The parameter can be:

• set to true or false.
• Passed to CBQ engine as command line parameters
• Passed as Query parameter with REST calls.

By default, it is set to true. When set to false, the white space characters are stripped from the query results. The performance benefits are significantly visible when queries produce large results, and it of course depends on the percentage of white-space overhead in your documents. For example, following query which selects all documents from travel-sample, run almost 3x faster when pretty = false. Also, note the size of result set, which is one third of the pretty formatted result.

With pretty = true

With pretty = false

Note that the total resultSize now is only 36754457bytes, and the query run in 2.2sec.

Enhancements to dynamic object construction in N1QL queries

N1QL already supports creation of JSON objects dynamically in queries. This immensely helps in creating specifically constructed result objects in query projection-lists. Couchbase server 4.5.1 extends the power of expressions and enriches the dynamic object creation and object processing in N1QL queries.

1. In Couchbase Server 4.5.1, N1QL allows both names and values of object fields to be arbitrary expressions. In earlier versions, the names of fields are required to be static strings. If a name does not evaluate to a string, the result of the object construction is NULL. For example:
   
   SELECT { UPPER(callsign) || "_key" : callsign || ":" || country || ":" || name } AS myobj FROM `travel-sample` WHERE type = 'airline' limit 1; [ { "myobj": { "MILE-AIR_key": "MILE-AIR:United States:40-Mile Air" } } ]

   1 2 3 4 5 6 7 8 9 10

   SELECT { UPPER(callsign) || "_key" : callsign || ":" || country || ":" || name } AS myobj FROM `travel-sample` WHERE type = 'airline' limit 1; [   {     "myobj": {       "MILE-AIR_key": "MILE-AIR:United States:40-Mile Air"     }   } ]

2. When constructing objects in a N1QL query, the names of fields in name-value pairs is made optional in 4.5.1. For example, the following query implicitly assigns names “type” and “name” for respective values:
   
   SELECT {type, name} AS myobj FROM `travel-sample` LIMIT 1; [ { "myobj": { "type": "airport" "name": "airport_123" } } ]

   1 2 3 4 5 6 7 8 9

   SELECT {type, name} AS myobj FROM `travel-sample` LIMIT 1; [   {     "myobj": {       "type": "airport"       "name": "airport_123"     }   } ]

New array function ARRAY_INTERSECT()

ARRAY_INTERSECT() function takes two or more arrays as parameters and returns the intersection of the input arrays as the result, i.e the array containing values that are present in all the input arrays. It returns an empty array if there are no common array elements. For more information, see documentation. For example, following query finds the hotels that are liked by Brian or Lilian, in the travel-sample bucket shipped with Couchbase Server 4.5.1.

Download Couchbase Server 4.5.1 and give it a try. Let me know any questions/comments, or just how awesome it is ;-)
Cheers!!