Hi, working on an Akka Persistence plugin for Couchbase and struggling with creating an index for nested arrays to be used. The documents looks like this:
{
"persistence_id":"...",
"messages": [
{
"ordering": "2018-12-20T09:10:53.368000000_13706215326216848386",
"tags": [
"tag-1"
]
}, ...
]
}
And we are trying to make a query for a specific “tag” in a range of “ordering”, we have it working with two indexes, one for tag
and one for ordering
, but realized that the paging that we do (using LIMIT
) doesn’t scale to the amount of events we expect, we hope that if we can achieve one combined index that would push the limit operation to the index (ofc we may be wrong about this).
Latest try to do a single index has been like this:
CREATE INDEX `tags-ordering` ON `akka`
(ALL ARRAY
(ALL ARRAY [t.tag, m.ordering]
FOR t IN m.tag_seq_nrs END)
FOR m IN messages END)
WHERE `type` = "journal_message"
But we can’t even get the simplest query to use this index:
SELECT a.persistence_id, m.* FROM akka a UNNEST messages m UNNEST m.tag_seq_nrs t
WHERE m.ordering > "2000-12-20T08:10:53.368000000_13706215326216848386"
AND a.type = "journal_message"
AND t.tag = "tag-1"
Would be awesome if someone could tell us what it is we are missing or have misunderstood.