Couchbase view with composite keys

Couchbase Server
1

If I have a view like this:

function (doc) {
if(doc.Type ==“TravelTip”)
{
if(doc.Status == “ACTIVE”) {
for(i=0; i < doc.Topics.length; i++) {
emit([doc.Topics[i].Topic, doc.Topics[i].Category, doc.SubmittedOn], null);
}
}
}
}

And I want to be able to retrieve all TravelTip documents where the Topic = “T1” independent of category value. I also want to retrieve all documents where Topic = “T1” and Category = “C1”.
So, basically I want to know if I can use one view to search documents by just Topic OR by both Topic/Category.
Is this possible using views?

2

Hi,

Yes you certainly can.

Couchbase queries work on the basis of ‘whole of key’ matching. This means that you must either specify an exact match (using key=xxx), or use a range query (starkey and endkey) to encompass all the possible keys based on a partial match.

For Topic and Category this is easy, you just specify the key you want to match against:
key=[“topic”,“category”]

To match only the first part of your compound key, you must specify the remainder of the compound value so that it includes the possible range, so rather than doing an exact match, you perform a range query with a startkey and endkey, using a character or string that would effectively include all the possible combinations. For example:
startkey=[“topic”,null]&endkey=[“topic”,"\u0fff"]

This works because in a range query the information is output in sorted order based on the key value.

Behind the scenes, what a range search does is start emitting when it sees the first instance of the record, specifically when the computed lexical value of the output is equal to the computed lexical value of the startkey (which will be the lowest value of the [“topic”,*] instance, and stop when the last one is seen (i.e. until the emitted value is greater than the computed lexical value of the endkey). In this case, we’ve used a unicode so far outside the normal character range, it should match everything.

There’s a summary of this (in relation to groups and numerical values) here:
http://www.couchbase.com/docs/couchbase-manual-2.0/couchbase-views-writing-querying-selection.html

MC

3

For this similar view with composite key:

function (doc) {
if(doc.Type ==“TravelTip”)
{
if(doc.Status == “ACTIVE”) {
for(i=0; i < doc.Topics.length; i++) {
emit([doc.SubmittedOn, doc.Topics[i].Topic, doc.Topics[i].Category], null);
}
}
}
}

Why is this query returning all documents instead of only the documents where Topic = “T1”?

?startkey=[null,“T1”,null]&endkey=["\u0fff",“T1”,"\u0fff"]

Thank you

4

Because of the sorting - everything is output in the lexically computed order of the keys, and because the output is a range that starts when it sees the first key, and only stops when the value higher than the end key is seen.

When a view is output, the content of the view is everything until the key, keys, or startkey or endkey are specified.

Startkey and endkey are start and stop values, not selection specifications.

Let’s look at some sample data:

[1, “T1”, “books”]
[2, “T1”, “books”]
[3, “T2”, “books”]
[4, “T1”, “books”]
[5, “T2”, “books”]

The above is output in the correct sort order - and your startkey and endkey would output everything, because [null, “T1”, null] would match the first and output would continue until there was something lexically greater than your endkey.

Might be clearer with some less wide ranging examples. Using the same data:

?startkey=[1, “T1”, null]&endkey[3, “T1”, “\u0fff”]

Would output:

[1, “T1”, “books”]
[2, “T1”, “books”]

From the source data:

The first row is lexically greater than startkey, so we start outputting.
The second row is not lexically greater than end key, so it is output
The third row is lexically greater than endkey, so it is not output, and output now stops

While:

?startkey=[1, “T1”, null]&endkey[4, “T1”, “\u0fff”]

Would output:
[1, “T1”, “books”]
[2, “T1”, “books”]
[3, “T2”, “books”]
[4, “T1”, “books”]

From the source data:

The first row is lexically greater than startkey, so we start outputting.
The second row is not lexically greater than end key, so it is output
The third row is not lexically greater than endkey, so it is output
and so on.

OR even more explicitly:

?startkey=[2, “T1”, null]&endkey[4, “T1”, “\u0fff”]

Would output:

[2, “T1”, “books”]
[3, “T2”, “books”]
[4, “T1”, “books”]

Sometimes it can be easier to think of it in numbers. If you think of each emitted row of output as a number, then if I ask for data starting with 100 and finishing with 200, I get 100, 101, 102,…

If I ask for everything between 121 and 221 then I’ll get 121, 122, 123, …136, 137, 138, …, 178, 179, 180…

The fact that I specified ‘2’ as the middle digit is relevant only for when output starts, and when it stops.

This is where you have to carefully select the output of your view to accommodate your query types, and the information you want.

Have you included the SubmittedOn because you want the information, or because you want to use it as a selection?

5

In this sample data:
id/topic/category

[1, “T1”, “C1”]
[2, “T1”, “C2”]
[3, “T2”, “C3”]
[4, “T1”, “C4”]
[5, “T2”, “C5”]

If I use this key range:

first query: I expected that I would get all documents where topic = “T1”.

?startkey=[null, “T1”, null]&endkey["\u0fff", “T1”, “\u0fff”]

second query: I expected that I would get all documents where category= “C1”.

?startkey=[null, null, “C1”]&endkey["\u0fff", “\u0fff”, “C1”]

third query: I expected that I would get all documents where topic = “T1” and category= “C1”.

?startkey=[null, “T1”, “C1”]&endkey["\u0fff", “T1”, “C1”]

But the Couchbase view outputs all documents in all cases above, why? I need to create only one view to handle a dynamic query (by ID, by ID/Topic, by ID/Category, by Topic/Category, or any combination)

Is this even possible?

Thank you

6

I think my question is relevant to this topic, so here goes.

I have the following in my view:
emit( [ doc.rank1,doc.rank2,doc.rank3,doc.rank4 ], null);
where rank1 - rank4 are numbers from 0 - 100.

I need to be able to sort by any combination of these, ascending or descending… SQL example:
ORDER BY rank1 DESC, rank3 DESC, rank4 ASC, rank1 ASC

Is this possible with a single view in Couchbase 2.0 ? Multiple views do not make sense, because of the number of possibilities for ordering items.

Any ideas or examples?

7

Hello,

You can only sort by the index you are building from left to right.

So it will be from “doc.rank1,doc.rank2,doc.rank3,doc.rank4” and only this.

If you need other sort you need to create different indexes/views.

Regards

Tug

8

I want to ask the same question as marcelocmelo.

i am in a similar situation do we really need to create a view index per search field?

i think this must be very resource consuming action and lexial search for composite key indexes.
in my situatin i want to search a document for 3 fields like[“key1”,“key2”,“key3”] but sometimes i have to use only key1 sometimes key1 & key2 together and sometimes just key3! i dont want to create 3 views separetly. i just created 3 key indexes by emit([doc.key1, doc.key2, doc.key3],null) as i said before when i just want to search by only key3 i can not do that. couchbase returns all documents!

how can i search with only key3 parameter?
thanks

9

Hello,

I am inviting your to look at these blog post that explain how the composite keys and sorting are working in Couchbase:

Regards
Tug
@tgrall