CouchDB querying and filtering by three indexed keys

Keys are always sorted smallest to highest, in one long 1-dimensional list. (I have tried to describe this intuitively in The Parable of CouchDB but no idea if I succeeded!)

What does an array sorted smallest-to-largest look like? If you read all the keys in a view, the left-hand value varies the least; the middle value varies more than the left; and the right-hand value varies the most. In other words, array keys tell CouchDB, "First priority is to sort by key[0], if that is equal, the tie-breaker will be key[1]; if those are equal also, the next tiebreaker is key[2], etc..."

Therefore you probably want your keys to look like this:

[ "receiver_1", null      , a_date       ],[ "receiver_1", "sender_A", some_date    ],[ "receiver_1", "sender_B", another_date ],[ "receiver_2", "sender_A", fourth_date  ],[ "receiver_3", "sender_C", fifth_date   ],

To find all messages for receiver_1 from sender_B and also public messages, you need two queries, one for the "receiver_1", null pairings, and another for "receiver_1", "sender_B". You want to know any date, so you need a range of rows that match the sender/receiver. Unfortunately, the HTTP POST query does not support this.

You could simply query for each selected sender (even all at the same time using threads or asynchronous programming). The receiver and sender are known, and this example allows a range from the smallest value (null) to the largest ({}), which will include all the dates.

?startkey=["receiver_1",null,null]&endkey=["receiver_1",null,{}]?startkey=["receiver_1","sender_B",null]&endkey=["receiver_1","sender_B",{}]

Another option is to simplify your keys and remove the dates.

[ "receiver_1", null      ],[ "receiver_1", "sender_A"],[ "receiver_1", "sender_B"],[ "receiver_2", "sender_A"],[ "receiver_3", "sender_C"],[ "receiver_3", "sender_C"],[ "receiver_3", "sender_C"],[ "receiver_3", "sender_C"],[ "receiver_3", "sender_C"],

Now you can query with the HTTP POST API again. Messages will return not ordered by date. This is not so bad, you can sort them on the client (or a _list function). And remember, even in my first example the dates are not perfectly sorted either.

I think you want;

emit([doc.author, doc.receiver[idx], doc.date], null);

You can then query with

startkey=["USERID1","USERID2"]&endkey=["USERID1","USERID2",{}]

this will return all documents sent by USERID1 to USERID2 in date order. {} is an empty object and, by the rules of CouchDB collation, will sort higher than any number or string, hence the range here is guaranteed to include all possible dates.

Finally, I'll note that CouchDB does not support wildcards.