Index on nested array inside a traditional array VS Index on a nested array inside object_pairs

SQL++
, ,
1

We have a requirement to create index on data.name and data.value (please refer the documents below)
ID field would be a dynamic value with no specific pattern. Our requirement is to create a write intensive API which will both create the data along side respective ID element, and add/append new name & value pairs in the data array.
We have the below variants in mind, could you help us in determining the best indexing strategy for our requirement, and also which variant to chose and why? Like is there any drawback to appending to nested array elements?
(any updates to the below schema variants will be welcome)

Variant 1:
{
“values”:[
{
“ID”:“item_1”,
“data”:[
{
“name”:“data_1”,
“value”:“A”
},
{
“name”:“data_2”,
“value”:“XYZ”
}
]
},
{
“ID”:“item_2”,
“data”:[
{
“name”:“data_1”,
“value”:“123”
},
{
“name”:“data_2”,
“value”:“A23”
}
]
}
]
}

Variant 2(Here object_pairs will be used on “values” so that values[a dynamic-key: value pair] becomes an array that can be indexed):
{
“values”:{
“item_1”:{
“data”:[
{
“name”:“data_1”,
“value”:“A”
},
{
“name”:“data_2”,
“value”:“XYZ”
}
]
},
“item_2”:{
“data”:[
{
“name”:“data_1”,
“value”:“123”
},
{
“name”:“data_2”,
“value”:“A23”
}
]
}
}
}

2

Both of them can use nested array indexing https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/indexing-arrays.html

3

Is there any downside of using object_pairs in indexes?

4

OBJECT_PAIRS() is function where ever used it needs to transform OBJECT to ARRAY. It will be overhead.

5

Suppose I create an index on Object_pair(values).val.data, will my index store the “values” field as an array(with elements name for ID and val for data due to object_pair). If so, and also if my n1ql query is a covered query(fetching only Object_pair(values).val.data via select clause), will there still be a performance overhead(because I am under the impression that in the above case, as index would already contain “values” field as an array, no actual object_pair transformation would take place hence avoiding the overhead. Only in the case of a non-covered query will the actual document be accessed and object_pair transformation done on “values” field)

Couchbase document:
{
“values”: {
“item_1”: {
“data”: [{
“name”: “data_1”,
“value”: “A”
},
{
“name”: “data_2”,
“value”: “XYZ”
}
]
},
“item_2”: {
“data”: [{
“name”: “data_1”,
“value”: “123”
},
{
“name”: “data_2”,
“value”: “A23”
}
]
}
}
}

6

Array index can only store single field. Multiple nested arrays may not cover. Please explain what your query looks.
If you create index on Object_pair(values).val.data querying is difficult.

Checkout https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/indexing-arrays.html

7

What could be the difficulty we’ll face if we do create indexes on object_pairs(values)
Also, what do you mean when you say array index can only store a single field?
Could I have more than 1 array indexes in 1 index?
What all data is stored via array indexes and how, like is there a specific format/structure?

Our query would look something like below:
SELECT ARRAY { value.name, value.val.data} FOR value IN object_pairs(values) END as values_array FROM bucket`

8

only one array index key per index.Index selection is based on predicate. Above query must use primary index.

I would recommend read through documentation first

Array Indexing | Couchbase Docs

Check slides 43-60 https://www.slideshare.net/Couchbase/utilizing-arrays-modeling-indexing-and-querying