Performing a "Select where property equals A or B" in CouchDB

I've been slowly converting a few SQL database tables to CouchDB where it makes sense, and recently ran into a problem where I couldn't figure out how to translate a simple SQL select statement into the CouchDB equivalant, where I wanted to select documents that had one of two known values. It turns out this was pretty darn simple, but first let's look at what I was trying to query.

Pretend I've got a CouchDb database with these four documents:

    { "_id": "foo",
      "sourceId": 5,
      "targetId": 10 },
    { "_id": "bar",
      "sourceId": null,
      "targetId": 10 },

    { "_id": "baz",
      "sourceId": 3,
      "targetId": 10 },

    { "_id": "bat",
      "sourceId": null,
      "targetId": 15 }

I was trying to write a view function that would be the equivalant of the SQL Select * from table where targetId = 10 AND (where sourceId = 5 OR where sourceId = null). I wanted to select the documents with id foo or bar, but not the documents with id baz or bat.

To do that, I tried to write a view function that would emit the source id and target id as keys for each document:

function (doc) {
    emit([doc.targetId, doc.sourceId], doc)

That function would put out keys like [10, 5] and [10, null]. But I couldn't figure out how to actually make use of those keys to get the foo and bar documents without making two different requests. I had attempted to use wildcards in the query, but that was accidentally catching any documents that had source or target ids between the wildcards.

http://localhost:5984/database/_design/docname/view/_viewname?start_key=[10, 15]&end_key=[10, {}]

But that was accidentally picking up documents with any target id, not just one that was null or 15. It turns out I was massively overthinking this, because I somehow forgot that you don't have to use start keys and end keys; you can just specify the exact keys you're searching for!

So if I'm looking for documents with a target id of 10 and a source id that was either 5 or null, I can easily request just those using my view function and the keys=[...] parameter:

http://localhost:5984/database/_design/docname/view/_viewname?keys=[[10, 5], [10, null]]

That's all it takes to query my view function and get just the foo and bar documents!

Learn how to build rock solid Shopify apps with C# and ASP.NET!

Did you enjoy this article? I wrote a premium course for C# and ASP.NET developers, and it's all about building rock-solid Shopify apps from day one.

Enter your email here and I'll send you a free sample from The Shopify Development Handbook. It'll help you get started with integrating your users' Shopify stores and charging them with the Shopify billing API.

We won't send you spam. Unsubscribe at any time.