Firestore: How to query a map object inside a collection of documents?

Question

My objective:

The web app (similar to Google Drive) has 2 screens.

First screen is 'My Story' which displays all the story documents whom I'm the owner.

Second screen is 'Shared with me' which displays all the story documents that I'm either reader, writer, or commenter.

We have this /stories/{storyid}

{
  title: "A Great Story",
  content: "Once upon a time ...",
  roles: {
    alice: "owner",
    bob: "reader",
    david: "writer",
    jane: "commenter"
    // ...
  }
}

Full data structure reference: https://firebase.google.com/docs/firestore/solutions/role-based-access

Question: How to write the below query to fulfil the above objective?

db
.collection('stories')
.where(`roles.${uid}`, '==', 'owner')

The above query does NOT work because it will require Firestore to index roles.alice, roles.bob, roles.david, roles.jane, and roles.[all_uid].

Edit #1: Found a related post (with no answers) asking about Firestore querying with roles

The link you provided is about security, not about querying. If you want to query this document per user, then you will have to index per user. Or, restructure your data so that you can query documents in a way that doesn't require the name of the user to be a field in the document. — Doug Stevenson, Feb 21 '19 at 16:41
So basically you want to get all the stories where `alice == owner`, right? — Alex Mamo, Feb 21 '19 at 16:50
@AlexMamo Yes. Want to get all the stories that belong to a UID (e.g. alice) who is the owner. — Jek, Feb 21 '19 at 17:02
@DougStevenson Yes the link provided is about security. However, the link is just referencing the data structure provided by your team as an example. Using the example data structure, and wanting to do querying, it does not make sense to index per user. So if there are 1,000,000 users, then have to create that many indices? No way! So how to query roles in a document that can return all documents belong to a user_id? e.g. alice. — Jek, Feb 21 '19 at 17:04
As I said, you'll need to use a different structure to support the query you wan to perform. It's common in NoSQL databases to duplicate data for different needs. You have one need for security, which is fine. Now you have a second need, which needs a second solution. — Doug Stevenson, Feb 21 '19 at 17:12
@DougStevenson I answered the question with an answer inspired by your comment. Could you have a look at the answer, and share with me your opinion? — Jek, Feb 21 '19 at 17:49
@choopage-JekBao Can you please explain how the accepted answer solved this problem? Thanks — Alex Mamo, Aug 31 '20 at 06:52

score 13 · Accepted Answer · answered Jul 29 '20 at 14:42

13

Now you can filter ...

db
  .collection('orders')
  .where('orderDetails.status', '==', 'OPEN')

It will check if field orderDetails at property status equals to 'OPEN'

answered Jul 29 '20 at 14:42

Cristi Maris

1,329
22
21

Can you please elaborate on how your suggestion is applicable to this issue? – Marius Brataas Jan 27 '23 at 18:07

score 9 · Answer 2 · answered Feb 21 '19 at 17:16

You cannot achieve this with your actual database structure in a way that you don't need to create an index for each user separately. To sovle this, you should duplicate your data. This practice is called denormalization and is a common practice when it comes to Firebase. If you are new to NoQSL databases, I recommend you see this video, Denormalization is normal with the Firebase Database for a better understanding. It is for Firebase realtime database but same rules apply to Cloud Firestore.

Also, when you are duplicating data, there is one thing that need to keep in mind. In the same way you are adding data, you need to maintain it. With other words, if you want to update/detele an item, you need to do it in every place that it exists.

That being said, you should create another collection named userStories, where you should add as documents all stories where a user is owner. So you database structure should look similar to this:

Firestore-root
   |
   --- userStories (collection)
         |
         --- uid (document)
              |
              --- allStories (collection)
                     |
                     --- storyId
                           |
                           --- role: "owner"

So a query like this:

db.collection('userStories').doc(${uid})
    .collection('allStories').where(`role`, '==', 'owner');

Will work perfectly fine.

could you comment on my answer. I think I know the answer to my question. — Jek, Feb 21 '19 at 17:50
Please see my comment above. So I'll stick with this answer. — Alex Mamo, Feb 21 '19 at 18:02
What motivates you to answer questions on SO? Just curious. Does Google/Firebase pay you for each successful answer? — Jek, Feb 23 '19 at 08:37
@choopage-JekBao No. I was also once helped and I'd like to help others in the same way ;) — Alex Mamo, Feb 23 '19 at 08:50

score 4 · Answer 3 · answered Mar 03 '20 at 22:38

4

Please let me know if I'm mistaken, but it appears that your query should actually work. as an experiment I added the structure you gave in the question and performed the query successfully in the firebase console.

Is this not what you are going for? I also made sure that the "in" operator works for this case as well. In this way you could ask which stories the user is owner & commenter.

This pulls in the correct results:

answered Mar 03 '20 at 22:38

Grahambo

467
5
11

Do you have index on it? – Jek Mar 08 '20 at 14:35
No. Nothing extra was configured to make this work. This works out of the box in this example. – Grahambo Mar 09 '20 at 15:13
this's what i'm looking for. – mrdev Aug 06 '20 at 23:05
That actually seems to work, I've just done a test with the same data structure and everything seems fine. – Joao Gavazzi May 14 '21 at 09:49
But this would require one index for alice, one index for bob, etc, right? You're specifically asking for "alice" here. Thus you'll need one index per user in your database. – Marius Brataas Jan 27 '23 at 18:13

score 0 · Answer 4 · answered Feb 21 '19 at 17:48

0

It is not possible therefore we need to duplicate data in NoSQL.

One possible data structure is

/stories/{storyid}

{
  title: "A Great Story",
  content: "Once upon a time ...",
  roles: {
    alice: "owner",
    bob: "reader",
    david: "writer",
    jane: "commenter",
    mary: "writer",
    // ...
  },
  owner: "alice", // newly added
  shared: ["bob", "david", "jane", "mary"] // newly added
  // alice, bob, david, jane, mary are firebase auth uid
}

So we can query for the web app UI 'My Story'

db
.collection('stories')
.where('owner', '==', uid)

While we can query for the web app UI of 'Shared with me'

db
.collection('stories')
.where('shared', 'array_contains', uid)

Answer is inspired by @Doug Stevenson comments.

answered Feb 21 '19 at 17:48

Jek

5,546
9
37
67

@DougStevenson what do you think of this answer? – Jek Feb 21 '19 at 17:52
3

No, that's not an option since you are duplicating data inside the same document. In this case, what about finding all stories `jane == commenter`? When we duplicate data, we duplicate the entire document, so we can copy the exact same data that already exists in one place, in another place, to suit queries that may not even be possible otherwise. Which is exactly your case. – Alex Mamo Feb 21 '19 at 18:01

Firestore: How to query a map object inside a collection of documents?

4 Answers4