MongoDB:如何聚合链接文档?

siv3szwd  于 2022-10-22  发布在  Go
关注(0)|答案(1)|浏览(213)

我有两个收藏,产品和库存
以下是示例值

  • 产品
{
    "_id" : ObjectId("63513c705f31b4bcb75b80ce"),
    "name" : "Coca-cola",    
    "stocks" : [
        ObjectId("63513c705f31b4bcb75b80d0")
        ObjectId("63513c705f31b4bcb75b80d1")
    ]
}
  • 股票
[{
    "_id" : ObjectId("63513c705f31b4bcb75b80d0"),
    "count" : 9,
    "remaining" : 6,
    "costPerItem" : 10,
    "createdAt" : ISODate("2022-10-20T12:17:52.985+0000"),
},
{
    "_id" : ObjectId("63513c705f31b4bcb75b80d1"),
    "count" : 10,
    "remaining" : 3,
    "costPerItem" : 10,
    "createdAt" : ISODate("2022-10-20T12:17:52.985+0000"),
}]

如何查询剩余库存量之和(剩余库存字段)小于例如100的产品?

7gcisfzg

7gcisfzg1#

一种选择是使用:
1.$lookup通过管道获取每个库存的所有剩余库存数量
1.用$sum求和
1.$match相关product s

db.products.aggregate([
  {$lookup: {
      from: "stock",
      let: {stocks: "$stocks"},
      pipeline: [
        {$match: {$expr: {$in: ["$_id", "$$stocks"]}}},
        {$project: {_id: 0, remaining: 1}}
      ],
      as: "remaining"
  }},
  {$set: {remaining: {$sum: "$remaining.remaining"}}},
  {$match: {remaining: {$lt: 100}}}
])

了解它在playground example上的工作原理

相关问题