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.
-
- JSON is too verbose.
- You want to convert a map/hashmap data structure where the keys are dynamic.
- Timeseries data when the field names are usually encoded timestamps.
- Dictionary based encoding
- 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
{ "cname": "Jane Smith", "dob" : "1990-01-30", "phones" : [ "+1 510-523-3529", "+1 650-392-4923" ], "billing": [ { "type": "visa", "cardnum": "5827-2842-2847-3909", "expiry": "2019-03" }, { "type": "master", "cardnum": "6274-2542-5847-3949", "expiry": "2018-12" } ] } |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
{ "Jane Smith": "1990-01-30", "home": "+1 510-523-3529", "office": "+1 650-392-4923", "Billing": [ { "visa": "5827-2842-2847-3909", "expiry": "2019-03" }, { "master": "6274-2542-5847-3949", "expiry": "2018-12" } ] } |
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.
1 2 3 4 |
Q2: SELECT p FROM people p WHERE ANY o IN object_names(p) SATISFIES o = "Jane Smith" END |
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.
1 2 3 4 5 6 7 8 9 |
SELECT OBJECT_NAMES({"Jane Smith": "1990-01-30", "home": "+1 510-523-3529"}) "$1": [ "Jane Smith", "home" ] } |
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,
CREATE INDEX ix_people ON people(DISTINCT OBJECT_NAMES(self))
With this index, for Q2, you’ll get a plan that uses the index.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
{ "#operator": "DistinctScan", "scan": { "#operator": "IndexScan3", "as": "p", "cardinality": 1, "cost": 0.273, "index": "ix_people", "index_id": "4a2df8dd85543aa4", "index_projection": { "primary_key": true }, "keyspace": "people", "namespace": "default", "spans": [ { "exact": true, "range": [ { "high": "\"Jane Smith\"", "inclusion": 3, "low": "\"Jane Smith\"" } ] } ], |
Use Case 2: Dynamic Key names.
This is use case is taken from the Couchbase forum post.
1 2 3 4 5 6 7 8 9 10 11 12 |
{ "id": "05a9b954-bdee-4d7f-9715-8e9e08f8cb75", "type": "article", "translations": { "en": "Hello", "de": "Hallo", "fr": "Bonjour", "es": "Hola" } } |
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.
1 2 3 4 |
CREATE INDEX ix_tren ON info(translations.en); SELECT * FROM info WHERE translation.en = “Hello”; |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
/* Query */ SELECT * FROM info WHERE ANY v IN OBJECT_PAIRS(translations) SATISFIES [v.name,v.val] = ["en", "Hello"] END /* Index */ CREATE INDEX ix_infoname ON info ( DISTINCT ARRAY [v.name, v.val ] FOR v IN OBJECT_PAIRS(translations) END ) |
Here’s the explain to verify that the index is indeed picked up and the predicates are pushed down to the index scan.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
{ "#operator": "DistinctScan", "scan": { "#operator": "IndexScan3", "cardinality": 0.5, "cost": 0.1665, "index": "ix_infoname", "index_id": "bebbfd22a022fb75", "index_projection": { "primary_key": true }, "keyspace": "info", "namespace": "default", "spans": [ { "exact": true, "range": [ { "high": "[\"en\", \"Hello\"]", "inclusion": 3, "low": "[\"en\", \"Hello\"]" } ] } ], "using": "gsi" } }, |
Not to worry, if the index definition looks a bit more complicated than normal. The Index Advisor has your covered.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
ADVISE SELECT * FROM info WHERE ANY v IN OBJECT_PAIRS(translations) SATISFIES [v.name,v.val] = ["en", "Hello"] END { "index_statement": "CREATE INDEX adv_DISTINCT_object_pairs_translations_name_val ON `info`(DISTINCT ARRAY [`v`.`name`, `v`.`val`] FOR v in object_pairs((`translations`)) END)", "keyspace_alias": "info", "recommending_rule": "Index keys follow order of predicate types: 2. equality/null/missing." } |
You can even add expressions on top of each expression you’re evaluating.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
ADVISE SELECT * FROM info WHERE ANY v IN OBJECT_PAIRS(translations) SATISFIES [LOWER(v.name),LOWER(v.val)] = ["en", "Hello"] END { "index_statement": "CREATE INDEX adv_DISTINCT_object_pairs_translations_lower_name_lower_val ON `info`(DISTINCT ARRAY [lower((`v`.`name`)), lower((`v`.`val`))] FOR v in object_pairs((`translations`)) END)", "keyspace_alias": "info", "recommending_rule": "Index keys follow order of predicate types: 2. equality/null/missing." } |
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