How to query all latest versions after implementing document versioning

SQL++
,
1

Hey, Couchbase beginner here.
I’m trying to implement document versioning. My idea was to index the document as {key}:{version} (similar to this blog article but without saving a copy of the newest version).
Querying the latest version for one id was straightforward (filter by id like {key}:% => order by id DESC => LIMIT 1) but querying the latest version for each key seems quite tricky. I can’t order by version after group by key because the version field is not part of the aggregate. Is this query possible?

Example:
Assuming the following data exists in the bucket

id | data
abc:0 | {"name":"User A", "key":"abc", "version":0}
abc:1 | {"name":"New Name", "key":"abc", "version":1}
def:0 | {"name":"User B", "key":"def", "version":0}

I want the query to return the second and third entry, but not the first (as there is a newer version for that key).

2

Hi @Heremeus ,

CREATE INDEX ix1 ON mybucket(`key`,version);

SELECT b.`key` ,  docid 
FROM mybucket AS b
WHERE b.`key` IS NOT NULL
GROUP BY b.`key` 
LETTING  docid = MAX([b.version,META(b).id])[1]
3

@vsr1 Thank you kindly, it’s working great.