Use N1QL when you’re in a JSON pickle. — Confucius

For the JSON data model, the advice is to think of collections as tables, JSON document as denormalized rows and field names as columns – roughly. All this holds in databases like Couchbase and MongoDB when the recommendations are strictly followed. There are many reasons why users don’t simply follow this key-value pair model all the time. Here are the main reasons. 

    1. JSON is too verbose.
    2. You want to convert a map/hashmap data structure where the keys are dynamic.
    3. Timeseries data when the field names are usually encoded timestamps.
    4. Dictionary based encoding
    5. Existing document formats and standards disallow redesign

If your database and query language the query language doesn’t deal with the situation, you’ve to go through an elaborate redesign.  In addition to simply accessing the information, how do you make the queries on JSON efficient when you don’t even know the name of the field you’ve to index on? Fortunately, Couchbase N1QL has a variety of query and index features to deal with flexible metadata as well.

Let’s consider these use cases.

Use Case 1: Value transformation.

Here’s a sample JSON document.

JSON data model is described simply as a set of key-value pairs.  Each key is a string, unique at that its level of the hierarchy and value can be scalars, objects or arrays.  A rigorous definition can be read here. JSON is also self-describing and that makes it flexible for a database document model. Not every customer has to have a fixed number of telephone numbers or cars or any other type of attributes. 

The same information above can be reorganized as the JSON below without loss of information, but some implicit sche

This is all well and good if you’re simply putting and setting the document.  It doesn’t matter what the structure of JSON. Simply schlep is back and forth.

Now, let’s see how this affects querying.

Q1: SELECT * FROM customers WHERE cxname = “Jane Smith”;

With the new JSON model, there isn’t a field name called cxname here.

What the magic of object_pairs() function?  This transforms the JSON {“key”:”value”} pairs into an array of name-value pairs.  Here’s an example.

The OBJECT_NAMES() function extracts the key (here “Jane Smith”) and returns as a value, which then can be indexed. Since the function returns not just one value, but an array of “key names” as values, you need to create an array index.  Queries Q1 and Q2 do the same job for the respective data model.  But, we need each of those queries to execute in milliseconds. 

For Q1, we simply create the index on cxname.

CREATE INDEX ix_cxname ON customers(cxname)

For Q2,  


With this index, for Q2, you’ll get a plan that uses the index.


Use Case 2: Dynamic Key names.

This is use case is taken from the Couchbase forum post.

Question: What would be the best way to index the values within translations dynamically? I.e. a generic index that indexes all keys within the translations object.

If the need is to simply query for English documents all the time,  to query all documents that have translations.en = “Hello”.

If you’re always looking for translations to English, you can simply create the index on transactions.en.

If the keys are dynamic, you don’t know what specific language is going to be in the data and which ones can be queries upon, you’ve to make them both dynamic.

Here’s the explain to verify that the index is indeed picked up and the predicates are pushed down to the index scan.

Not to worry, if the index definition looks a bit more complicated than normal.  The Index Advisor has your covered.

You can even add expressions on top of each expression you’re evaluating.


More Object Functions

N1QL has additional object and nested data functions to help with complex data models.  Check out the full set of object functions and the token functions.


  1. Couchbae N1QL Object Functions Documentation
  2. Couchbase Array indexing 
  3. Couchbase index blog


Posted by Keshav Murthy

Keshav Murthy is a Vice President at Couchbase R&D. Previously, he was at MapR, IBM, Informix, Sybase, with more than 20 years of experience in database design & development. He lead the SQL and NoSQL R&D team at IBM Informix. He has received two President's Club awards at Couchbase, two Outstanding Technical Achievement Awards at IBM. Keshav has a bachelor's degree in Computer Science and Engineering from the University of Mysore, India, holds ten US patents and has three US patents pending.

Leave a reply