Querying a View based on Multiple Keys to get a range of data


#1

I have a set of documents where multiple documents are associated with userids/accounts and each document has a time stamp attribute. I would like to retrieve all documents for an account/userid in a particular date range.

I have my emit function written as below

function (doc, meta) {
  var months = { 'Jan':1, 'Feb':2, 'Mar':3, 'Apr':4, 'May':5, 'Jun':6, 'Jul':7, 'Aug':8,'Sep':9, 'Oct':10, 'Nov':11, 'Dec':12
  }
  var t = doc.TIMESTAMP.split(/[- .]/);

  emit([doc.A_NUM,t[2],months[t[1]]], [doc.ID,doc.TIMESTAMP]); // t[2] returns the year and months[t[1]]] returns month
}

Once i have indexed the view, I would like to make a query to retrieve all doc IDs along with one more field TIMESTAMP for a particular A_NUM for last couple of months. SO my input would be something like

Inputs:
start key - [A_NUM, 2014, 6]
end key - [A_NUM, 2014, 4]

List<String> list = new ArrayList<>();
list.add("localhost");
Cluster cluster = CouchbaseCluster.create(list);
Bucket bucket = cluster.openBucket("my-service-data", "");
ViewResult result = bucket.query(ViewQuery.from("contact", "getByANumber").startKey(/*startKey*/).endKey(/*endKey*/));

How should i form startKey and endKey and how my inputs need to be provided here? Could someone please point me to the right example. I have looked at http://docs.couchbase.com/developer/java-2.1/querying-views.html but i could not understand or formulate my case from this doc.


#2

hey @daschl can you advise here please. thanks


#3

I think what you can use here is the JsonArray class:

ViewResult result = bucket.query(
  ViewQuery.from("contact", "getByANumber")
    .startKey(JsonArray.from(A_NUM, 2014, 4))
    .endKey(JsonArray.from(A_NUM, 2014, 6)));

#4

Thanks @simonbasle this worked.


#5

If i would like to query based on a single key, is this the way we query ?

ViewQuery.from(“contact”, “getContactHistoryByAccountNumber”).startKey(“101”).endKey(“101”));

If i query using the below format, it doesn’t return the results.

ViewQuery.from(“contact”, “getContactHistoryByAccountNumber”).key(“101”);


#6

key("myKey") should work I think… remember this is not the Document’s ID, but the key as emitted by the map function. so if your map function looks like the one in first post, this should actually be another JsonArray.


#8

That’s nice.
Is there an equivalent for JsonArray in .NET ?


#9

@itay not really. for this kind of multi-key in the .NET client you’d have to pass JSON in String form and use the overload that specifies no encoding is necessary (as in “this is already JSON”):

var query = new ViewQuery("beer-sample", baseUri).
                From("beer", "brewery_beers").
                StartKey("[\"21st_amendment_brewery_cafe\"]", false).
                EndKey("[\"aass_brewery\"]", false);