mongodb $lookup 联表查询

collection  orders :      orders   record example

collection  items :items record example 

$lookup 联表查询

通过item字段连接两个集合orders和items，然后使用 $replaceRoot 中的 $mergeObjects 合并成items和orders的连接文档

db.orders.aggregate( [
   {
      $lookup: {
         from: "items",
         localField: "item",    // field in the orders collection
         foreignField: "item",  // field in the items collection
         as: "fromItems"
      }
   },
   {
      $replaceRoot: { newRoot: { $mergeObjects: [ { $arrayElemAt: [ "$fromItems", 0 ] }, "$$ROOT" ] } }
   },
   { $project: { fromItems: 0 } }
] )

 建立集合：orders和items

db.orders.insertMany( [
   { "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
   { "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 }
] )

db.items.insertMany( [
  { "_id" : 1, "item" : "almonds", description: "almond clusters", "instock" : 120 },
  { "_id" : 2, "item" : "bread", description: "raisin and nut bread", "instock" : 80 },
  { "_id" : 3, "item" : "pecans", description: "candied pecans", "instock" : 60 }
] )

  查询结果为items和orders集合的合并文档：

{
  _id: 1,
  item: 'almonds',
  description: 'almond clusters',
  instock: 120,
  price: 12,
  quantity: 2
},
{
  _id: 2,
  item: 'pecans',
  description: 'candied pecans',
  instock: 60,
  price: 20,
  quantity: 1
}

gpt示例：

代码中，$project操作符通过指定1或0来选择要包含或排除的字段，这里我们只选择了product_id字段，而将_id字段排除。

注意，由于只选择了product_id字段，查询结果中不会包含其他字段，例如订单编号、订单日期等等，这些字段如果需要在查询结果中显示，需要在$project操作符中添加相应的字段。

db.orders.aggregate([
  {
    $lookup: {
      from: "items",
      localField: "product_id",
      foreignField: "product_id",
      as: "order_items"
    }
  },
  {
    $match: {
      "order_items": { $ne: [] }
    }
  },
  {
    $project: {
      _id: 0,
      product_id: 1
    }
  }
])

关联查询的实际操作：

// 关联查询
db.中国裁判文书网法院文书.aggregate([
        {
            $lookup: {
                from: "中国裁判文书网", // 要关联查询的集合
                localField: "7", // goods集合中的_id
                foreignField: "s7", // 要查询的集合的 关联id
                as: "anhao"
            }
        },
			{
				$match: {
					anhao: {$ne:[ ]}            //查询条件，相当于where （2021）豫1302民初9080号
				}
			},
   {
      $replaceRoot: { newRoot: { $mergeObjects: [ { $arrayElemAt: [ "$anhao", 0 ] }, "$$ROOT" ] } }
   },
   { $project: { _id:0,"s7": 1,"s1":1} }
    ])