Using compound keys and group-by functions

Using compound keys and group-by functions

When Couchbase Server generates an index, it can create compound keys ; a compound key is an array that contains multiple values. Couchbase Server will sort items in an index based on the sequence of keys provided in a compound key. Couchbase Server will sort items in an index based on the key in position 0, and then for all items with matching keys for position 0, sort based on the key in position 1 and so forth. This enables you to control how an index is sorted, and ultimately how you can retrieve information that is grouped the way you need it. For example here is an index created by Couchbase Server using compound keys:

KeyValue ["a","b","c"] 1 ["a","b","e"] 1 ["a","c","m"] 1 ["b","a","c"] 1 ["b","a","g"] 1 

Each of the keys above is a compound key consisting of three different array elements. The keys at the beginning of the index all have ‘a’ in position 0 of the array; within the first group of items starting with ‘a’, the items with ‘b’ in position 1 are placed before those with ‘c’ in position 1. By providing multiple keys we have a first key for sorting, and within the first keys that match, a secondary key for sorting within that group, and so forth.

To retrieve results from this index, we can use a group-by function to extract the items which meet our criterion. The criterion we use to select an item for a group-by function is called a prefix . When you run a group-by query you run a reduce query on each range that exists at the level you want. Couchbase Server will return results grouped by the unique prefix at that level. For instance, if you specify level 1 a unique prefix is ["a"] ; if you specify level 2, a unique prefix is ["a","b"] . Here is an example using the Ruby SDK:

doc.myview(:group_level => 1) 

We query myview and provide the parameter :group_level set to 1 to indicate the first position in a compound key. The result set returned by Couchbase Server will appear as follows:

KeyValue ["a"] 3 ["b"] 2 

In this case we perform the built-in reduce function of _count which will count the unique instances of keys at level 1, which corresponds to position 0 of the compound key. Since we have three instances of ["a"] we have the first row in the result set have 3 as the value, and since there are two instances of a ["b"] as a prefix, we have 2 for the second result. The next example demonstrates the result set we receive if we query a view with a group level of 2. Our query would be as follows:

doc.myview(:group_level => 2) 

In this case we query the view with the group-by level set to 2, and the unique prefix will consist of items in position 0 and 1 of the array. We receive this result set:

KeyValue ["a", "b"] 2 ["a", "c"] 1 ["b", "a"] 2 

Since there are two instances of the unique prefix ["a","b"] , we have the value of 2 for the first result. The second result is the next unique item based on the unique prefix ["a","c"] . In this case the item only occurs one time in our index, therefore we have the value of 1. The last item is the unique prefix ["b","a"] which occurs 2 times in the index, therefore we have a value of 2 for that result. To learn more about group-by parameters used in view queries, see the individual Language Reference for your SDK at Develop with Couchbase .