大于x的数组中对象内字段的MongoDB和

Question

//8.至少售出X册的图书数目(由你决定X的价值)。

书例

  {
    isbn: "0001",
    title: "Book1",
    pages: NumberInt("150"),
    price: NumberDecimal("321.2"),
    copies: NumberInt("3"),
    language: "english",
    author: ["Author1"],
    category: ["Space Opera"],
    genre: ["Genre-1", "Genre-2"],
    character: ["Character-1", "Character-2"],
  },

序例

{
    orderNo: "3",
    customerNo: "0003", 
    date: {
      day: NumberInt("25"),
      month: NumberInt("02"),
      year: NumberInt("2021"),
    },
    orderLine: [
      {
        isbn: "0006", 
        price: NumberDecimal("341.0"),
        amount: NumberInt("2"),
      },
      {
        isbn: "0007", 
        price: NumberDecimal("170.5"),
        amount: NumberInt("1"),
      },
    ],
  },

我的努力--我相信我在小组阶段的比赛中犯了一个错误。现在，我至少需要有isbn和在一个对象中出售的副本。

db.books.aggregate([ // editing this
  { $match : {} },
  {
    $lookup : 
      {
        from : "orders",
        pipeline : [
          {
            $group : 
            {
              _id: null,
              amount_total : { $sum : "$orderLine.amount" }
            }
          },
          { $project : { _id : 0,  amount_total : 1} }
        ],
        as : "amount"
      }
  },
  { $project : { _id : 0, isbn : 1, amount : 1} }
])

不知道为什么都是0，我在期待至少有几个不同的数字。

{
    "isbn": "0001",
    "amount": [
      {
        "amount_total": 0
      }
    ]
  },
  {
    "isbn": "0002",
    "amount": [
      {
        "amount_total": 0
      }
    ]
  },
  {
    "isbn": "0003",
    "amount": [
      {
        "amount_total": 0
      }
    ]
  },// and so on

Answer 1

显然，这做了我想做的事。

db.books.aggregate([
  {
    $lookup: {
      from: "orders",
      let: { isbn: "$isbn" },   // Pass this variable to pipeline for Joining condition.
      pipeline: [
        { $unwind: "$orderLine" },
        {
          $match: {
            // Join condition.
            $expr: { $eq: ["$orderLine.isbn", "$$isbn"] }
          }
        },
        {
          $project: { _id: 0 , orderNo : 1,  "orderLine.amount": 1}
        }
      ],
      as: "amount"
    }
  }, { $project : { _id : 0, isbn : 1, amount_total : { $sum : "$amount.orderLine.amount" } } }
])

Answer 2

在您的查询中，$lookup是在没有任何条件的情况下执行连接操作，而是尝试以下查询：

db.books.aggregate([
    {
        $lookup: {
            from: "orders",
            let: { isbn: "$isbn" },
            pipeline: [
                { $unwind: "$orderLine" },
                {
                    $match: {
                        $expr: { $eq: ["$orderLine.isbn", "$$isbn"] }
                    }
                }
            ],
            as: "amount"
        }
    },
    { 
        $project: { 
            _id: 0, 
            isbn: 1, 
            amount_total: { $sum: "$amount.orderLine.amount" } 
        }
    }
]);

测试数据：

books收藏：

/* 1 createdAt:3/12/2021, 10:41:13 AM*/
{
    "_id" : ObjectId("604af7f14b5860176c2254b7"),
    "isbn" : "0001",
    "title" : "Book1"
},

/* 2 createdAt:3/12/2021, 10:41:13 AM*/
{
    "_id" : ObjectId("604af7f14b5860176c2254b8"),
    "isbn" : "0002",
    "title" : "Book2"
}

orders收藏：

/* 1 createdAt:3/12/2021, 11:10:51 AM*/
{
    "_id" : ObjectId("604afee34b5860176c2254ce"),
    "orderNo" : "1",
    "customerNo" : "0001",
    "orderLine" : [
        {
            "isbn" : "0001",
            "price" : 341,
            "amount" : 2
        },
        {
            "isbn" : "0002",
            "price" : 170.5,
            "amount" : 1
        },
        {
            "isbn" : "0003",
            "price" : 190.5,
            "amount" : 3
        }
    ]
},

/* 2 createdAt:3/12/2021, 11:10:51 AM*/
{
    "_id" : ObjectId("604afee34b5860176c2254cf"),
    "orderNo" : "3",
    "customerNo" : "0003",
    "orderLine" : [
        {
            "isbn" : "0001",
            "price" : 341,
            "amount" : 2
        },
        {
            "isbn" : "0002",
            "price" : 170.5,
            "amount" : 1
        },
        {
            "isbn" : "0003",
            "price" : 190.5,
            "amount" : 3
        }
    ]
}

输出：

/* 1 */
{
    "isbn" : "0001",
    "amount_total" : 4
},

/* 2 */
{
    "isbn" : "0002",
    "amount_total" : 2
}

Answer 3

$sum在$group阶段中将与根字段和分组字段相加，但是这里的orderLine字段是一个数组，您需要在应用$sum之前对该数组进行加和，这意味着嵌套$sum操作，

{
  $group: {
    _id: null,
    amount_total: {
      $sum: {
        $sum: "$orderLine.amount"
      }
    }
  }
}

游乐场

尝试最后的解决方案，

• $match isbn数组在orderLine.isbn中使用$in条件
• $filter来迭代orderLine数组的外观，并匹配isbn，它将返回过滤过的文档。
• $let声明一个orders变量以保存已过滤的orderLine文档，并使用$sum对过滤后的数组中的amount进行求和。
• $project以显示所需字段，并获得amount_total数组的总和

db.books.aggregate([
  {
    $lookup: {
      from: "orders",
      let: { isbn: "$isbn" },
      pipeline: [
        { $match: { $expr: { $in: ["$$isbn", "$orderLine.isbn"] } } },
        {
          $project: {
            _id: 0,
            amount_total: {
              $let: {
                vars: {
                  orders: {
                    $filter: {
                      input: "$orderLine",
                      cond: { $eq: ["$$this.isbn", "$$isbn"] }
                    }
                  }
                },
                in: { $sum: "$$orders.amount" }
              }
            }
          }
        }
      ],
      as: "amount"
    }
  },
  {
    $project: {
      _id: 0,
      isbn: 1,
      amount_total: { $sum: "$amount.amount_total" }
    }
  }
])

游乐场