0

I have three mongoDB collections and their respective documents namely; mosques, prayers and subscriptions as follows:

mosque document:

{
  "_id": "64a48ce14ebcac9ec9a3b0b9",
  "address": "pti road , effurun",
  "city": "Warri",
  "state": "Delta",
  "country": "Nigeria",
  "name": "PTI central mosque",
  "image": "",
  "imamName": "Malam malam",
  "verified": false,
  "active": true,
  "__v": 0
}

prayer document:

{
  "_id":"64a5f9c2f11d298e808170cb".
  "title": "Subhi",
  "adhaanTime": "05:10",
  "iqaamaTime": "05:20",
  "imamName": "Malam Suleiman",
  "mosque":"64a48ce14ebcac9ec9a3b0b9",
  "__v": 0
}

subscription document

{
  "_id":"64aac5e5fd0db3668806e4ee",
  "user":"649966a5cba88e510479da0a",
  "service":"64a75b6e4c57834c539faea6"
  "mosque":"64a48ce14ebcac9ec9a3b0b9",
  "__v": 0
}

I want to be able to query a single mosque with its associated prayers and each prayer with its subscriptions using aggregation like so:

{
  "_id": "64a48ce14ebcac9ec9a3b0b9",
  "address": "pti road , effurun",
  "city": "Warri",
  "state": "Delta",
  "country": "Nigeria",
  "name": "PTI central mosque",
  "image": "",
  "imamName": "Malam malam",
  "verified": false,
  "active": true,
  "prayers": [
    {
      "_id":"64a5f9c2f11d298e808170cb".
      "title": "Subhi",
      "adhaanTime": "05:10",
      "iqaamaTime": "05:20",
      "imamName": "Malam Suleiman",
      "mosque":"64a48ce14ebcac9ec9a3b0b9",
      "__v": 0,
      "subscriptions": [
          {
            "_id":"64aac5e5fd0db3668806e4ee",
            "user":"649966a5cba88e510479da0a",
            "service":"64a75b6e4c57834c539faea6"
            "mosque":"64a48ce14ebcac9ec9a3b0b9",
            "__v": 0
          },
          {
            "_id":"6546775e5fd0db3668806e45446",
            "user":"649966a5cba88e510479da0a",
            "service":"64a75b6e4c57834c539faea6"
            "mosque":"64a48ce14ebcac9ec9a3b0b9",
            "__v": 0
          }
      ]
    },
    {
      "_id":"64a5f9c2f11d298e808170cb".
      "title": "Subhi",
      "adhaanTime": "05:10",
      "iqaamaTime": "05:20",
      "imamName": "Malam Suleiman",
      "mosque":"64a48ce14ebcac9ec9a3b0b9",
      "__v": 0,
      "subscriptions": [
          {
            "_id":"64aac5e5fd0db3668806e4ee",
            "user":"649966a5cba88e510479da0a",
            "service":"64a75b6e4c57834c539faea6"
            "mosque":"64a48ce14ebcac9ec9a3b0b9",
            "__v": 0
          },
          {
            "_id":"6546775e5fd0db3668806e45446",
            "user":"649966a5cba88e510479da0a",
            "service":"64a75b6e4c57834c539faea6"
            "mosque":"64a48ce14ebcac9ec9a3b0b9",
            "__v": 0
          }
      ]
    },
  ]
}

How do I achieve that?

This is the aggregate I attempted to build:

const result = await Mosque.aggregate([
    {
      $match: {
        _id: new mongoose.Types.ObjectId(mosqueId),
      },
    },
    {
      $lookup: {
        from: "prayers",
        localField: "_id",
        foreignField: "mosque",
        as: "prayers",
      },
    },
 
    {
      $set: {
        "prayers.subscriptions": "subscriptions",
      },
    },
  ]);

And I got the following result:

{
    "mosque": [
        {
            "_id": "64a48ce14ebcac9ec9a3b0b9",
            "user": {
                "_id": "649966a5cba88e510479da0a",
                "firstName": "Bello",
                "otherName": "sani",
                "phoneNumber": "+6575455475",
                "email": "",
                "authProvider": "credentials",
                "password": "$2a$10$Ednofrd3dxjWzgDC77rq1umrNhpzAKc.ypzksQ3rbOWTd7lKSFGC6",
                "location": "my town",
                "lga": "my town",
                "state": "my town",
                "country": "my town",
                "verified": false,
                "active": true,
                "role": "user",
                "createdAt": "2023-06-26T10:21:25.067Z",
                "updatedAt": "2023-06-26T10:21:25.067Z",
                "__v": 0
            },
            "address": "pti road , effurun",
            "city": "Warri",
            "state": "Delta",
            "country": "Nigeria",
            "name": "PTI central mosque",
            "image": "",
            "imamName": "Malam Malam",
            "verified": false,
            "active": true,
            "createdAt": "2023-07-04T21:19:29.365Z",
            "updatedAt": "2023-07-04T21:19:29.365Z",
            "__v": 0,
            "prayers": [
                {
                    "_id": "64a5f9c2f11d298e808170cb",
                    "title": "Subhi",
                    "adhaanTime": "05:10",
                    "iqaamaTime": "05:20",
                    "imamName": "Malam malam",
                    "mosque": "64a48ce14ebcac9ec9a3b0b9",
                    "createdAt": "2023-07-05T23:16:18.641Z",
                    "updatedAt": "2023-07-05T23:16:18.641Z",
                    "__v": 0,
                    "subscriptions": "subscriptions"
                },
                {
                    "_id": "64a5fca4f11d298e8081b847",
                    "title": "Zuhr",
                    "adhaanTime": "01:42",
                    "iqaamaTime": "00:50",
                    "imamName": "Malam malam",
                    "mosque": "64a48ce14ebcac9ec9a3b0b9",
                    "createdAt": "2023-07-05T23:28:36.180Z",
                    "updatedAt": "2023-07-06T23:54:38.111Z",
                    "__v": 0,
                    "subscriptions": "subscriptions"
                },
                {
                    "_id": "64a5fecaf11d298e8081dd6e",
                    "title": "Asr",
                    "adhaanTime": "04:05",
                    "iqaamaTime": "04:15",
                    "imamName": "Malam malam",
                    "mosque": "64a48ce14ebcac9ec9a3b0b9",
                    "createdAt": "2023-07-05T23:37:46.418Z",
                    "updatedAt": "2023-07-06T23:55:21.588Z",
                    "__v": 0,
                    "subscriptions": "subscriptions"
                },
                {
                    "_id": "64a75b6e4c57834c539faea6",
                    "title": "Magrib",
                    "adhaanTime": "06:55",
                    "iqaamaTime": "07:05",
                    "imamName": "Malam malam",
                    "mosque": "64a48ce14ebcac9ec9a3b0b9",
                    "createdAt": "2023-07-07T00:25:18.282Z",
                    "updatedAt": "2023-07-07T10:37:26.153Z",
                    "__v": 0,
                    "subscriptions": "subscriptions"
                }
            ]
        }
    ]
}

I wish to replace "subscriptions" with matching list of subscription documents from the subscriptions collection

Improve this question
5
  • What have you tried? What is the association between a subscription and a prayer? Seems like you can probably achieve what you want via $lookup
    – user20042973
    Commented Jul 9, 2023 at 17:44
  • @user20042973 I have updated the questions with what I have implemented and what I got
    – Bello Shehu
    Commented Jul 9, 2023 at 21:16
  • Looks like you're pretty close. Need to do another $lookup to pull the subscriptions. You could either do that as a separate top level stage and then reshape the document in the $set as you're currently doing, or use the pipeline functionality of $lookup to embed the other one in the first (potentially preventing the need to do that final $set at all).
    – user20042973
    Commented Jul 9, 2023 at 22:10
  • @user20042973 thanks so much. Following your suggestion, I was able to complete the aggregation to get exactly what I want.
    – Bello Shehu
    Commented Jul 9, 2023 at 23:03
  • @user20042973 If you look at the result at the moment, the same array of subscriptions are embedded to all prayers. I would like to filter the subscriptions to include only the ones whose service matches the prayer Id.
    – Bello Shehu
    Commented Jul 10, 2023 at 9:39

2 Answers 2

Reset to default
1

Here is the complete aggregation that I was looking for:

const result = await Mosque.aggregate([
    {
      $match: {
        _id: new mongoose.Types.ObjectId(id),
      },
    },
    {
      $lookup: {
        from: "prayers",
        localField: "_id",
        foreignField: "mosque",
        as: "prayers",
      },
    },
    {
      $lookup: {
        from: "subscriptions",
        pipeline: [
          {
            $match: {
              user: new mongoose.Types.ObjectId("649966a5cba88e510479da00"),
            },
          },
        ],
        as: "subs",
      },
    },

    {
      $set: {
        "prayers.subscriptions": "$subs",
      },
    },
    {
      $project: {
        subs: 0,
      },
    },
  ]);

The result of the aggregation:

{
    "mosque": [
        {
            "_id": "64a48ce14ebcac9ec9a3b0b9",
            "user": {
                "_id": "649966a5cba88e510479da0a",
                "firstName": "",
                "otherName": "",
                "phoneNumber": "5865474447",
                "email": "",
                "authProvider": "credentials",
                "password": "$2a$10$Ednofrd3dxjWzgDC77rq1umrNhpzAKc.ypzksQ3rbOWTd7lKSFGC6",
                "location": "my town",
                "lga": "my town",
                "state": "my town",
                "country": "my town",
                "verified": false,
                "active": true,
                "role": "user",
                "createdAt": "2023-06-26T10:21:25.067Z",
                "updatedAt": "2023-06-26T10:21:25.067Z",
                "__v": 0
            },
            "address": "pti road , effurun",
            "city": "Warri",
            "state": "Delta",
            "country": "Nigeria",
            "name": "PTI central mosque",
            "image": "",
            "imamName": "Malam malam",
            "verified": false,
            "active": true,
            "createdAt": "2023-07-04T21:19:29.365Z",
            "updatedAt": "2023-07-04T21:19:29.365Z",
            "__v": 0,
            "prayers": [
                {
                    "_id": "64a5f9c2f11d298e808170cb",
                    "title": "Subhi",
                    "adhaanTime": "05:10",
                    "iqaamaTime": "05:20",
                    "imamName": "Malam malam",
                    "mosque": "64a48ce14ebcac9ec9a3b0b9",
                    "createdAt": "2023-07-05T23:16:18.641Z",
                    "updatedAt": "2023-07-05T23:16:18.641Z",
                    "__v": 0,
                    "subscriptions": [
                        {
                            "_id": "64aac5e5fd0db3668806e4ee",
                            "user": "649966a5cba88e510479da0a",
                            "service": "64a75b6e4c57834c539faea6",
                            "mosque": "64a48ce14ebcac9ec9a3b0b9",
                            "createdAt": "2023-07-09T14:36:21.834Z",
                            "updatedAt": "2023-07-09T14:36:21.834Z",
                            "__v": 0
                        },
                        {
                            "_id": "64aae139fd0db3668806e55d",
                            "user": "649966a5cba88e510479da0a",
                            "service": "64a5f9c2f11d298e808170cb",
                            "mosque": "64a48ce14ebcac9ec9a3b0b9",
                            "createdAt": "2023-07-09T16:32:57.694Z",
                            "updatedAt": "2023-07-09T16:32:57.694Z",
                            "__v": 0
                        }
                    ]
                },
                {
                    "_id": "64a5fca4f11d298e8081b847",
                    "title": "Zuhr",
                    "adhaanTime": "01:42",
                    "iqaamaTime": "00:50",
                    "imamName": "Malam malam",
                    "mosque": "64a48ce14ebcac9ec9a3b0b9",
                    "createdAt": "2023-07-05T23:28:36.180Z",
                    "updatedAt": "2023-07-06T23:54:38.111Z",
                    "__v": 0,
                    "subscriptions": [
                        {
                            "_id": "64aac5e5fd0db3668806e4ee",
                            "user": "649966a5cba88e510479da0a",
                            "service": "64a75b6e4c57834c539faea6",
                            "mosque": "64a48ce14ebcac9ec9a3b0b9",
                            "createdAt": "2023-07-09T14:36:21.834Z",
                            "updatedAt": "2023-07-09T14:36:21.834Z",
                            "__v": 0
                        },
                        {
                            "_id": "64aae139fd0db3668806e55d",
                            "user": "649966a5cba88e510479da0a",
                            "service": "64a5f9c2f11d298e808170cb",
                            "mosque": "64a48ce14ebcac9ec9a3b0b9",
                            "createdAt": "2023-07-09T16:32:57.694Z",
                            "updatedAt": "2023-07-09T16:32:57.694Z",
                            "__v": 0
                        }
                    ]
                },
                {
                    "_id": "64a5fecaf11d298e8081dd6e",
                    "title": "Asr",
                    "adhaanTime": "04:05",
                    "iqaamaTime": "04:15",
                    "imamName": "Malam malam",
                    "mosque": "64a48ce14ebcac9ec9a3b0b9",
                    "createdAt": "2023-07-05T23:37:46.418Z",
                    "updatedAt": "2023-07-06T23:55:21.588Z",
                    "__v": 0,
                    "subscriptions": [
                        {
                            "_id": "64aac5e5fd0db3668806e4ee",
                            "user": "649966a5cba88e510479da0a",
                            "service": "64a75b6e4c57834c539faea6",
                            "mosque": "64a48ce14ebcac9ec9a3b0b9",
                            "createdAt": "2023-07-09T14:36:21.834Z",
                            "updatedAt": "2023-07-09T14:36:21.834Z",
                            "__v": 0
                        },
                        {
                            "_id": "64aae139fd0db3668806e55d",
                            "user": "649966a5cba88e510479da0a",
                            "service": "64a5f9c2f11d298e808170cb",
                            "mosque": "64a48ce14ebcac9ec9a3b0b9",
                            "createdAt": "2023-07-09T16:32:57.694Z",
                            "updatedAt": "2023-07-09T16:32:57.694Z",
                            "__v": 0
                        }
                    ]
                },
                {
                    "_id": "64a75b6e4c57834c539faea6",
                    "title": "Magrib",
                    "adhaanTime": "06:55",
                    "iqaamaTime": "07:05",
                    "imamName": "Malam malam",
                    "mosque": "64a48ce14ebcac9ec9a3b0b9",
                    "createdAt": "2023-07-07T00:25:18.282Z",
                    "updatedAt": "2023-07-07T10:37:26.153Z",
                    "__v": 0,
                    "subscriptions": [
                        {
                            "_id": "64aac5e5fd0db3668806e4ee",
                            "user": "649966a5cba88e510479da0a",
                            "service": "64a75b6e4c57834c539faea6",
                            "mosque": "64a48ce14ebcac9ec9a3b0b9",
                            "createdAt": "2023-07-09T14:36:21.834Z",
                            "updatedAt": "2023-07-09T14:36:21.834Z",
                            "__v": 0
                        },
                        {
                            "_id": "64aae139fd0db3668806e55d",
                            "user": "649966a5cba88e510479da0a",
                            "service": "64a5f9c2f11d298e808170cb",
                            "mosque": "64a48ce14ebcac9ec9a3b0b9",
                            "createdAt": "2023-07-09T16:32:57.694Z",
                            "updatedAt": "2023-07-09T16:32:57.694Z",
                            "__v": 0
                        }
                    ]
                }
            ]
        }
    ]
}

Explanation of the pipelines:

  1. First $match pipeline: This filters the "mosques" collection to grab a mosque with matching ID.

  2. The first $lookup pipeline: This pipeline grabs documents from "prayers" collection and join them with the mosque from the previous "$match" pipeline.

  3. Second $lookup pipeline: Here, subscription documents with matching user ID from the subscriptions collection are grabbed, and the result is named "subs". This ensures that only the currently logged in user's subscriptions are fetched.

  4. The $set pipeline: This pipeline create new field "subscriptions" on each "prayer" document and assigns result (i.e. "subs") from the previous $lookup pipeline to it.

  5. The $project pipeline: $ project pipeline helps to remove the unwanted field "subs" that the last $lookup added to the mosque.

Improve this answer
1

It looks like you were able to successfully create a pipeline that achieved most of your results. The pending requirement expressed in the comments seems to be the following:

If you look at the result at the moment, the same array of subscriptions are embedded to all prayers. I would like to filter the subscriptions to include only the ones whose service matches the prayer Id

That goes back to one of my initial questions which was:

What is the association between a subscription and a prayer?

Per your comment the relationship seems to be the service field in subscription documents refers back to the _id of the prayer documents. Taking that into account and simplifying a bit, one solution to this may be nesting the $lookups as follows:

db.mosques.aggregate([
  {
    $match: {
      _id: ObjectId("64a48ce14ebcac9ec9a3b0b9"),
    },
  },
  {
    $lookup: {
      from: "prayers",
      localField: "_id",
      foreignField: "mosque",
      pipeline: [
        {
          $lookup: {
            from: "subscriptions",
            localField: "_id",
            foreignField: "service",
            pipeline: [
              {
                $match: {
                  user: ObjectId("649966a5cba88e510479da00"),
                },
              },
            ],
            as: "subs",
          },
        }
      ],
      as: "prayers",
    },
  },
])

Demonstration in this playground link.

One minor note here is that I've removed the user: new mongoose.Types.ObjectId("649966a5cba88e510479da00") filter that you had in your version. I did this under the assumption that it is already naturally expressed by the more direct relationship of the service field. If my assumption is incorrect and this user check is separately needed, then you can add that pipeline argument back to the embedded $lookup.

Improve this answer
1
  • That is exactly what I have been trying to achieve. Thank you so much
    – Bello Shehu
    Commented Jul 10, 2023 at 21:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.