CouchDB view query with multiple key values

Question

I am currently trying to create a view and query to fit this SQL query:

SELECT * FROM articles
WHERE articles.location="NY" OR articles.location="CA"
ORDER BY articles.release_date DESC

I tried to create a view with a complex key:

function(doc) { 
  if(doc.type == "Article") { 
    emit([doc.location, doc.release_date], doc) 
  }
}

And then using startkey and endkey to retrieve one location and ordering the result on the release date.

.../_view/articles?startkey=["NY", {}]&endkey=["NY"]&limit=5&descending=true

This works fine.

However, how can I send multiple startkeys and endkeys to my view in order to mimic

WHERE articles.location="NY" OR articles.location="CA" ?

While the answers are correct, it looks like this still isn't possible, it appears that this will be available whenever 2.0 comes out: issues.apache.org/jira/browse/COUCHDB-523 — Matt Grande
– Matt Grande, Commented May 14, 2015 at 15:06

JasonSmith · Accepted Answer · 2011-04-07 04:47:07Z

7

My arch nemesis, Dominic, is right.

Furthermore, it is never possible to query by criteria A and then sort by criteria B in CouchDB. In exchange for that inconvenience, CouchDB guarantees scalable, dependable, logarithmic query times. You have a choice.

Store the view output in its own database, and make a new view to sort by criteria B
or, sort the rows afterward, which can be either
- Sort client-side, once you receive the rows
- Sort server-side, in a _list function. The is great, but remember it's not ultimately scalable. If you have millions of rows, the _list function will probably crash.

answered Apr 7, 2011 at 4:47

JasonSmith

74k23 gold badges128 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ephemere Over a year ago

I plan to have at least 50 000 docs, so sorting on the client side is out of the question:) And _list functions won't scale... so the only 2 options left are to store the view output in its own database and make a new view, or use the Lucene search engine? How would solve this? Let's say I have 50 locations, I don't want to make 50 calls to my view and then iterate through the results to get the 10 most recent articles.

JasonSmith Over a year ago

@ephemere, A temporary database is not ideal, I know. However once you've got it, you can use it for any similar situation, select by A, sort by B. You'll just be fetching _view rows and turning them into _bulk_docs rows.

seb Over a year ago

@ephemere, would you mind clarifying the use cases? Searching for 10 most recent articles across all locations could be solved by a different view. Also, if you are searching for a limited number of docs (say, 10 most recent articles) across arbitrary selection of locations (user request perhaps) you could always issue one search per location but limit the returned result from each one. Fetching 10 docs from each location = 30 docs.

Dominic Barnes · Accepted Answer · 2012-06-28 17:14:04Z

6

The short answer is, you currently cannot use multiple startkey/endkey combinations.

You'll either have to make 2 separate queries, or you could always add on the lucene search engine to get much more robust searching capabilities.

It is possible to use multiple key parameters in a query. See the Couchbase CouchDB documentation on multi-document fetching.

edited Jun 28, 2012 at 17:14

answered Apr 6, 2011 at 19:46

Dominic Barnes

28.5k8 gold badges69 silver badges91 bronze badges

4 Comments

JasonSmith Over a year ago

+1. I took the liberty of linking to the docs about multiple key queries at the same time (kind of, but not quite an OR query).

ephemere Over a year ago

The problem with this solution is that I don't want to specify a date. Let's say I want the 10 most recent articles for NY or CA, how could I write this using keys? { "keys": [["NY", {}], ["CA", {}]] }

Cheeso Over a year ago

the page on multi-document fetching is 404. You may want this: couchbase.com/docs/couchbase-single-server-1-2/…

Jonathan Hall Over a year ago

Dead link in answer, and @Cheeso's link is also dead.

Collectives™ on Stack Overflow

CouchDB view query with multiple key values

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related