MongoDB query double nested array

Working with Data
1

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 !

2

Hello @Arthur_Gamblin. I hope you’re doing well.

I’d love to help you with this. But first could you let me know which Mongodb version you’re using?

Thanks :slight_smile:

1 Like
3

Hey @HomeAskle thanks a lot for lending a hand !

I’m running Mongo 4.4 :slight_smile:

So far I’ve success to achieve something with this query

Events.find({
    cohortsGroups: {
      $elemMatch: {
        $or: [{
          '0._id': {
            $in: usersCohortsIds,
          },
          1: {
            $exists: false,
          },
          2: {
            $exists: false,
          },
        }, {
          '0._id': {
            $in: usersCohortsIds,
          },
          '1._id': {
            $in: usersCohortsIds,
          },
          2: {
            $exists: false,
          },
        }, {
          '0._id': {
            $in: usersCohortsIds,
          },
          '1._id': {
            $in: usersCohortsIds,
          },
          '2._id': {
            $in: usersCohortsIds,
          },
        },
        ],
      },
    }
})

But this is a limited solution as I only check for 3 cohorts maximum. My guess is with research that it’s feasible with an aggregation but sadly I’m using this package called casl who is a permission-system connected to mongoose and where conditions expect a MongoQuery and not an agregation.

4

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.

I’ve tried this solution on MongoPlayground but Event-3 is not returned. Mongo playground

btw: I read on CASL docs there is a way to do aggregation: CASL.js
Find the line that says " We can also use the resulting conditions in aggregation pipeline"