Indexing arrays in Couchbase 4.1

SQL++
1

Hi,
I am using couchbase 4.0.
As per what I could serach & read, there is no way to index arrays & nested arrays in couchbase 4.0.

We have a no. of documents which have arrays & nested arrays as data.

Doing a N1QL (using JOIN & UNNEST) for these type of documents takes a long time for the query to return results. The Couchbase Community Edition 4.5, which supports array indexing is not yet available.

Querries
Q1) Is there a way to a) index an array b) index individual array elements?
Q2) If there is no way to index array & nested array, what is the suggested change in json document structure (as below) to get data faster?
Q3) I am using the following N1QL which takes a very long time to return results, is there a better way for N1QL, I have created a GSI index on the field "type " on both types of documents mentioned below.

SELECT bkt1.type type1, bkt2.type type2, nwTrans.nwId, nwTrans.nwTranGroupId nwTransGrpId, nwTrans.tranId, nwTranGroups.nwId grpNwId, nwTranGroups.nwTranGroupId,
nwTranGroups.nwTranGroupStops
FROM devl bkt1
UNNEST bkt1.nwTrans
LEFT JOIN devl bkt2
ON KEYS ‘nwTranGroups::’ || TO_STRING(nwTrans.tranId)
UNNEST bkt2.nwTranGroups nwTranGroups
WHERE bkt1.type = 'trans’
AND bkt2.type = 'tranGroups’
AND nwTrans.nwId = 1
AND nwTrans.tranId = 12
AND nwTranGroups.nwId = 1
AND nwTranGroups.nwTranGroupId = 11

Q4) When is the Couchbase 4.5 Community Edition expected to be released?

The following are my json document structure, arrays in documents are in bold:

document 1 - nw::1
{
“type”: “trans”,
“nwTrans”: [
{
“nwId”: 1,
“nwTranGroupId”: 1,
“tranId”: 1
},
{
“nwId”: 1,
“nwTranGroupId”: 2,
“tranId”: 2
},
{
“nwId”: 1,
“nwTranGroupId”: 3,
“tranId”: 3
},
{
“nwId”: 1,
“nwTranGroupId”: 3,
“tranId”: 6
}
]
}

document 2 - nwTranGroups::1
{
“type”: “tranGroups”,
“nwTranGroups”: [
{
“nwId”: 1,
“nwTranGroupId”: 1,
“nwTranGroupStops”: [
{
“minSinceFirstStop”: 0,
“minSincePrevStop”: 0,
“nwGroupStopNum”: 1,
“nwStopNum”: 16
},
{
“minSinceFirstStop”: 3,
“minSincePrevStop”: 3,
“nwGroupStopNum”: 2,
“nwStopNum”: 17
},
{
“minSinceFirstStop”: 8,
“minSincePrevStop”: 5,
“nwGroupStopNum”: 3,
“nwStopNum”: 18
}
],
“startNwStopNum”: 16,
“travelDurationInMin”: 21
},
{
“nwId”: 1,
“nwTranGroupId”: 2,
“nwTranGroupStops”: [
{
“minSinceFirstStop”: 0,
“minSincePrevStop”: 0,
“nwGroupStopNum”: 1,
“nwStopNum”: 12
},
{
“minSinceFirstStop”: 3,
“minSincePrevStop”: 3,
“nwGroupStopNum”: 2,
“nwStopNum”: 13
},
{
“minSinceFirstStop”: 6,
“minSincePrevStop”: 3,
“nwGroupStopNum”: 3,
“nwStopNum”: 14
}
],
“startNwStopNum”: 12,
“travelDurationInMin”: 36
},
{
“nwId”: 1,
“nwTranGroupId”: 3,
“nwTranGroupStops”: [
{
“minSinceFirstStop”: 0,
“minSincePrevStop”: 0,
“nwGroupStopNum”: 1,
“nwStopNum”: 21
},
{
“minSinceFirstStop”: 3,
“minSincePrevStop”: 3,
“nwGroupStopNum”: 2,
“nwStopNum”: 22
}
],
“startNwStopNum”: 21,
“travelDurationInMin”: 36
}
]
}

Regards,
Sachin Vyas.

2

If you can’t use array indexing, you should pull out the elements of nwTranGroups into separate documents, and have them refer to the groups they belong to by id.

You would end up with three types of documents, I think, (nwTrans, nwTransGrou, nwTransGroupStop) normalized as they would be in a traditional relational database. That will allow indexing to work correctly, but it does mean that you’ll need to do nest operations when you want to bundle things up under nwTrans, as you have them now.

1 Like
3

Thanks johan_larson.

So, I am assuming that there is no way for Array indexing in Couchbase 4.0 or 4.1…right.

Any updates on when is the Couchbase 4.5 Community Edition expected to be released?

Regards,
Sachin Vyas.

4

No array indexes before 4.5, right.

4.5 CE should be out some time this year, probably before Christmas.

5

Thanks johan_larson.

Regards,
Sachin Vyas.