node.js - Mongoose 使用 $lookup 包含辅助文档中的字段

Question

我想显示所有位置并将订单(如果有)附加到响应中。

位置文档示例:

{
  id: "LOCATION_A_OBJECTID",
  name: "Location A"
},
{
  id: "LOCATION_B_OBJECTID",
  name: "Location B"
},
{
  id: "LOCATION_C_OBJECTID",
  name: "Location C"
}

订单文档示例:(A 2 个订单，B 1 个订单，C 0 个订单)

{
  id: "5e17a001f1e0220def7a2b5d",
  location: "LOCATION_A_OBJECTID", 
  products: [1,2,3]
},
{
  id: "5e17a001f1e0220def7a2b5d",
  name: "LOCATION_B_OBJECTID",
  products: [1,2,3]
},
{
  id: "5e17a001f1e0220def7a2b5d",
  name: "LOCATION_A_OBJECTID",
  products: [1,2,3]
}

预期结果:

{
  id: "LOCATION_A_OBJECTID",
  name: ...
  products: ...
   ...
},
{
  id: "LOCATION_B_OBJECTID",
  name: ...
  products: ...
  ...
}
...

编辑将在这里发布我的架构，因为有些事情是不对的。

订单架构

const mongoose = require('mongoose')

const orderSchema = mongoose.Schema({
  location: {
    type: mongoose.Schema.Types.ObjectId
  },
  timestamp: {
    type: Date, default: Date.now
  },
  status: Number,
  products: Array,
  total:   Number
})

module.exports = mongoose.model('Order', orderSchema)

位置架构:

const mongoose = require('mongoose')

const locationSchema = mongoose.Schema({
  name: String,
  address: String
})

module.exports = mongoose.model('Location', locationSchema)

Answer 1

您需要$lookup with custom pipeline与 name 或 location 字段匹配，然后只需运行 $project 即可获取结果文档所需的格式:

db.Locations.aggregate([
    {
        $lookup: {
            from: "Orders",
            let: { loc_id: "$_id" },
            pipeline: [
                { $match: { $expr: { $eq: [ "$$loc_id", "$_id" ] } } },
                { $project: { _id: 0, products: 1 } }
            ],
            as: "orders"
        }
    },
    {
        $match: { orders: { $ne: [] } }
    },
    {
        $project: {
            _id: 1,
            name: 1,
            products: "$orders.products"
        }
    }
])

Mongo Playground