Use N1QL when you're in a JSON pickle. - Confucius
For the JSON data model, the general advice is to think of collections as tables, JSON documents as denormalized rows, and field names as columns — roughly. All this holds true in databases like Couchbase and MongoDB when 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:
You want to convert a map/hashmap data structure where the keys are dynamic.
Timeseries data when the field names are usually encoded timestamps.
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 have 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.
The 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. 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 any loss of information, but some implicit schema.
This is all well and good if you're simply putting and setting the document. It doesn't matter what the structure of JSON is. Simply schlep it back and forth.
Now, let's see how this affects querying.
With the new JSON model, there isn't a field name called, cxname, here.
What is the magic of the 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 it as a value, which then can be indexed at. 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.
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 have to make them both dynamic.
Here's the explanation 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 you 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.
References:- Couchbae N1QL Object Functions Documentation.
- Couchbase Array indexing.
- Couchbase index blog.
Comments
Post a Comment