Select statement take past revisions into account

SQL++
1

Hi !

I’m using Couchbase Lite and Sync Gateway stuffs and I want to run some N1Ql queries through the Query Workbench. N1Ql is working fine but my SELECT clause is returning a result for each revision of a document (taking past revisions into account) and I would like to take only current revision into account.

Here is my N1QL query:

SELECT META(event).id  FROM MAIN_BUCKET event 
UNNEST event.elementValues elementValue
JOIN MAIN_BUCKET patient ON KEYS event.patientId
WHERE 
		event.`type` = "EVENT"
	AND elementValue.elementValueTypeId = "EVT_IS_ACNC"

And here is the result:

[
	{
		"id": "244dff6e-3535-4f9c-8cb5-0fe77c22b4b4"
	},
	{
		"id": "840b129d-0dff-4546-804f-2c4d6f7be165"
	},
	{
		"id": "_sync:rev:244dff6e-3535-4f9c-8cb5-0fe77c22b4b4:34:5-0605f614ccdd6b32d3a17881adb8e720"
	},
	{
		"id": "_sync:rev:244dff6e-3535-4f9c-8cb5-0fe77c22b4b4:34:6-2ba904d682ad6ee47d08b507094d0223"
	},
	{
		"id": "b42767e9-5252-4599-9ec8-3b562697cb1c"
	},
	{
		"id": "f4f67a04-a427-4117-b9ec-8048a39b644a"
	}
]

As we can see, there is 2 “sync:rev:…” ids. My initial query is a COUNT(*) and I don’t want those previous revision to messed up with my counts. Is there a way to tell N1QL to avoid taking previous revisions into account?

Note: I just saw that it happens only a few minutes (<5 minutes) after a revision has been created but re-running the query after this lapse of time won’t show these “_sync:rev…” ids. But still, it would mean that my counts are potentially erroneous if I run the query just after a new revision has been added to a document.

2

you should add condition to exclude “_sync” document, such as

SPLIT(META().`id`,":")[0] != "_sync"
3

Oh right, thank’s for the tip !

4

Here is my solution

SELECT META(n1).id as _id , n1.* ,  object_remove(n1 , \'_sync\').* 
, _sync.rev as _rev 
FROM test_db as n1 
WHERE n1._id IS NOT VALUED