Firestore select where is not null

Question

I'm using firebase to manage my project and I cannot get to create a query with a where clause where some value is not null.

Example: I have a collection of employees. Each have a list of equipments as an object where the key is the equipment id and the value a color.

user = {
    firstName: 'blabla',
    lastName: 'bloblo',
    equipments: {
        123: 'blue',
        124: 'red'
    }
}

I would like to get all the employees who has a certain equipment in the equipments. Lets say 123.

It comes to Select * from Employees where equipments.123 is not null. I've tried:

firestore.collection('employees').where(`equipments.${equipmentId}`, '!=', null)

but it's not working.

I can't seems to make it work. Can you help me.

Cloud Firestore has no "not equal" operator. See https://stackoverflow.com/questions/47251919/firestore-how-to-perform-a-query-with-inequality-not-equals — Frank van Puffelen, Jan 27 '18 at 19:31
As @FrankvanPuffelen mentioned, there's no "not equal" operator, but a workaround just crossed my mind. Can you try using `firestore.collection('employees').where('equipments.${equipmentId}', '<', '\uf8ff')`. I've never tested this, but I believe it's worth a shot. — Rosário Pereira Fernandes, Jan 27 '18 at 21:55
Oops... I misread as a "where not exists". A "where exists" is indeed often feasible. Sounds like an answer @RosárioPereiraFernandes! :-) — Frank van Puffelen, Jan 27 '18 at 23:26
Im just checking for any field in the non-existing document (field named e.g. .exists) if it equals true — repo, Jan 22 '19 at 14:35

score 96 · Accepted Answer · edited May 20 '21 at 16:24

96

Update Sep 2020: v7.21.0 introduces support for not equals (!=) queries! That means you can now use the code bellow:

firestore.collection('employees').where(`equipments.${equipm‌entId}`, '!=', null)

Previous answer:

Firestore has no "not equal" operator. But looking at the logic, what you're trying to do is query for values which are String, and not null. Firestore can do that if you pass a String to the where() function.

So what you can do is query for values lower than \uf8ff. That's a very high code point in the Unicode range. Since it is after most regular characters in Unicode, this query will return everything that is of type String:

firestore.collection('employees').where(`equipments.${equipm‌entId}`, '<', '\uf8ff')

Or you can simply query for values higher than "" (empty String):

firestore.collection('employees').where(`equipments.${equipm‌entId}`, '>', '')

edited May 20 '21 at 16:24

Laurent

14,122
13
57
89

answered Jan 28 '18 at 00:00

Rosário Pereira Fernandes

11,015
6
55
79

26

Even easier is >= "" – Dan McGrath Jan 28 '18 at 00:26
3

Wow, I wonder how I did not think of that. Thank you @DanMcGrath – Rosário Pereira Fernandes Jan 28 '18 at 00:31
This answer doesn't seem to work for fields with document references unfortunately. – Martin Cremer Jun 20 '19 at 17:04
4

please note that `!=` and `not-in` are: `Neither query operator will match documents where the specified field is not present.` [firebase](https://firebase.google.com/support/release-notes/js#cloud-firestore_3) – AsimNet Oct 20 '20 at 00:32

score 29 · Answer 2 · edited May 20 '21 at 16:25

FWIW since Firestore indexes are sparse [1] you can do something like:

firestore.collection('employees').orderBy(`equipments.${equipm‌entId}`)

And you'll only get documents where that field is set. If you're explicitly setting the fields to null in your database, however, you'll get null values first, so if you want to avoid explicit null values you can do:

firestore.collection('employees').orderBy('equipments.${equipm‌entId}').startAfter(null);

[1]: Sparse in the sense that if the field is not present in the document, Cloud Firestore does not create an index entry in the index for that document/field.

Thank you @rockwotj, the `.startAfter(null)` was just what I needed! — Jon Humphrey, Sep 14 '20 at 21:06

Firestore select where is not null

2 Answers2

Linked