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

Can orderLine array have entry for same book more than once? — Dheemanth Bhat
– Dheemanth Bhat, Commented Mar 11, 2021 at 18:45

kakakakakakakk · Accepted Answer · 2021-03-12 05:10:32Z

1

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

answered Mar 12, 2021 at 5:10

kakakakakakakk

53115 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

kakakakakakakk Over a year ago

The updated answer does not sum all order line amounts.

Dheemanth Bhat Over a year ago

I might not be understanding your problem correctly but u don't need to perform $sum since you have only one entry per isbn in orderLine array. Anyway if your solution works for you then cheers!

kakakakakakakk Over a year ago

I have more entries, one order has an orderLine array which holds different book order information. The order has { orderNo, cusomerNo, orderLine [ {bookId, amount, price}, {bookId, amount, price} ] }

kakakakakakakk Over a year ago

{ "_id": { "$oid": "604902813dffe54e58a3c0dd" }, "isbn": "0002", "title": "Book2", "pages": 734, "price": { "$numberDecimal": "300" }, "copies": 0, "language": "english", "author": [ "Author2", "Author5" ], "category": [ "Dystopian" ], "genre": [ "Genre-1", "Genre-3" ], "character": [ "Character-1", "Character-2" ], "amount": [ { "amount_total": 2 }, { "amount_total": 3 } ] },

Dheemanth Bhat Over a year ago

Ok got it Perfect!

|

Dheemanth Bhat · Accepted Answer · 2021-03-12 05:43:23Z

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
}

turivishal · Accepted Answer · 2021-03-12 17:31:47Z

0

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

edited Mar 12, 2021 at 17:31

answered Mar 11, 2021 at 17:52

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

3 Comments

kakakakakakakk Over a year ago

if you add some more orders, it fails to calculate. mongoplayground.net/p/rggph4AJoc0

kakakakakakakk Over a year ago

if you know how to expand my answer so that it shows records that have amount_total greather than x, I would appreciate help

turivishal Over a year ago

i have updated the answer with the solution.

Collectives™ on Stack Overflow

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

3 Answers 3

6 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related