How to simultaneously constrain one key with a range and constrain another key with an exact match?

Question

I created a test Node.js script that uses Nano to generate some example data documents, create two views, and run two test queries. Each data document has two keys: "a" and "b". I'd like my query to result in all of the documents where "a" is between 1 and 3 and "b" is equal to 2. I tested a view/query pattern that I found online which uses a startkey array and an endkey array. However, it does not behave as expected when I constrain "a" before constraining "b", but it does appear to behave as expected when I constrain "b" before constraining "a".

Why does the b_then_a view appear to work but the a_then_b view does not? Is this approach incorrect? The script and its output are below.

var nano = require("nano")("http://HCOADAMM:HcoAdammRSM@localhost:5984");

let jasonDB = nano.db.use("jason");

const DESIGN_DOCUMENT_NAME = "findtest";

var testData = [
    { a: 1, b: 1 },
    { a: 1, b: 2 },
    { a: 1, b: 3 },
    { a: 1, b: 4 },
    { a: 2, b: 1 },
    { a: 2, b: 2 },
    { a: 2, b: 3 },
    { a: 2, b: 4 },
    { a: 3, b: 1 },
    { a: 3, b: 2 },
    { a: 3, b: 3 },
    { a: 3, b: 4 },
    { a: 4, b: 1 },
    { a: 4, b: 2 },
    { a: 4, b: 3 },
    { a: 4, b: 4 }
];


var shuffleArray = function(arrayIn) {
    var arrayInLength = arrayIn.length;
    var arrayOut = [];
    while(arrayInLength)
        arrayOut.push(arrayIn.splice(
            parseInt(Math.random() * (arrayInLength--)), 1
        )[0]);
    return arrayOut;
}

var createTestRecords = function() {

    var recordsShuffled = shuffleArray(testData);

    recordsShuffled.forEach(function(record) {
        jasonDB.insert(
            record,
            function(err, body) {
                if(err)
                    console.log(err);
                else
                    console.log("updated user doc " + JSON.stringify(body));
            }
        );    
    });
}

var createDesignDocument = function() {

    jasonDB.get("_design/" + DESIGN_DOCUMENT_NAME, {}, function(err, body, headers) {
        if(!err || err.error === "not_found") {

            var dbObject = new Object();
            dbObject._id = "_design/" + DESIGN_DOCUMENT_NAME;
            if(!err) {
                dbObject._rev = body._rev;
            }
            dbObject.language = "javascript";

            dbObject.views = {
                a_then_b: {
                    map: function(doc) {
                        emit([doc.a, doc.b]);
                    }
                },
                b_then_a: {
                    map: function(doc) {
                        emit([doc.b, doc.a]);
                    }
                },
            };

            jasonDB.insert(dbObject, function(err, body, header) {
                if(err) {
                    console.log("insert error:");
                    console.log(err);
                } else {
                    console.log("created " + "jason/_design/" + DESIGN_DOCUMENT_NAME);
                }
            })
        } else {
            console.log("get error:");
            console.log(err);
        }
    });


}

var queryTest = function() {

    jasonDB.view(
        DESIGN_DOCUMENT_NAME,
        "a_then_b",
        { startkey: [1, 2], endkey: [3, 2] },
        function(err, body) {
            if(err) {
                console.log(err);
            } else {
                console.log("a_then_b")
                body.rows.forEach(function(el) {
                    console.log(el);
                });
                console.log("body.rows.length = " + body.rows.length);
                console.log("");
            }
        }
    );

    jasonDB.view(
        DESIGN_DOCUMENT_NAME,
        "b_then_a",
        { startkey: [2, 1], endkey: [2, 3] },
        function(err, body) {
            if(err) {
                console.log(err);
            } else {
                console.log("b_then_a")
                body.rows.forEach(function(el) {
                    console.log(el);
                });
                console.log("body.rows.length = " + body.rows.length);
            }
        }
    );
}

//createTestRecords();
//createDesignDocument();
setTimeout(function() {
    queryTest();
}, 1000);

output:

a_then_b
{ id: '812f16b3826569ec94eb35d087030d64',
  key: [ 1, 2 ],
  value: null }
{ id: '812f16b3826569ec94eb35d087030709',
  key: [ 1, 3 ],
  value: null }
{ id: '812f16b3826569ec94eb35d08702a846',
  key: [ 1, 4 ],
  value: null }
{ id: '812f16b3826569ec94eb35d087032077',
  key: [ 2, 1 ],
  value: null }
{ id: '812f16b3826569ec94eb35d08702fd89',
  key: [ 2, 2 ],
  value: null }
{ id: '812f16b3826569ec94eb35d08702caee',
  key: [ 2, 3 ],
  value: null }
{ id: '812f16b3826569ec94eb35d08702c32a',
  key: [ 2, 4 ],
  value: null }
{ id: '812f16b3826569ec94eb35d08702b358',
  key: [ 3, 1 ],
  value: null }
{ id: '812f16b3826569ec94eb35d087031386',
  key: [ 3, 2 ],
  value: null }
body.rows.length = 9

b_then_a
{ id: '812f16b3826569ec94eb35d087030d64',
  key: [ 2, 1 ],
  value: null }
{ id: '812f16b3826569ec94eb35d08702fd89',
  key: [ 2, 2 ],
  value: null }
{ id: '812f16b3826569ec94eb35d087031386',
  key: [ 2, 3 ],
  value: null }
body.rows.length = 3

Answer 1

You can only do this if the second key is the first in the index. So you need to revere the keys in your index, such that b is indexed first, and a, second. This will allow you to search for on a range of [2,1] through [2,3].