$lookup with nested array of objects

Question

My document has array with multiple objects, And I need to find related values and push into same array.

CART

{
    "_id" : ObjectId("5b2b72119fbb60750e0061b9"),
    "cartId" : "1529573905701",
    "supplierId" : ObjectId("5b221d1b63eda2902418434d"),
    "user_id" : "5b20c54651e68057b3cbe745",
    "createdAt" : ISODate("2018-06-21T09:38:25.680Z"),
    "services" : [ 
        {
            "date" : "21-06-2018",
            "timeSlot_id" : ObjectId("5b29e08cb116c31f5b1f56c6"),
            "time" : "03:30 PM - 04:30 PM",
            "serviceId" : ObjectId("5b24aff4abf2494701bc1c15"),
            "cost" : 250,
            "_id" : ObjectId("5b2b72119fbb60750e0061ba")
        }, 
        {
            "_id" : ObjectId("5b2b72329fbb60750e0061bb"),
            "cost" : 250,
            "serviceId" : ObjectId("5b24aff4abf2494701bc1c15"),
            "time" : "03:30 PM - 04:30 PM",
            "timeSlot_id" : ObjectId("5b29e08cb116c31f5b1f56c6"),
            "date" : "21-06-2018"
        }
    ],
    "__v" : 0
}

SERVICES

{
    "_id" : ObjectId("5b24aff4abf2494701bc1c15"),
    "isActivated" : true,
    "supplierServiceId" : 900146649,
    "serviceId" : 99473640,
    "serviceName" : "AC Reparing",
    "description" : "all type of AC, Split AC and Window AC",
    "cost" : 250,
    "serviceDuration" : "60",
    "startTime" : "07:00 AM",
    "endTime" : "08:30 PM",
    "supplierId" : "5b221d1b63eda2902418434d",
    "createdAt" : ISODate("2018-06-16T06:36:36.091Z"),
    "__v" : 0
}

TIMESLOTS

{
    "_id" : ObjectId("5b29e08cb116c31f5b1f56c6"),
    "displayString" : "03:30 PM - 04:30 PM",
    "isActivated" : true,
    "startTime" : "15:30 PM",
    "endTime" : "16:30 PM",
    "duration" : "60",
    "createdAt" : ISODate("2018-06-20T05:05:16.405Z"),
    "__v" : 0
}

Inside the services in CART there are two values serviceId and timeSlot_id

*I need data related to both from related collections. Within same object.

Mongo version -v3.6.5

Please Help to solve this. Thanks in advance.

Ashh · Accepted Answer · 2018-10-05 13:29:13Z

13

You need to $unwind the services in order to match its timeSlot_id and serviceId with the _id of another collection of TimeSlots and ServiceId respectively... And then you can $group it to start the process of "rolling back" into the arrays.

If you have mongodb version 3.6

Cart.aggregate([
  { "$match": { "user_id": _user._id } },
  { "$unwind": "$services" },
  { "$lookup": {
    "from": TimeSlot.collection.name,
    "let": { "timeSlot_id": "$services.timeSlot_id" },
    "pipeline": [
       { "$match": { "$expr": { "$eq": [ "$_id", "$$timeSlot_id" ] } } }
     ],
     "as": "services.timeSlot_id"
  }},
  { "$lookup": {
    "from": Service.collection.name,
    "let": { "serviceId": "$services.serviceId" },
    "pipeline": [
       { "$match": { "$expr": { "$eq": [ "$_id", "$$serviceId" ] } } }
     ],
     "as": "services.serviceId"
  }},
  { "$unwind": "$services.timeSlot_id" },
  { "$unwind": "$services.serviceId" },
  { "$group": {
    "_id": "$_id",
    "services": { "$push": "$services" },
    "cartId" : { "$first": "$cartId" },
    "supplierId" : { "$first": "$supplierId" },
    "user_id" : { "$first": "$user_id" },
    "createdAt" : { "$first": "$createdAt" }
  }}
])

If you have mongodb version prior to 3.6

Cart.aggregate([
  { "$match": { "user_id": _user._id } },
  { "$unwind": "$services" },
  { "$lookup": {
    "from": TimeSlot.collection.name,
    "localField": "services.timeSlot_id",
    "foreignField": "_id",
    "as": "services.timeSlot_id",
  }},
  { "$lookup": {
    "from": Service.collection.name,
    "localField": "services.serviceId",
    "foreignField": "_id",
    "as": "services.serviceId",
  }},
  { "$unwind": "$services.timeSlot_id" },
  { "$unwind": "$services.serviceId" },
  { "$group": {
    "_id": "$_id",
    "services": { "$push": "$services" },
    "cartId" : { "$first": "$cartId" },
    "supplierId" : { "$first": "$supplierId" },
    "user_id" : { "$first": "$user_id" },
    "createdAt" : { "$first": "$createdAt" }
  }}
])

edited Oct 5, 2018 at 13:29

answered Jun 21, 2018 at 10:43

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

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

2 Comments

Shivam Verma Over a year ago

Hey @Ashh I upvoted your solution, its really helpful for me. But I would like to add a scenario here, so let say if serviceId is optional, in that case what will happen is, it will return only those array elements who has both serviceId & timeSlot_id, So do you have any solution for that? How can we overcome this and should be able to return all the array elements even if any key is optional and not available inside object.

Ashh Over a year ago

docs.mongodb.com/manual/reference/operator/aggregation/unwind/….... Use preserveNullAndEmptyArrays on $unwind

Collectives™ on Stack Overflow

$lookup with nested array of objects

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related