Question

0

328

Views

How to $sum value on two collections in MongoDB?

My English is not good, hope you understand my question. I'm newbie, i trying use some value from 2 collection.

I have two collections:

Collection 1:

{
 _id: "61241ad82fcb9a369e59868a",
 "name": "aaaa",
 "products": [{
     "_id": "611803232eeaf825336e26c4",
     "name": "products1",
     "pricePolicy": { "costPrice": 10 }
   }, 
   {
     "_id": "611803232eeaf825336e26c5",
     "name": "products2",
     "pricePolicy": { "costPrice": 10 }
  }]
}, 
{
 _id: "61241ad82fcb9a369e59868b",
 "name": "bbb",
 "products": [{
    "_id": "611803232eeaf825336e26c4",
   "name": "products1",
   "pricePolicy": { "costPrice": 20 }
  }, 
  {
   "_id": "611803232eeaf825336e26c5",
   "name": "products2",
   "pricePolicy": { "costPrice": 10 }
  }]
}

And collection 2:

{
  "_id": "61179c8d4ef00f31df195223",
  "name": "zzzz",
  "listProduct": [
     {
       "id": "611803232eeaf825336e26c4",
       "name": "products1",
       "costPrice": 100,
     },
     {
       "id": "611803232eeaf825336e26c6",
       "name": "products3",
       "costPrice": 10,
      }
     ]
},

How to $sum costPrice from two collection and sort product by value? result follow:

   {
  _id: 611803232eeaf825336e26c4,
    name: products1,
      totalCollection1: 30,
        totalCollection2: 10,
          total: 40
}
{
  _id: 611803232eeaf825336e26c5,
    name: products2,
      totalCollection1: 20,
        totalCollection2: 0,
          total: 20
}
{
  _id: 611803232eeaf825336e26c6,
    name: products3,
      totalCollection1: 0,
        totalCollection2: 10,
          total: 10
}

8 months ago · Santiago Trujillo

2 answers

Answer question

0

Answer question

Find remote jobs

Answer 1 · 2022-06-01T15:32:44.143Z

I tried this aggregate query, and it works as desired. I had used the data from your question post and the result is at the bottom:

db.coll1.aggregate([
{ $unwind: "$products" },
{ $sort: { "products._id": 1 } },
{ $group: { 
    _id: "$products._id", 
    productName1: { $first: "$products.name" }, 
    total1: { $sum: "$products.pricePolicy.costPrice" } 

} },
{ $group: { _id: null, productSummary1: { $push: "$$ROOT" } } },
{ $lookup: {
     from: "coll2",
     pipeline: [],
     as: "products2"
}},
{ $unwind: "$products2" },
{ $unwind: "$products2.listProduct" },
{ $sort: { "products2.listProduct.id": 1 } },
{ $group: {
    _id: "$products2.listProduct.id", 
    productSummary1: { $first: "$productSummary1" },
    productName2: { $first: "$products2.listProduct.name" }, 
    total2: { $sum: "$products2.listProduct.costPrice" }
}},
{ $group: { _id: null, 
    productSummary1: { $first: "$productSummary1" }, 
    productSummary2: { $push: { "_id": "$_id", "productName1": "$productName2", "total2": "$total2" } }
}},
{ $project: { products: { $concatArrays: [ "$productSummary1", "$productSummary2" ] } } },
{ $unwind: "$products" },
{ $group: { 
    _id: "$products._id", 
    productName: { $first: "$products.productName1" },
    total1: { $sum: "$products.total1"  },
    total2: { $sum: "$products.total2" },
    total1a: { $push: "$products.total1" },
    total2a: { $push: "$products.total2" },
}},
{ $project: { productName: 1, total1: 1, total2: 1, total: { $sum: { $concatArrays: [ "$total1a", "$total2a" ] } } } },
{ $sort: { total: -1 } }
])

The output:

{
        "_id" : "611803232eeaf825336e26c4",
        "productName" : "products1",
        "total1" : 30,
        "total2" : 100,
        "total" : 130
}
{
        "_id" : "611803232eeaf825336e26c5",
        "productName" : "products2",
        "total1" : 20,
        "total2" : 0,
        "total" : 20
}
{
        "_id" : "611803232eeaf825336e26c6",
        "productName" : "products3",
        "total1" : 0,
        "total2" : 10,
        "total" : 10
}

Answer 2 · 2022-06-01T15:32:44.150Z

$unwind deconstruct the products array
$group by null and construct the products array with required fields
$lookup with collection 2
- $unwind deconstruct the listProduct array
- $project to show required fields
$project to concat both collections array in single field products
$unwind deconstruct the above products array
$group by _id means product id and get the total sum of price for both the collection
$ifNull to check if the value is null then it will return 0
`$addFields to add new field total to get total of both collections price
$sort by total in descending order

db.col1.aggregate([
  { $unwind: "$products" },
  {
    $group: {
      _id: null,
      col1: {
        $push: {
          _id: "$products._id",
          name: "$products.name",
          totalCollection1: "$products.pricePolicy.costPrice"
        }
      }
    }
  },
  {
    $lookup: {
      from: "col2",
      pipeline: [
        { $unwind: "$listProduct" },
        {
          $project: {
            _id: "$listProduct.id",
            name: "$listProduct.name",
            totalCollection2: "$listProduct.costPrice"
          }
        }
      ],
      as: "col2"
    }
  },
  { $project: { products: { $concatArrays: ["$col1", "$col2"] } } },
  { $unwind: "$products" },
  {
    $group: {
      _id: "$products._id",
      name: { $first: "$products.name" },
      totalCollection1: { $sum: { $ifNull: ["$products.totalCollection1", 0] } },
      totalCollection2: { $sum: { $ifNull: ["$products.totalCollection2", 0] } }
    }
  },
  { $addFields: { total: { $sum: ["$totalCollection1", "$totalCollection2"] } } },
  { $sort: { total: -1 } }
])

Playground