Firebase OrderByKey with startAt and endAt giving wrong results

Question

I have 3 objects with the keys as it looks like this:

They are in format of YYYYMMDD. I am trying to get data of a month. But I am not getting the desired output.

When I query it like this:

var ref = db.child("-KPXECP6a1pXaM4gEYe0");

ref.orderByKey().startAt("20160901").once("value", function (snapshot) {
    console.log("objects: " + snapshot.numChildren());
    snapshot.forEach(function(childSnapshot) {
        console.log(childSnapshot.key);
    });
});

I get the following output:

objects: 3
20160822-KPl446bbdlaiQx6BOPL
20160901-KPl48ID2FuT3tAVf4DW
20160902-KPl4Fr4O28VpsIkB70Z

---

When I query this along with endAt:

ref.orderByKey().startAt("20160901").endAt("20160932").once("value", function (snapshot) {
    console.log("objects: " + snapshot.numChildren());
    snapshot.forEach(function(childSnapshot) {
        console.log(childSnapshot.key);
    });
});

I get this:

objects: 0

---

If I use ~ sign at the end,

ref.orderByKey().startAt("20160901").endAt("20160932~").once("value", function (snapshot) {
    console.log("objects: " + snapshot.numChildren());
    snapshot.forEach(function(childSnapshot) {
        console.log(childSnapshot.key);
    });
});

I get the output:

objects: 3
20160822-KPl446bbdlaiQx6BOPL
20160901-KPl48ID2FuT3tAVf4DW
20160902-KPl4Fr4O28VpsIkB70Z

Is there anything I am missing here?

Answer 1

Wow... this took some time to dig up. Thanks for the jsfiddle, that helped a lot.

TL;DR: ensure that you always have a non-numeric character in your search criteria, e.g. ref.orderByKey().startAt("20160901-").endAt("20160931~").

Longer explanation

In Firebase all keys are stored as strings. But we make it possible for developers to store arrays in the database. In order to allow that we store the array indices as string properties. So ref.set(["First", "Second", "Third"]) is actually stored as:

"0": "First"
"1": "Second"
"2": "Third"

When you get the data back from Firebase, it'll convert this into an array again. But it is important for your current use-case to understand that it is stored as key-value pairs with string keys.

When you execute a query, Firebase tries to detect whether you're querying a numeric range. When it thinks that is your intent, it converts the arguments into numbers and queries against the numeric conversion of the keys on the server.

In your case since you are querying on only a numeric value, it will switch to this numeric query mode. But since your keys are actually all strings, nothing will match.

For this reason I'd recommend that you prefix keys with a constant string. Any valid character will do, I used a - in my tests. This will fool our "is it an array?" check and everything will work the way you want it.

The quicker fix is to ensure that your conditions are non-convertible to a number. In the first snippet I did this by adding a very low range ASCII character to the startAt() and a very high ASCII character to endAt().

Both of these are workarounds for the way Firebase deals with arrays. Unfortunately the API doesn't have a simple way to handle it and requires such a workaround.