MongoDB sum of fields inside objects inside an array that is inside of an object greater than x

Question

//8. isbn numbers of books that sold at least X copies (you decide the value for X).

Book example

  {
    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"],
  },

Order example

{
    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"),
      },
    ],
  },

My try I believe I have a mistake inside the pipeline at the group stage. For now I need at least to have isbn along with the copies sold in one object.

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} }
])

No idea why all are 0's, I was expecting at least some different numbers.

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

Answer 1

The $sum inside $group stage will sum root and grouped fields but here orderLine field is an array, you need to sum that array of numbers before applying $sum, it means nested $sum operation,

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

Playground

---

Try the final solution,

• $match isbn array in orderLine.isbn using $in condition
• $filter to iterate look of orderLine array, and match isbn, it will return filtered documents
• $let declare a orders variable to hold above filtered documents of orderLine, sum the amount from filtered array using $sum
• $project to show required fields, and get total sum of amount_total array

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" }
    }
  }
])

Playground

Answer 2

In your query $lookup is performing a join operation without any condition instead try this query:

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" } 
        }
    }
]);

Test data:

books collection:

/* 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 collection:

/* 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
        }
    ]
}

Output:

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

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

Answer 3

Apparently, this does what I wanted.

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" } } }
])