Order by on missing field constrains result

Question

I'm running into an issue with Firestore that is pretty unintuitive to me and so I'm wondering if anyone can help me understand why Firestore is giving this result.

Let's say I have a collection called "people" with fields "firstName" and "lastName". Let's say I have 20 documents in that collection that have the lastName "Quaid". I then have a field "inCanada" that is only present in a subset of those 20 documents, let's say 5. The other 15 documents don't have that field.

What is surprising is that an orderBy clause using that optional field is actually filtering the result set rather than just sorting it, which doesn't make sense to me when compared with other databases.

In the below example, I expected both result sets to have 20 elements, however the second set that has the orderBy only has those 5 documents where that field is present.

Could someone please explain why Firestore does this?

const Admin = require('firebase-admin');

(async function () {
  const initFirebase = require('../initFirebase');
  initFirebase();

  const people = Admin.firestore().collection('people');
  const quaids = people.where('lastName', '==', 'Quaid')
  const quaids1 = await quaids.get();
  const quaids2 = await quaids.orderBy('inCanada', 'desc').get();

  console.log(quaids1._size); // 20
  console.log(quaids2._size); // 5
})();

Answer 1

In a nutshell, this is because Firestore queries are based on indexes: each time you create a doc Firestore creates an index for each field of the doc.

So since the field "inCanada" is only present "in a subset of the 20 documents", only this subset of documents is present in the "inCanada" index, resulting in the situation you mention.

This official video from Todd Kerpelman from the Firebase team explains it very well: https://www.youtube.com/watch?v=Ofux_4c94FI#t=4m17s. (This link will open the video at 4m17s i.e. when the explanations on the index mechanism start. However the part on the impact of indexes on the querying possibilities is more around 6m22s!)

If you want to include the other documents in your query results you should write a value for the field "inCanada" for those documents, for example use a false value (and use true for the docs that are "inCanada").