使用嵌套对象数组的$lookup

6

我的文档有一个包含多个对象的数组,我需要找到相关的值并将它们推入同一个数组中。

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
} 

服务

{
    "_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
}

CART服务内部,有两个值serviceIdtimeSlot_id

*我需要从相关集合中获取两者的数据。在同一对象内。

Mongo版本 -v3.6.5

请帮忙解决。谢谢提前。

1个回答

12
你需要$unwind服务,以便将其timeSlot_idserviceId与TimeSlots和ServiceId的另一个集合的_id相匹配...然后你可以$group它,开始"回滚"到数组的过程。
如果你有mongodb版本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" }
  }}
])

如果您使用的是MongoDB版本低于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" }
  }}
])

嘿@Ashh,我为你的解决方案点了赞,它对我非常有帮助。但是我想在这里添加一个场景,假设serviceId是可选的,在这种情况下会发生什么是,它将仅返回具有serviceId和timeSlot_id的数组元素,那么你有没有解决方案?我们如何克服这个问题,并且能够返回所有的数组元素,即使任何键是可选的并且不在对象中。 - Shivam Verma
1
使用 $unwindpreserveNullAndEmptyArrays。请参考 https://docs.mongodb.com/manual/reference/operator/aggregation/unwind/#includearrayindex-and-preservenullandemptyarrays. - Ashh

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,
原文链接