Couchbase Multiple Keys

Question

I presume a simple question. I have the following data.

I want to search for all rows where the ID is > 2 but < 8 and the Price is > 30

I have used various versions of: startkey=["2", null] or even something like startkey=["2", "30"] just for testing.

It only ever seems to run both conditions on the first row. So if I do: startkey=["2", "30"] then I get back:

{"id":"3","key":["3","30"],"value":null},
{"id":"4","key":["4","30"],"value":null},
{"id":"5","key":["5","20"],"value":null},
{"id":"6","key":["6","60"],"value":null},
{"id":"8","key":["8","60"],"value":null}

Why is row 5 there?

I am starting to get the view that I need to handle this in the code (.net) and make multiple calls somehow... I can't seem to find anything on this that works....

Note: I have tried doing say a loop with for (i = 0; i < doc.ID.length; i++) and then using doc.ID[i] but it never returns anything....

Currently I just have

function (doc, meta) {
    emit([doc.ID, doc.Price ],null);
}

Essentially I want to have a search where there are 5 input keys that a user has. So do I need to make 5 calls and then keep taking data from the previous output as the source for the next???

Other references I have looked at include: the manual

Thanks in advance,

Kindest Regards Robin

Answer 1

This is a common misconception, with a compound array index key, it's still treated as a string, therefore the index key [2,10] is actually "[2,10]", and the index key [5,20], is actually "[5,20]".

So the reason that startkey=["2", "30"]shows the {"id":"5","key":["5","20"],"value":null}, row is because as a string it is > startkey.

Likewise, the Query startkey=[2,10]&endkey=[5,10] returns

{"total_rows":7,"rows":[
  {"id":"2","key":[2,20],"value":null},
  {"id":"3","key":[3,30],"value":null},
  {"id":"4","key":[4,30],"value":null}
  ]
}

because startkey="[2,10]" < "[2,20]" && "[4,30]" < "[5,10]"=endkey, but "[5,20]" is not within that string Range.

Range Queries with startkey and endkey

startkey => endkey is a Range query using strcmp(), the group and group level is based on the string, where the comma is separating string tokens.

A Good Reference Link (since Couchbase Views work much like Apache CouchDB Views (inspired by them)) http://wiki.apache.org/couchdb/View_collation#Collation_Specification

Spatial View/Query

To achieve the result you are trying for, you could also write a Spatial View to have multi-dimensional Queries, numeric only. While you might not initially think of it

function (doc, meta) {
  emit({
    type: "Point",
    coordinates: [doc.ID, doc.Price]
 }, meta.id);
}

The Query would be a Bounding Box Query:

&bbox=2,0,8,30

{"total_rows":0,"rows":[
  {"id":"2","bbox":[2,20,2,20],"geometry":{"type":"Point","coordinates":[2,20]},"value":"2"},
  {"id":"3","bbox":[3,30,3,30],"geometry":{"type":"Point","coordinates":[3,30]},"value":"3"},
  {"id":"4","bbox":[4,30,4,30],"geometry":{"type":"Point","coordinates":[4,30]},"value":"4"},
  {"id":"5","bbox":[5,20,5,20],"geometry":{"type":"Point","coordinates":[5,20]},"value":"5"}
]
}

Another Query:

&bbox=2,30,8,30

{"total_rows":0,"rows":[
  {"id":"3","bbox":[3,30,3,30],"geometry":{"type":"Point","coordinates":[3,30]},"value":"3"},
  {"id":"4","bbox":[4,30,4,30],"geometry":{"type":"Point","coordinates":[4,30]},"value":"4"}
 ]
}