如何加入深层嵌套数组?

时间:2021-03-06 02:54:34

Here is my actual database schema. company_id is reference object of companies collection and booking_days.consultants.consultant_id is reference object of users collection.

这是我的实际数据库架构。 company_id是公司集合的引用对象,booking_days.consultants.consultant_id是用户集合的引用对象。

I want to join embedded document with company_id and booking_days.consultants.consultant_id.

我想加入嵌入式文档与company_id和booking_days.consultants.consultant_id。

{
    "_id" : ObjectId("5a7040d664544e1bb877deae"),
    "company_id" : ObjectId("5a6eb43f437e6a0d9e00c92f"),
    "booking_days" : [ 
        {
            "booking_date" : ISODate("2018-01-31T00:00:00.000Z"),
            "_id" : ObjectId("5a7040d664544e1bb877deca"),
            "consultants" : [ 
                {
                    "consultant_id" : ObjectId("5a6f2854ce7d6938de1dd52c"),
                    "_id" : ObjectId("5a7040d664544e1bb877decc")
                }, 
                {
                    "consultant_id" : ObjectId("5a6f2854ce7d6938de1dd52f"),
                    "_id" : ObjectId("5a7040d664544e1bb877decb")
                }
            ]
        }, 
        {
            "booking_date" : ISODate("2018-02-01T00:00:00.000Z"),
            "_id" : ObjectId("5a7040d664544e1bb877dec6"),
            "consultants" : [ 
                {
                    "consultant_id" : ObjectId("5a6f2854ce7d6938de1dd52f"),
                    "_id" : ObjectId("5a7040d664544e1bb877dec9")
                }, 
                {
                    "consultant_id" : ObjectId("5a6f2854ce7d6938de1dd52c"),
                    "_id" : ObjectId("5a7040d664544e1bb877dec8")
                }, 
                {
                    "consultant_id" : ObjectId("5a6f2854ce7d6938de1dd52c"),
                    "_id" : ObjectId("5a7040d664544e1bb877dec7")
                }
            ]
        }, 
        {
            "booking_date" : ISODate("2018-02-02T00:00:00.000Z"),
            "_id" : ObjectId("5a7040d664544e1bb877dec4"),
            "consultants" : [ 
                {
                    "consultant_id" : ObjectId("5a6f2854ce7d6938de1dd52c"),
                    "_id" : ObjectId("5a7040d664544e1bb877dec5")
                }
            ]
        }, 

    ],
    "__v" : 0
}

I am using below query.

我正在使用以下查询。

db.getCollection('booking_days').aggregate(
  [
     { $match: { company_id:ObjectId("5a6eb43f437e6a0d9e00c92f") } },
     { 
         $lookup: { 
                    localField: "company_id",
                    from: "companies",
                    foreignField: "_id",
                    as: "companies"
                 },

     },
      { 
         $lookup: { 
                    localField: "booking_days.consultants.consultant_id",
                    from: "users",
                    foreignField: "_id",
                    as: "userssss"
                 },

     },
      { 
        $unwind:"$companies"
      },


   ]
  )

Actual Output

实际产出

{
    "_id" : ObjectId("5a7040d664544e1bb877deae"),
    "company_id" : ObjectId("5a6eb43f437e6a0d9e00c92f"),
    "booking_days" : [ 
        {
            "booking_date" : ISODate("2018-01-31T00:00:00.000Z"),
            "_id" : ObjectId("5a7040d664544e1bb877deca"),
            "consultants" : [ 
                {
                    "consultant_id" : ObjectId("5a6f2854ce7d6938de1dd52c"),
                    "_id" : ObjectId("5a7040d664544e1bb877decc")
                }, 
                {
                    "consultant_id" : ObjectId("5a6f2854ce7d6938de1dd52f"),
                    "_id" : ObjectId("5a7040d664544e1bb877decb")
                }
            ]
        }, 
        {
            "booking_date" : ISODate("2018-02-01T00:00:00.000Z"),
            "_id" : ObjectId("5a7040d664544e1bb877dec6"),
            "consultants" : [ 
                {
                    "consultant_id" : ObjectId("5a6f2854ce7d6938de1dd52f"),
                    "_id" : ObjectId("5a7040d664544e1bb877dec9")
                }, 

            ]
        }, 
    ],
    "__v" : 0,
    "companies" : {
        "_id" : ObjectId("5a6eb43f437e6a0d9e00c92f"),
        "first_name" : "Adrienne Runolfsson",
    },
    "users" : [ 
        {
            "_id" : ObjectId("5a6f2854ce7d6938de1dd52c"),
            "first_name" : "Christ Hamill",

        }, 
        {
            "_id" : ObjectId("5a6f2854ce7d6938de1dd52e"),
            "first_name" : "Miss Dina Kovacek",

        }, 

    ]
}

Excepted output. consultant data will come in booking_days.consultants array.

例外输出。顾问数据将出现在booking_days.consultants数组中。

{
    "_id" : ObjectId("5a7040d664544e1bb877deae"),
    "company_id" : ObjectId("5a6eb43f437e6a0d9e00c92f"),
    "booking_days" : [ 
        {
            "booking_date" : ISODate("2018-01-31T00:00:00.000Z"),
            "_id" : ObjectId("5a7040d664544e1bb877deca"),
            "consultants" : [ 
                {
                    "consultant_id" : {
                        "_id" : ObjectId("5a6f2854ce7d6938de1dd52c"),
                        "first_name" : "Christ Hamill",
                    },
                    "_id" : ObjectId("5a7040d664544e1bb877decc")
                }, 
                {
                    "consultant_id" :  {
                        "_id" : ObjectId("5a6f2854ce7d6938de1dd52e"),
                        "first_name" : "Miss Dina Kovacek",
                    },
                    "_id" : ObjectId("5a7040d664544e1bb877decb")
                }
            ]
        }, 
        {
            "booking_date" : ISODate("2018-02-01T00:00:00.000Z"),
            "_id" : ObjectId("5a7040d664544e1bb877dec6"),
            "consultants" : [ 
                {
                    "consultant_id" :  {
                        "_id" : ObjectId("5a6f2854ce7d6938de1dd52e"),
                        "first_name" : "Miss Dina Kovacek",
                    }, 
                    "_id" : ObjectId("5a7040d664544e1bb877dec9")
                }, 

            ]
        }, 
    ],
    "__v" : 0,
    "companies" : {
        "_id" : ObjectId("5a6eb43f437e6a0d9e00c92f"),
        "first_name" : "Adrienne Runolfsson",
    },

}

1 个解决方案

#1


0  

As such you have to $unwind the localField when it is an embedded document array expect in some cases where localField is an array of scalar ids.

因此,当localField是一个嵌入式文档数组时,你必须展开localField,在某些情况下,localField是一个标量id数组。

$unwind twice as consultant array is two levels deep followed by $lookup to get the name and $group to get back the expected output.

$ unwind两次作为顾问数组是两个级别的深度,然后是$ lookup来获取名称和$ group来获得预期的输出。

db.getCollection('booking_days').aggregate([
  {"$match":{"company_id":ObjectId("5a6eb43f437e6a0d9e00c92f")}},
  {"$lookup":{"localField":"company_id","from":"companies","foreignField":"_id","as":"companies"}},
  {"$unwind":"$companies"},
  {"$unwind":"$booking_days"},
  {"$unwind":"$consultants"},
  {"$lookup":{
    "localField":"booking_days.consultants.consultant_id",
    "from":"users",
    "foreignField":"_id",
    "as":"booking_days.consultants.consultant_id"
  }},
  {"$group":{
    "_id":{"_id":"$_id","booking_days_id":"$booking_days._id"},
    "company_id":{"$first":"$company_id"},
    "booking_date":{"$first":"$booking_days.booking_date"},
    "companies":{"$first":"$companies"},
    "consultants":{"$push":"$booking_days.consultants"}
  }},
  {"$group":{
    "_id":"$_id._id",
    "company_id":{"$first":"$company_id"},
    "companies":{"$first":"$companies"},
    "booking_days":{
      "$push":{
        "_id":"$_id.booking_days_id",
        "booking_date":"$booking_date",
        "consultants":"$consultants"
      }
    }
  }}
])

#1


0  

As such you have to $unwind the localField when it is an embedded document array expect in some cases where localField is an array of scalar ids.

因此,当localField是一个嵌入式文档数组时,你必须展开localField,在某些情况下,localField是一个标量id数组。

$unwind twice as consultant array is two levels deep followed by $lookup to get the name and $group to get back the expected output.

$ unwind两次作为顾问数组是两个级别的深度,然后是$ lookup来获取名称和$ group来获得预期的输出。

db.getCollection('booking_days').aggregate([
  {"$match":{"company_id":ObjectId("5a6eb43f437e6a0d9e00c92f")}},
  {"$lookup":{"localField":"company_id","from":"companies","foreignField":"_id","as":"companies"}},
  {"$unwind":"$companies"},
  {"$unwind":"$booking_days"},
  {"$unwind":"$consultants"},
  {"$lookup":{
    "localField":"booking_days.consultants.consultant_id",
    "from":"users",
    "foreignField":"_id",
    "as":"booking_days.consultants.consultant_id"
  }},
  {"$group":{
    "_id":{"_id":"$_id","booking_days_id":"$booking_days._id"},
    "company_id":{"$first":"$company_id"},
    "booking_date":{"$first":"$booking_days.booking_date"},
    "companies":{"$first":"$companies"},
    "consultants":{"$push":"$booking_days.consultants"}
  }},
  {"$group":{
    "_id":"$_id._id",
    "company_id":{"$first":"$company_id"},
    "companies":{"$first":"$companies"},
    "booking_days":{
      "$push":{
        "_id":"$_id.booking_days_id",
        "booking_date":"$booking_date",
        "consultants":"$consultants"
      }
    }
  }}
])