Some kind of a join-aggregation query in MongoDB

Question

Inside my DB I have two collections:

Subjects Collection
 - id : ObjectID(own)
 - subjectCreator : ObjectID(of a user)
 - groups : Array
            [
                 ObjectID(of a group),
                 ObjectID(of a group),
                 ....more
            ]
 - media : Array
            [
                 { 
                     id : ObjectID(of media)
                     mediaUploader: ObjectID(of user)
                     other properties that not important
                 },
                 { 
                     id : ObjectID(of media)
                     mediaUploader: ObjectID(of user)
                     other properties that not important
                 },

            ]

and a Groups collection

Groups Collection
 - id : ObjectID(own)
 - groupName: string
 - groupAdmin: ObjectID(of user)
 - members: Array
            [
                 ObjectID(of a user),
                 ObjectID(of a user),
                 ....more
            ]

what I am trying to do is, when given a user id (ObjectID) to get an array of all Subjects that the user can see.

a user can see a subject by two criteria:

if they are the subject Creator
if they are members of the group that the subject include in the groups field

I understand that this is a kind of aggregation between the two queries, but I cannot provide an example of my own trial because I am new to MongoDB, and don't know even how to approach it.

I think (probably wrong) that if this was written in SQL server it would look like this:

given a @userID param

SELECT distinct s.* 
FROM Subjects s
WHERE @userID = s.subjectCreator
OR 
@userID in (
               SELECT members 
               FROM Groups g 
               WHERE g.GroupID in (
                                       SELECT groups 
                                       FROM Subjects
                                       WHERE s.Groups.GroupID = g.GroupID
                                   )
           )

my server side is written in NodeJS

and another small question, which one would be the best practice for the DB structure? the structure above, or the structure below? the difference is within the arrays, please explain why

Subjects Collection
 - id : ObjectID(own)
 - subjectCreator : ObjectID(of a user)
 - groups : Array
            [
                 { groupID: ObjectID(of a group) },
                 { groupID: ObjectID(of a group) },,
                 ....more **OBJECTS**
            ]
 - media : Array
            [
                 { 
                     id : ObjectID(of media)
                     mediaUploader: ObjectID(of user)
                     other properties that not important
                 },
                 { 
                     id : ObjectID(of media)
                     mediaUploader: ObjectID(of user)
                     other properties that not important
                 },

            ]

and a Groups collection

Groups Collection
 - id : ObjectID(own)
 - groupName: string
 - groupAdmin: ObjectID(of user)
 - members: Array
            [
                 { memberID : ObjectID(of a user) },
                 { memberID : ObjectID(of a user) },
                 ....more objects of members id
            ]

Tom Slabbaert Tom Slabbaert · Accepted Answer · 2019-05-28T10:12:37

For the first question our strategy would be to use $lookup to "merge" these collections and then query on it.

db.collection.Subjetcs.aggregate([
        {
           $lookup: {
               from: "Groups",
               let: {group_ids: "$groups"},
               pipeline: [
                    {
                       $match: {
                          $expr:  {
                              $in: ["$id", "$$group_ids"]
                          }
                       }
                    }
               ]
               as: "group" 
           }
        },
        {
           $match: {
              $or: [
                  {subjectCreator: ObjectId(givenUserID)},
                  {'group.members': ObjectId(givenUserID)}
              ]
           }
        },
])

Design wise i would stick with the former, it makes queries look "cleaner" and from my experience will save you time and resources in the future when converting [{_id: 1}, {_id: 2}] to [1, 2] for whatever reason.

Note though that they are equivalent "power", i.e what can be accomplisht with it.

Also i'm not exactly sure how big the over head will be but i imagine indexing that field as an array is far easier for mongo in the case you do so.

Some kind of a join-aggregation query in MongoDB

1 Answers