FR: Firestore OR operator in WHERE query

[EpicButterz]

[REQUIRED] Describe your environment

• Operating System version: N/A
• Firebase SDK version: 4.6.2
• Firebase Product: firestore

[REQUIRED] Describe the problem

Rather than having to make several separate queries I would like to see an OR operator for the WHERE query in Firestore. This is something that is currently available in MongoDB or with the $or operator here.

Relevant Code:

The query could look something like this:

citiesRef.where("state", "==", "CA", "||", "state", "==", "AZ")

or better yet

citiesRef.where("state == CA || state == AZ")

[EpicButterz]

For my specific case something more like an in operator would be more appropriate. I am looking to retrieve documents from a collection that correspond to a list of IDs. So something like:

citiesRef.in('id', ['P4ZoMnrNRQjnMGmuMmvc', 'VSw2KyP1YaBRnkheq5kB', 'i1658JL5a2Itv2l0BC8B'])

[agersoncgps]

OR and IN / CONTAINS / LIKE are pretty basic concepts in query languages. Confused why they are missing here.

[mikelehen]

Thanks for the feedback! These query improvements (OR, IN / CONTAINS, and LIKE queries) are all on our radar for future Cloud Firestore query improvements. I'm closing this issue as it's not specific to the JS SDK, but we know that these features would be very useful and there's a lot of demand for them. We intend to address them in the future as we continue to evolve Firestore's query functionality. Thanks!

[dzoba]

I would be very interested in these as well.

[andreluisce]

Any news about it?

[taishikato]

I am looking forward to this feature

[atcults]

Any news on ORing two filters ?

[csoreff]

I'd kill for a NOT IN [] >.<

[katothompson]

This would be a great feature to have!

[schassande]

I am looking forward to this feature too !

[jkampitakis]

Same here, I am looking forward to this feature.

[cnolimit]

can't wait to see the LIKE query addition

[mdrideout]

For anyone struggling for a solution, I mirrored the parts of firestore database relevant to search in elasticsearch. It's a search engine for no-sql database data, and can do WAY more than a "LIKE" or "WHERE" clause in a sql query. It's very customizable. Any additional data needed from the search results can then be pulled from firestore. In my app, elasticsearch handles any search queries, and data is pulled from firestore.

You can setup a stack on GCP relatively easily and for free, and now elasticsearch has their own stack deployment option (which can also use GCP, and provides more security options). https://www.elastic.co/ https://bitnami.com/stack/elk

[githubbla]

I am looking forward to this feature too !

[rtman]

second all this.. OR is very necessary. Trying to concat lists after two WHERE queries is not great.

[1001daysofcode]

Can't wait for this feature!

[m-a-zhou]

This feature would be very helpful.

[CyrusZei]

any ETA on this ?

[mikelehen]

We can't provide ETAs on upcoming features, but as I said before, OR queries and IN queries are definitely on our radar and we appreciate all the interest.

For what it's worth, IN queries are likely more straightforward and so we may tackle them first, something along the lines of what @EpicButterz described in https://github.com/firebase/firebase-js-sdk/issues/321#issuecomment-344994495. I'd be curious to hear how many of you would find that useful or if you're looking for more general-purpose OR queries. And if you're looking for more general-purpose OR queries, some details on your use cases (e.g. examples of specific queries that you'd want to do in your app) could help us design the feature. Thanks!

[csoreff]

@mikelehen IN and NOT IN are more useful I think. OR would be nice to have but for now we can just combine the results of multiple queries for that.

[mikelehen]

Thanks. I'm curious if there's a strong desire for "NOT IN" (against a list of values) or if just "!=" (against a single value) would be sufficient for the use cases you have in mind.

[csoreff]

@mikelehen Let's say I have a list of keys for ignored items. I'd like to be able to easily grab all items where the key is NOT IN []. != is certainly desired as well, but they have different use cases. Right now I'm forced to grab all items and then manually filter out the ignored items on client side.

[antonybudianto]

i want a datastore like this:

members: [
  {
    id: 123
  },
  {
    id: 231
  }
],

teams: [
  {
    id: 1,
    members: [123, 231]
  },
  {
    id: 2,
    members: [123]
  }
]

but because there's no IN query, I've to duplicate the relationship:

members: [
  {
    id: 123,
    teams: [1, 2]
  },
  {
    id: 231,
    teams: [1]
  }
],

/* teams remain the same **/

my requirement is that: a user must be able to know his/her teams and a team must know its members.

[mikelehen]

@antonybudianto Thanks for the feedback. If I'm understanding correctly, I'd actually characterize this as a slightly different feature and the good news is that:

1. You can solve this today by modeling your teams as { id: 1, members: { "123": true, "231": true } } and then for user 123 to find all their teams you can do a query like teamsCollection.where("members.123", "==", true).
2. We're looking into adding a new feature that would let you query based on array values which would let you keep your desired schema (with arrays) and do something like teamsCollection.where("members", "array_contains", 123). I can't promise any timelines but this may launch before we get to OR / IN queries.

[antonybudianto]

Thanks for the solution. But one more, suppose I visit the team "A" page, and want to display its members there (I want to display their names/other fields, not ids), how do you query it (userCollections should contains no relationship object because teamcollections already have it)?

[mikelehen]

@antonybudianto Ahh, yeah. To get a member's name, etc. you'd need to look that up from the members collection presumably. Cloud Firestore doesn't support JOIN operations and that's unlikely to change in the foreseeable future. That said, if we added an IN query as described in https://github.com/firebase/firebase-js-sdk/issues/321#issuecomment-344994495 then you could look up all the members with a single additional query once you have their IDs.

Thanks for the feedback.

[antonybudianto]

@mikelehen yes, IN operation will be sufficient for my case, thanks for the consideration.

[jorgevvr]

Hello all, i'm new here. So i am sad that have not some functionality that i can do with SQL but i hope that have soon. This example mentioned by @mikelehen is exactly what i was searching for, hope it is added soon:

teamsCollection.where("members", "array_contains", 123)

Thanks!

[lxpdd]

Hello, I'm with team waiting a lot for such functionality as operators IN and NOT IN, it would improve performance of our application a lot. Thanks!

[humanagent]

please do OR!

[wonsuc]

Since I noticed Firestore Security Rules doesn't support the enforce uniqueness of values across documents, I chose the way to search a document if it already exists with same values before I insert a new document from Cloud Functions.

This is my data structure.

logs: [
    1: {
        userId: "aaa",
        ip: "212.32.0.110",
        uuid: "00001101-0000-1000-8000-00805f9b34fb",
        timestamp: 1508249728426
    },
    2: {
        userId: "bbb",
        ip: "55.10.23.25",
        uuid: "00001101-0000-1000-8000-00a238b2d02e",
        timestamp: 1514802708503
    }
]

And here is a new document, which I'm trying to insert.

{
    userId: "ccc",
    ip: "212.32.0.110",
    uuid: "00001101-0000-1000-8000-001z21g29v0a",
    timestamp: 1514807874543
}

Now I have to query like this,
logsRef.where("userId == ccc || ip == 212.32.0.110 || uuid == 00001101-0000-1000-8000-001z21g29v0a")

In this example, it should return 1 result which has same ip value(=212.32.0.110) with new document.

I hope theOR query feature will be possible soon! Please let me know if I'm doing something wrong or missing something :)

[natario1]

@mikelehen may I ask you something about your comment https://github.com/firebase/firebase-js-sdk/issues/321#issuecomment-380652617 ?

You state that the map approach is today's solution. But I don't get how we are supposed to leverage this for queries that are just a bit more complex: query.where("members.id1", "==", true).orderBy("createdAt"). This would need to create an index on member ids, so an index for each user. Obviously it is not affordable. How should this be tackled?

This came up in a super simple messaging app: conversation: { members: { id1: true, id2: true}} and I hope I am missing something, because it looks like the Hello World of databases. Do you have any clue?

If not, I hope that array_contains is not just in your radar but a high priority feature.

(By the way, this issue pops up pretty high in search results, so while I understand it should be closed, it would be cool if you could provide a link to a more appropriate resource where people can discuss (a google group post, ...))

[merlinnot]

@natario1 the simplest solution is to remove the inequality (orderBy or an inequality filter), you can sort it on the client side. You don't need custom indexes for this.

[natario1]

Thanks @merlinnot , but the collection might be large so we want to fetch small chunks with orderBy and limit and load the others as the user scrolls.

We also plan an additional constraint where(foo == bar), but I don't want to bother you with my specific use case, I think it is a common query / requirement overall.

[mikelehen]

@natario1 Thanks for the feedback. I can't provide concrete timelines, but array_contains has been under active development recently. If you poked at the commit history in this repo you might notice that the client-side support is mostly implemented. But we are waiting on the backend indexing / query support to roll out and unfortunately this could take some time (or in the worst case we could discover some blocker that delays the feature indefinitely). So I can't provide a concrete timeline, but the feature has been making progress. Hope this helps!

[mtnptrsn]

I'd love to see this feature!

[sumairrasool]

Wasted two hours because of not availability of OR/IN feature. :-( And till now could not find a satisfactory solution except some performance leak solutions like querying multiple times or filtering data on client side. Please guide me if you have any better solution for now. Hope to see these features very soon.

[spiffytech]

I'd like OR so I can perform rudimentary substring searches. I'm happy to tokenize and stem my text and store that as document fields, but I'd need a way to query whether a document has any of the search terms.

[Elvis10ten]

Am still waiting for this.

[serrokitskiy]

Am still waiting for this.

[leonardoalcantara1]

Waiting ... ... ...

[lehno]

Waiting....

[IamSammyT]

I am very interested in the OR clause for the Android SDK as well. I show tasks that are due today (or overdue) or have been completed today under 3 headings (Due Today, Overdue, Completed) My sql query is like this: where nextDate <= today or lastDate = today

Something like this would be nice, so you can put: any type of where clause inside the or and more than 2 clauses

db.collection("tasks") .or( whereLessThanOrEqualTo("nextDate",today), whereEqualTo("lastDate", today), etc )

[kshkrao3]

[adihat]

The reason OR queries isn't provided is very strategic from google. I may be wrong but this is my take on the subject => OR queries require the full scan of the database (or index in this case as they index every field by default) which may slow down the query speed and hence slow down fire store irrespective of whether they index every field or offer an option to create custom indexes. Also its pretty evident that they wan't you to make multiple attempts to read the data and then concatenate it into a global array/list. One of the solutions proposed was to use observables and merge the stream. You can find one of the proposed solution (hacks if you will) for JS here =>https://medium.com/google-developer-experts/performing-or-queries-in-firebase-cloud-firestore-for-javascript-with-rxjs-c361671b201e

[ranjjose-chaz]

So, we have a very common activities feeds requirement where each activity can be tagged with multiple interests . Users are advised to select their interests when they sign up. Now, without OR operator, how am I supposed to list activties relevant to the users? If someone is having say, 5 interests, then it involves querying 5 times and then locally merging them (again sorting them probably based on timestamp). This will create complexities such as splitting the pagination limit across the interests, if there's not enough results in one interest then querying again on other interests to compensate, etc.

I really wish Firebase doc upfront mentioning features such as OR, NOT, etc, aren't there. This is a very difficult situation.

[mikelehen]

Sorry for the pain. We know that this is a very large inconvenience, perhaps a blocker in some cases. To be clear, OR queries are on our roadmap. We can implement them efficiently without a full scan, at least in a large set of cases. There is a lot of work required though and I can't speak to the timeline.

In the meantime, doing multiple queries client-side and merging the results is the only workaround. I realize this is challenging, especially with pagination, etc. as @ranjjose-chaz mentions.

[cissecedric]

I think it could have been a good idea to clearly announce this point in the upfront doc (as said in a post before), so that developers won t feel like trapped once they ve mooved to firestore and discover it by themselves.

[IamSammyT]

@mikelehen Thanks for the update! The in clause is also on the roadmap? This should be an easier one to resolve?

[mikelehen]

@IamSammyT Yep, also on the roadmap. It could be easier to resolve if we treat it specially, but it may be more efficient for us to just implement "generic" OR queries at least at the low-level and have IN plug into that.