mongodb 如何使用$lookup 连接3表查询

假如有这么3张表
1、省份表（test_provinces）

db.test_provinces.insertMany([{
    name:'广东省'
},
{
    name:'广西省'
}
])

2.城市表（test_city）

db.test_city.insertMany([{
    name:'深圳市',
    province_id:ObjectId("63d78a3a07781d41000431aa")
},
{
    name:'广州市',
    province_id:ObjectId("63d78a3a07781d41000431aa")
}
])

3.区域表 （test_district）

db.test_district.insertMany([
    {
    name:'天河区',
    city_id:ObjectId("63d78add07781d41000431ad")
    },
     {
    name:'海珠区',
    city_id:ObjectId("63d78add07781d41000431ad")
    },
    {
    name:'福田区',
    city_id:ObjectId("63d78add07781d41000431ac")
    }
])

重点来了，如何进行连表并返回以下数据格式

{
   name:'广东省',
   city:[{
       name:'深圳市',
       district:[{
            name:'福田区'
       }]
   }]
}

使用MongoDB的$lookup操作符实现三表的连接查询

db.test_provinces.aggregate([
  {
    $lookup: {
      from: "test_city",
      localField: "_id",
      foreignField: "province_id",
      as: "city"
    }
  },
  {
    $unwind: "$city"
  },
  {
    $lookup: {
      from: "test_district",
      localField: "city._id",
      foreignField: "city_id",
      as: "city.district"
    }
  },
  {
    $group: {
      _id: "$_id",
      name: {
        $first: "$name"
      },
      city: {
        $push: "$city"
      }
    }
  }
])

该代码首先使用$lookup操作符连接test_provinces和test_city表，然后使用$unwind操作符拆分新生成的"city"数组，最后再次使用$lookup操作符连接test_district表。最后使用$group操作符分组合并结果，生成期望的数据格式。

这是一个 MongoDB 中的连表查询问题。

在 MongoDB 中，你可以使用 $lookup 操作符来进行连表查询

db.test_provinces.aggregate([
  {
    $lookup: {
      from: "test_city",
      localField: "_id",
      foreignField: "province_id",
      as: "city"
    }
  },
  {
    $unwind: "$city"
  },
  {
    $lookup: {
      from: "test_district",
      localField: "city._id",
      foreignField: "city_id",
      as: "city.district"
    }
  },
  {
    $group: {
      _id: "$_id",
      name: {
        $first: "$name"
      },
      city: {
        $push: "$city"
      }
    }
  }
])

在 MongoDB 中，可以使用 $lookup 连接三张表来查询数据。下面是一个示例代码：

db.test_provinces.aggregate([
   {
      $lookup:
         {
           from: "test_city",
           localField: "_id",
           foreignField: "province_id",
           as: "city"
         }
   },
   {
      $lookup:
         {
           from: "test_district",
           localField: "city._id",
           foreignField: "city_id",
           as: "city.district"
         }
   },
   {
      $project:
         {
           _id: 0,
           name: 1,
           city: {
             $map: {
               input: "$city",
               as: "city",
               in: {
                 name: "$$city.name",
                 district: {
                   $map: {
                     input: "$$city.district",
                     as: "district",
                     in: "$$district.name"
                   }
                 }
               }
             }
           }
         }
   }
])

这个代码会返回每个省份对应的城市和区域，格式如题中所述。

您可以使用MongoDB聚合框架进行连接操作，如下：

php

db.test_provinces.aggregate([
    {
        $lookup: {
            from: "test_city",
            localField: "_id",
            foreignField: "province_id",
            as: "city"
        }
    },
    {
        $unwind: "$city"
    },
    {
        $lookup: {
            from: "test_district",
            localField: "city._id",
            foreignField: "city_id",
            as: "city.district"
        }
    },
    {
        $group: {
            "_id": "$_id",
            "name": {
                $first: "$name"
            },
            "city": {
                $push: "$city"
            }
        }
    }
]).pretty()

这样，您就可以得到需要的数据格式：

css

{
    "_id": ObjectId("63d78a3a07781d41000431aa"),
    "name": "广东省",
    "city": [
        {
            "_id": ObjectId("63d78add07781d41000431ac"),
            "name": "深圳市",
            "province_id": ObjectId("63d78a3a07781d41000431aa"),
            "district": [
                {
                    "_id": ObjectId("63d78afd07781d41000431af"),
                    "name": "福田区",
                    "city_id": ObjectId("63d78add07781d41000431ac")
                }
            ]
        },
        {
            "_id": ObjectId("63d78add07781d41000431ad"),
            "name": "广州市",
            "province_id": ObjectId("63d78a3a07781d41000431aa"),
            "district": [
                {
                    "_id": ObjectId("63d78afd07781d41000431ae"),
                    "name": "天河区",
                    "city_id": ObjectId("63d78add07781d41000431ad")
                },
                {
                    "_id": ObjectId("63d78afd07781d41000431af"),
                    "name": "海珠区",
                    "city_id": ObjectId("63d78add07781d41000431ad")
                }
            ]
        }
    ]
}