How to query documents by a field other than id?

For example I have documents with the following schema:

{
  "status": "done",
  "id": 1
}

I want to retrieve all documents with value done for the key status.

This is a use case for N1QL queries. Here is a simplified example.

CREATE INDEX idx_status ON default (status)
SELECT default.* FROM default WHERE status = 'done'
2 Likes

Not OP but as I understand it, N1QL is only querying indexes that eventually equal the source data and should be avoided in applications that do a lot of data editing. If you need to do this is it required to create those secondary documents that tie an ID to the field you want to query? There is no way to do a get on the data other than the ID without creating extra documents?

I am not sure I understand what you mean. Can you please elaborate?

This topic can get a bit complicated, let me see if I can lay it out.

Indexes are updated asynchronously in Couchbase. This means that when you change or add a document, if you immediately issue a query the results may or may not reflect that change. This is different than getting documents by primary key, which will always be up-to-date.

However, in most use case scenarios this isn’t a major factor. It’s just important to understand your use case. Often, a lag of a second or two before another user sees the change you just made isn’t important to the system. Where this lag is a problem, you can force your query to wait for index updates, though this incurs performance penalties.

Additionally, the processing load to keep indexes up-to-date can be a problem in very-high mutation scenarios. But I wouldn’t expect this to be a huge issue unless you’re doing something on the scale of hundreds of thousands of mutations a second or more, and horizontal scaling and index partitioning can help with a lot of that as well.

All of that said, denormalizing your data using what I call “lookup documents” is a valid procedure for scenarios requiring particularly high performance. In this scenario, you store documents keyed off a secondary field that have pointers to the primary keys of the main document. However, this scenario adds its own complexities around consistency during mutations across multiple documents, since only updates to a single document are atomic.

Brant

2 Likes