How to skip "_sync:rev:<docID>:<rev>" data from N1QL

Mobile Sync Gateway
1

I am using Couchbase Sync Gateway 1.2.0 and Couchbase Server 4.1.1.
When I query data with N1QL, I can receive some data with start with “_sync:rev:”, but this is not what I want to query.
I think this document is generated by Sync Gateway temporary for document acl when document is changed.
@adamf can you explain what this document use for?
Now I have some N1QL with some JOIN clause,and I don’t want to get all document which document id is start with “_sync:rev:”，Is there a good way to skip all document which document id is start with “_sync:rev:”?

2

I don’t know how to use N1QL (there are other forums here for that), but I can say that any key in the bucket that starts with _sync: is for internal use by Sync Gateway and should be ignored. (Just like anything in the _sync property in a document.)

1 Like
3

yes, for ignore _sync property in a document, I got the following easy way:

select meta().id,object_remove(default,"_sync").*  from default;

Note:only supported from CB 4.5 beta.

but I don’t know if there is a easy way to ignore any key in the bucket that start with _sync.

4

@atom_yang GW temporary documents are a problem for me too. It seems it can be worked around by using WHERE clause.

The query below does not return results from documents with id that starts with _sync but other matching documents are included.

SELECT meta(`example-bucket`).id FROM `example-bucket` USE INDEX (exampleindex2 USING GSI) WHERE meta(`example-bucket`).id NOT LIKE "_sync%" AND company="test" ;

As I’m using an index I would like to skip adding them to the index in the first place. However, it seems that if I add a similar WHERE clause to the CREATE INDEX statement there’s never a single result in SELECT statements.

This index gives results:

CREATE INDEX exampleindex2 ON `example-bucket`(company, timestamp) USING GSI ;

This index does not give results but queries are executed successfully:

CREATE INDEX exampleindex ON `example-bucket`(company, timestamp) WHERE meta(`example-bucket`).id NOT LIKE "_sync%" USING GSI ;

Hopefully this is helpful. I used 4.5 beta.

Edit:

It seems that the index below gives results and ignores GW temporary documents.

CREATE INDEX exampleindex3 ON `example-bucket`(company, timestamp) WHERE meta().id NOT LIKE "_sync%" USING GSI ;

The key was to omit the bucket name in the WHERE condition.

5

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
6

Here is mine
select meta().id from bucket and meta().id not like ‘%_sync%’