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?

You've included a picture of the JSON tree in your question. Please replace that with the actual JSON as text, which you can easily get by clicking the Export button in your Firebase Database console. Having the JSON as text makes it searchable, allows us to easily use it to test with your actual data and use it in our answer and in general is just a Good Thing to do. A jsfiddle/jsbin that reproduces the problem would be even better. — Frank van Puffelen, Aug 22 '16 at 14:34
@FrankvanPuffelen, Here is the fiddle: https://jsfiddle.net/kirtan403/g0o6y7wv/ — kirtan403, Aug 22 '16 at 15:43

score 13 · Accepted Answer · edited Jun 20 '20 at 09:12

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.

Woh ! I never get it if I didn't posted. I spent more than 4 hours to understand what is happening! I tried in Android and javascript, thought something is wrong with the sdk at last. So, that little line I was failing to understand `Children with a key that can be parsed as a 32-bit integer come first, sorted in ascending order` in the docs was the answer! Superb explanation! Space comes before every special character and ~ at the last, that is why it was working correctly with that option (http://www.asciitable.com/) .. — kirtan403, Aug 22 '16 at 18:04
One last question, is it possible to create a fiddle for firebase without exposing api key? — kirtan403, Aug 22 '16 at 18:05
Nope. To access the Firebase Database you need to have the URL for the database and the API key. But note that this is not a security risk: http://stackoverflow.com/questions/37482366/is-it-safe-to-expose-firebase-apikey-to-the-public — Frank van Puffelen, Aug 22 '16 at 18:17
So, i will set security rules for that node before sharing it! Thanks! :) — kirtan403, Aug 22 '16 at 18:23

Firebase OrderByKey with startAt and endAt giving wrong results

1 Answers1

Longer explanation

Linked