How to build a MongoDB query that combines two field temporarily?

Question

I have a schema which has one field named ownerId and a field which is an array named participantIds. In the frontend users can select participants. I'm using these ids to filter documents by querying the participantIds with the $all operator and the list of participantsIds from the frontend. This is perfect except that the participantsIds in the document don't include the ownerId. I thought about using aggregate to add a new field which consists of a list like this one: [participantIds, ownerId] and then querying against this new field with $all and after that delete the field again since it isn't need in the frontend.

How would such a query look like or is there any better way to achieve this behavior? I'm really lost right now since I'm trying to implement this with mongo_dart for the last 3 hours.

This is how the schema looks like:

{
    _id: ObjectId(),
    title: 'Title of the Event',
    startDate: '2020-09-09T00:00:00.000',
    endDate: '2020-09-09T00:00:00.000',
    startHour: 1,
    durationHours: 1,
    ownerId: '5f57ff55202b0e00065fbd10',
    participantsIds: ['5f57ff55202b0e00065fbd14', '5f57ff55202b0e00065fbd15', '5f57ff55202b0e00065fbd13'],
    classesIds: [],
    categoriesIds: [],
    roomsIds: [],
    creationTime: '2020-09-10T16:42:14.966',
    description: 'Some Desc'
}

Tl;dr I want to query documents with the $all operator on the participantsIds field but the ownerId should be included in this query.

What I want is instead of querying against:

participantsIds: ['5f57ff55202b0e00065fbd14', '5f57ff55202b0e00065fbd15', '5f57ff55202b0e00065fbd13']

I want to query against:

participantsIds: ['5f57ff55202b0e00065fbd14', '5f57ff55202b0e00065fbd15', '5f57ff55202b0e00065fbd13', '5f57ff55202b0e00065fbd10']

score 1 · Answer 1 · answered Sep 18 '20 at 02:44

Having fun here, by the way, it's better to use Joe answer if you are doing the query frequently, or even better a "All" field on insertion.

Additional Notes: Use projection at the start/end, to get what you need

https://mongoplayground.net/p/UP_-IUGenGp

db.collection.aggregate([
  {
    "$addFields": {
      "all": {
        $setUnion: [
          "$participantsIds",
          [
            "$ownerId"
          ]
        ]
      }
    }
  },
  {
    $match: {
      all: {
        $all: [
          "5f57ff55202b0e00065fbd14",
          "5f57ff55202b0e00065fbd15",
          "5f57ff55202b0e00065fbd13",
          "5f57ff55202b0e00065fbd10"
        ]
      }
    }
  }
])

score 0 · Answer 2 · answered Sep 18 '20 at 00:39

0

Didn't fully understand what you want to do but maybe this helps:

db.collection.find({ 
  ownerId: "5f57ff55202b0e00065fbd10",
  participantsIds: { 
    $all: ['5f57ff55202b0e00065fbd14', 
           '5f57ff55202b0e00065fbd15', 
           '5f57ff55202b0e00065fbd13']
  })

answered Sep 18 '20 at 00:39

luisenrike

2,742
17
23

Edited question I hope it's more accurate now. – Hard_Veur Sep 18 '20 at 00:47

score 0 · Answer 3 · answered Sep 18 '20 at 01:06

You could use the pipeline form of update to either add the owner to the participant list or add a new consolidated field:

db.collection.update({},[{$set:{
   allParticipantsIds: {$setUnion: [
        "$participantsIds",
        ["$ownerId"]
   ]}
}}])

How to build a MongoDB query that combines two field temporarily?

3 Answers3