What is CouchDB equivalent of SQL not in or not equals?

I’ve started using CouchDB mainly as a database for mobile apps. However I found it useful also as a backend for server side web applications. I’m a beginner with CouchDB and in one of my applications I needed something simple. I wanted all documents except the ones with a certain value for a field. The value of the field was not known in advance, so I started searching in the documentation for the CouchDB equivalent of SQL not in or not equals. The answer I found online There is no equivalent did not really satisfy me, so I gave it a go to finding a solution. In this post I will show you how to retrieve all documents from CouchDB except those with a particular field value. We will pass the value in, so it will be just like a dynamic query.

The problem

First, let’s take some example data. Let’s say we store books in our CouchDB. The documents might look like this:

{
 ISBN:"0716604892"
 title:"The Great Gatsby",
 genre:"historical fiction",
 rating:3.6,
 author:{
 code:192733,
 name:"F. Scott Fitzgerald",
 }
},
{
 ISBN:"0896160472"
 title:"The Grapes of Wrath",
 genre:"historical fiction",
 rating:4.5,
 author:{
 code:291641,
 name:"John Steinbeck",
 }
},
{
 ISBN:"0489612047"
 title:"Nineteen Eighty-Four",
 genre:"science fiction",
 rating:4.8,
 author:{
 code:976231,
 name:"George Orwell",
 }
},
{
 ISBN:"0604849612"
 title:"Ulysses",
 genre:"fiction",
 rating:3.9,
 author:{
 code:415416,
 name:"James Joyce",
 }
},...

 

Let’s say that I would like to retrieve all books, except the ones by John Steinbeck. How do we do that in CouchDB since there is no built in mechanism for opting out documents.

First of all let’s take a look at what CouchDB offers for querying data.

There are CouchDB views. What are CouchDB views? Since I’m coming from years and years of using only relational database, the closest equivalent to CouchDB views would be relational database indexes. CouchDB views allow you to associate per document a key to a partial or full document. You can then quickly retrieve documents based on these keys.

With views however you can only query by retrieving by key, a list of keys or a range of keys. I will not get into the details here since neither of them helps with what we’re trying to achieve.

The solution

Since views were not really helpful for what I needed I extended my search and found something interesting. Besides views, with their map/reduce functions, CouchDB also offers something called shows and lists. Basically, these allow presenting the documents from the database in a different way. shows is used for a single document and  lists is used for a collection of documents.

I believe that the main use of these was to provide a visualization layer for the data, since most of the examples make use of the two to present the data in an HTML format.

Here’s how a shows function would look for our data:

{
 "_id" : "_design/show-function-examples",
 "shows" : {
 "summary" : "function(doc, req){ return '<h1>'+doc.title+'</h1> <p>by '+doc.author.name+'</p>' }"
 }
}

this will return in HTML the title and author name for specific document/book. As you can see our shows function takes in 2 parameters: doc – the document and req – the original request.

Let’s now take a look at the more interesting lists function in our case. We can have something like:

{
 "_id" : "_design/list-function-examples",
 "lists" : {
 "summary" : "function(head, req){ 
 start({'headers': {'Content-Type': 'text/html'} }); 
 var html = '<ul>';
 while(row = getRow()){
 html += '<li>' + row.title + ' by ' + doc.author.name + '</li>';
 }
 html += '</ul>';
 send(html);
 }"
 }
}

The list function would return an html list with all the books and authors from the database.

So, how do we retrieve all books except the ones from James Joyce (415416) for example? Well, my solution is to make use of the req parameter of the list function. req.query contains all the query string parameters passed when retrieving the results from CouchDB. So, let’s say we pass a parameter called exceptAuthorId. The query would look something like this:

http://localhost:5984/com-codingdude-books/_design/list-function-examples/_list/summary/BooksView?exceptAuthorId=415416

I assume here that BooksView is a map function that does something simple like emit(doc.isbn,doc). Now let’s modify a bit the lists function to take the exceptAuthorId param into account:

{
 "_id" : "_design/list-function-examples",
 "lists" : {
 "summary" : "function(head, req){ 
 start({'headers': {'Content-Type': 'text/html'} }); 
 var html = '<ul>';
 while(row = getRow()){
 if (!req.query.exceptAuthorId || doc.author.id != req.query.exceptAuthorId){
 html += '<li>' + row.title + ' by ' + doc.author.name + '</li>';
 }
 }
 html += '</ul>';
 send(html);
 }"
 }
}

So, if there is no exceptAuthorId param passed in then all the documents will be in the list, and if there is one, then all the documents except the ones with the author.id passed in will be in the list.

But this only shows me  an HTML, which is all fine if I’m only viewing the data. But what if I want to use the data like any other data I retrieve from CouchDB. Let’s transform the function a bit and retrieve the data as JSON:

{
 "_id" : "_design/list-function-examples",
 "lists" : {
 "summary" : "function(head, req){ 
 start({'headers': {'Content-Type': 'application/json'} }); 
 var rows = [];
 while(row = getRow()){
 if (!req.query.exceptAuthorId || doc.author.id != req.query.exceptAuthorId){
 rows.push(row);
 }
 }
 send(JSON.stringify({total_rows:rows.length,rows:rows}));
 }"
 }
}

So, first we changed the Content-Type header to application/json to indicate that the response that follows is actually JSON data. Then we store the results in an array and at the end we create an object with a similar form to the response of CouchDB, containing the number of rows retrieved and the rows themselves.

I used JSON.stringify because the result has to be a string representation of the object.

That is my solution for the CouchDB equivalend of not in or not equals to. You can easily extend this to support passing in multiple IDs via the req.query.

John Negoita

View posts by John Negoita
I'm a Java programmer, been into programming since 1999 and having tons of fun with it.
[jetpack-related-posts]

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top