Blog

View Collation in CouchDB

February 14, 2012

A couple of weeks ago I learned about a very neat feature of CouchDB called view collations. Basically, view collations enables us to make joins between documents. Let’s look at a simple example here.

Suppose we have a customer-order model where a customer can have one or more orders. The data of the documents for our example looks like this:

Here we have two customers with their respective orders. The first customer has two orders and the second customer has only one order. Notice that the documents for the orders each have an attribute that contains the ID of the corresponding customer document. 

The final goal is that we want to retrieve all customers with their corresponding orders using a single view. In order to accomplish this we have to make use of complex keys in our map function:

{
   "_id": "807023fe77d927e1c222adfe4a0007ba",
   "_rev": "2-2eae48beb08acc72ed2ab64ccfbcb1c7",
   "type": "customer",
   "firstName": "Homer",
   "lastName": "Simpson"
}

{
   "_id": "807023fe77d927e1c222adfe4a0013c2",
   "_rev": "2-9fc81f3b250c514fbd7e1d5f4a44816b",
   "type": "order",
   "amount": 34,
   "customer": "807023fe77d927e1c222adfe4a0007ba"
}

{
   "_id": "807023fe77d927e1c222adfe4a001b53",
   "_rev": "2-9dbf739124353363d53d1ba6671aac4e",
   "type": "order",
   "amount": 58,
   "customer": "807023fe77d927e1c222adfe4a0007ba"
}

{
   "_id": "807023fe77d927e1c222adfe4a002516",
   "_rev": "1-5cb6d3729618eb7db0f9285237fd8308",
   "firstName": "Ned",
   "lastName": "Flanders",
   "type": "customer"
}

{
   "_id": "807023fe77d927e1c222adfe4a002a4f",
   "_rev": "1-cc5eb0d8c6db0f698c53f9078d16db3b",
   "type": "order",
   "amount": 75,
   "customer": "807023fe77d927e1c222adfe4a002516"
}

The key is composed of the ID of a customer and an  arbitrary number used for sorting (collating). For this number we’ve used 0 for customer documents and  1 for order documents. Because the orders will also be sorted by customer ID and their sorting number is higher than the equivalent number for customer documents, the order documents for a particular customer will always come after the customer document itself. This is how the output of the view looks like:

image

Here we see the first customer and his respective orders, followed by the second customer and his single order. This way we can have our cake and eat it too.

We can use extra filters as well. Suppose that we want to retrieve the data and all the associated orders for one specific customer, we can easily use the startkey/endkey query options in order to get a subset of the view rows:

?startkey=["807023fe77d927e1c222adfe4a0007ba"]&endkey=["807023fe77d927e1c222adfe4a0007ba", 2]

Using this technique can be quite useful for a couple of scenarios. Being able to make use of the sorting features for complex keys is actually quite nice. 

Until next time

Profile picture of Jan Van Ryswyck

Jan Van Ryswyck

Thank you for visiting my blog. I’m a professional software developer since Y2K. A blogger since Y2K+5. Curator of the Awesome Talks list. Past organizer of the European Virtual ALT.NET meetings. Thinking and learning about all kinds of technologies since forever.

Comments

About

Thank you for visiting my website. I’m a professional software developer since Y2K. A blogger since Y2K+5. Curator of the Awesome Talks list. Past organizer of the European Virtual ALT.NET meetings. Thinking and learning about all kinds of technologies since forever.

Contact information

(+32) 496 38 00 82

infonull@nullprincipal-itnull.be