MongoDB query double nested array with matching sets

Question

Being not familiar with Mongo and still progressing I came accross a problem I can't think to fix (and I don't know if it's feasible)

I have an Event document whose structure looks like this:

{
    "_id": "6138451fb3a7d9564a0229fd"
    "title": "Event  1",
    "cohortsGroups": [
        [
          {
            "_id": "6124beef59d728c82088fd59",
            "name": "2022",
            "type": "promotion"
          }, 
          {
            "_id": "6124bf2159d728c82088fd60",
            "name": "Toronto",
            "type": "city"
          }
       ],
       [
         {
            "_id": "6124beef59d728c82088fd57",
            "name": "2024",
            "type": "promotion"
          },
          {
            "_id": "6124bf2159d728c82088fd68",
            "name": "Tokyo",
            "type": "city"
          }
        ],   
    ]
},
{
    "_id": "6138451fb3a7d9564a0229fe"
    "title": "Event 2",
    "cohortsGroups": [
        [
          {
            "_id": "6124beef59d728c82088fd59",
            "name": "2022",
            "type": "promotion"
          }
        ]
     ]
},
{
    "_id": "6138451fb3a7d9564a0229fh"
    "title": "Event 3",
    "cohortsGroups": [
        [
          {
            "_id": "6124beef59d728c82088fd21",
            "name": "2022",
            "type": "promotion"
          },
          {
            "_id": "6124beef59d728c82088fd43",
            "name": "Amsterdam",
            "type": "city"
          }
        ]
    ]
}

As you can see the field cohortsGroups is a double array of Objects. I would like to retrieve those events based on my user's object who possess also an array (simple) of cohorts

So for example let's say my user looks like this:

{
 "firstName": "John",
 "lastName": "Doe",
 "cohortsRef": [
   {
    "_id": "6124beef59d728c82088fd59",
    "name": "2022",
    "type": "promotion"
   },
   {
    "_id": "6124bf2159d728c82088fd60",
    "name": "Toronto",
    "type": "city"
   }
 ]
}

To make it simple I would like to retrieve an event only if one of the set of cohorts in in his cohortsGroups have his cohorts all presents in the user object.

Taking that in mind and the above example I would only be able to retrive Event 1 and Event 2. I can't retrieve Event 3 because even tho I have the Cohort 2022 in my user, it's paired with the Cohort Amsterdam which is not present in my user's cohorts, (eq: none of the cohort subarrays sets have their values entirely present in my user's cohorts).

I really hope someone can give me a hand on that problem, so far I've tried to map all the user's cohorts ID in an array and query by $elemMatch

Events.find({ cohortsGroups: { $elemMatch: { $elemMatch: { _id: { $in: [ '6124beef59d728c82088fd59', '6124bf2159d728c82088fd60' ] } } } } })

But this solution just retrieves every event that have a subarray cohort matching, it doesn't take into account the sets, so in this case it would also retrieve Event 3 - because 2022 is present - but it's paired with the cohort Amsterdam which is wrong.

Let me know if I wasn't clear enough, any help would be appreciated. At least to know if it's something doable as a mongo query.

Thanks a lot for reading !

sheilak · Accepted Answer · 2021-09-10 19:35:33Z

1

This should work:

db.events.find({ cohortsGroups: { $elemMatch: { $not: {$elemMatch: { _id: { $nin: [ '6124beef59d728c82088fd59', '6124bf2159d728c82088fd60' ] } } } } } })

The outer $elemMatch, matches any document where any array in cohortsGroups matches the inner condition.

The inner condition

{ $not: {$elemMatch: { _id: { $nin: [ '6124beef59d728c82088fd59', '6124bf2159d728c82088fd60' ] } } } } }

is using a double negative with $not and $nin to find subarrays that have no element with an _id except those that you are searching for.

answered Sep 10, 2021 at 19:35

sheilak

5,8737 gold badges36 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MongoDB query double nested array with matching sets

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related