MongoDB中文档连接查询的问题

Question

我试图加入以下两个基于相关字段的文档，即users._id和addressschemas.userId。

示例users文档；

{
    "_id" : ObjectId("58f74901b3ec8e2d0bc898d5"),
    "fullName" : "public user",
    "firstName" : "public",
    "lastName" : "user",
    "email" : "user@gmail.com",
    "password" : "$2a$10$thYCJS62ejUxxzIlpIfiXeRxswQPzU4sVhc4PeoGxAXN3/IBxnxeO",
    "mobile" : "9876543210",
    "countryCode" : "+91",
    "accountEnabled" : true,
    "__v" : 0
}

示例addressschemas文档；

{
    "_id" : ObjectId("58f9b18880a35c281078f42b"),
    "userId" :  ObjectId("58f74901b3ec8e2d0bc898d5"),
    "deliveryName" : "test",
    "deliveryAddress" : "new address",
    "deliveryState" : "Haryana",
    "deliveryCity" : "Gurgaon",
    "deliveryZipCode" : "122001",
    "deliveryCountryCode" : "+91",
    "deliveryMobile" : "9876543211",
    "deliveryEmail" : "subadmin2@email.com",
    "__v" : 0
}

上述连接的预期结果应如下；

{

    "_id" : ObjectId("58f74901b3ec8e2d0bc898d5"),
    "fullName" : "public user",
    "firstName" : "public",
    "lastName" : "user",
    "email" : "user@gmail.com",
    "password" : "$2a$10$thYCJS62ejUxxzIlpIfiXeRxswQPzU4sVhc4PeoGxAXN3/IBxnxeO",
    "mobile" : "9876543210",
    "countryCode" : "+91",
    "accountEnabled" : true,

    "userId" :ObjectId("58f74901b3ec8e2d0bc898d5"),
    "deliveryName" : "test",
    "deliveryAddress" : "new address",
    "deliveryState" : "Haryana",
    "deliveryCity" : "Gurgaon",
    "deliveryZipCode" : "122001",
    "deliveryCountryCode" : "+91",
    "deliveryMobile" : "9876543211",
    "deliveryEmail" : "subadmin2@email.com"
}

以及我用来实现这一目标的查询；

db.getCollection('users').aggregate([
  {
    $match: {
      _id: ObjectId("58f74901b3ec8e2d0bc898d5")
    }
  },
  {
    $lookup: {
      from: "addressschemas",
      localField: "_id",
      foreignField: "usersId",
      as: "results"
    }
  },
  {
    $project: {
      addressschemas: {
        $filter: {
          input: "$_id",
          cond: {
            $eq: ['$$addressschemas.userId', '$users._id']
          }
        }
      }
    }
  }
])

但是上面的查询结果是空的，有人能告诉我它的问题吗？

Answer 1

您可以通过以下查询获得一个近似结果；

db.getCollection('users').aggregate([
  {
    $match: {
      _id: ObjectId("58f74901b3ec8e2d0bc898d5")
    }
  },
  {
    $lookup: {
      from: "addressschemas",
      localField: "_id",
      foreignField: "userId",
      as: "address"
    }
  },
  {
    $unwind : "$address" 
  },
  {
    $project: {
      __v: 0,
      "address.__v": 0,
      "address._id": 0,
      "address.userId": 0
    }
  }
])

这将导致以下文件，使用您的测试数据；

{
  "_id": ObjectId("58f74901b3ec8e2d0bc898d5")
  "fullName": "public user",
  "firstName": "public",
  "lastName": "user",
  "email": "user@gmail.com",
  "password": "$2a$10$thYCJS62ejUxxzIlpIfiXeRxswQPzU4sVhc4PeoGxAXN3/IBxnxeO",
  "mobile": "9876543210",
  "countryCode": "+91",
  "accountEnabled": true,
  "address": {
    "deliveryName": "test",
    "deliveryAddress": "new address",
    "deliveryState": "Haryana",
    "deliveryCity": "Gurgaon",
    "deliveryZipCode": "122001",
    "deliveryCountryCode": "+91",
    "deliveryMobile": "9876543211",
    "deliveryEmail": "subadmin2@email.com"
  }
}

由于$lookup与users文档的原始字段一起创建了一个新字段，因此在这个新的address字段下的数组中将有addressschemas的值，因此我使用$undwind操作来处理这个数组。没有一种简单的方法可以将它们向上移动到根，因为这是在您想要的结果中，我认为最好像这样表示地址数据。

显然，我假设用户文档只包含一个单数地址，如果不是这样的话，$unwind操作将产生上面的多个对象，每个对象包含一个不同的addess字段，但是根据您想要的结果，我推断这两个文档之间存在一对一的关系。

Answer 2

MongoDB用两种方式定义关系

(1)参考文献(2)嵌入式文档

在实体间存在许多关系的应用程序中，引用更可取。

在实体间存在一对多关系的应用程序中，嵌入式文档更可取。

在上述场景中，用户和地址模式之间似乎存在一对多的关系。

因此，可以将各自用户的地址定义为用户文档中的嵌入式文档，从而便于在单次访问数据库服务器时检索用户文档。

E.g

{
    "_id": ObjectId("58f74901b3ec8e2d0bc898d5"),
    "fullName": "public user",
    "firstName": "public",
    "lastName": "user",
    "email": "user@gmail.com",
    "password": "$2a$10$thYCJS62ejUxxzIlpIfiXeRxswQPzU4sVhc4PeoGxAXN3/IBxnxeO",
    "mobile": "9876543210",
    "countryCode": "+91",
    "accountEnabled": true,
    "__v": 0,
    "addressschemas": [
        {
            "deliveryName": "test",
            "deliveryAddress": "new address",
            "deliveryState": "Haryana",
            "deliveryCity": "Gurgaon",
            "deliveryZipCode": "122001",
            "deliveryCountryCode": "+91",
            "deliveryMobile": "9876543211",
            "deliveryEmail": "subadmin2@email.com"
        }
    ]
}