Question 204720 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – How to create embedded array of documents and further create embedded document on each element

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search