Question 218239 in Mongodb Question posted in
The official documentation can be found here.

Using lookup with group in mongodb

I have a bunch of records in this format on mongodb

[
  {_id: 1, date:'2023-07-28',sales:1,ad_id:1,status: 'Active'},
  {_id: 2 , date:'2023-07-29',sales:2,ad_id:1,status: 'Active'},
   {_id: 3, date: '2023-07-30',sales: 4,ad_id:1,status: 'Paused'}
]

Now if I want data from date 2023-07-28 to 2023-07-29 . I want sales to be summed but I want the status from latest record that is of 2023-07-30. So In the end I want result something like this
{'ad_id': 1, sales: 3, status: 'Paused'}.

On mysql it would be something like this

 with ad as (
 SELECT sum(sales) as totalSales,ad_id from table_name
    where date >= '2023-07-28' and date <= '2023-07-29'
 group by ad_id 
)
SELECT * from ad left join (
   SELECT id,ad_id,status from table_name where id in (
       select max(id) from table_name group by ad_id
   )
) t on t.ad_id = ad.ad_id
Tags:

2

Answers


  1. 0

    I’ll give a summary of what the following code is doing:

    1. using facet to create two difference aggregates named as salesSum and recentStatus
    2. In salesSum we are filtering on the date, then grouping on ad_id column and then extracting the sales info from it
    3. In recentStatus we are grouping on ad_id and figuring out the max of _id column through grouping, then filtering it on the maxId to get the recent status using $push: "$$ROOT"
    4. Then doing a kind of cartesian product on salesSum and recentStatus tables using $unwind
    5. Then filtering them on ad_id

    Code:

    db.collection.aggregate([
  {
    $facet: {
      salesSum: [
        {
          // filters on date
          $match: {
            date: {
              $gte: "2023-07-28",
              $lte: "2023-07-29",
              
            },
            
          },
          
        },
        {
          // groups on ad_id and gets sum of sales
          $group: {
            _id: "$ad_id",
            sales: {
              $sum: "$sales",
              
            },
            
          },
          
        },
        {
          // select specific cols
          $project: {
            _id: 0,
            ad_id: "$_id",
            sales: 1,
            
          },
          
        },
        
      ],
      recentStatus: [
        {
          // group on ad_id, get maxId
          // assuming maximum ID is associated with latest entry
          $group: {
            _id: "$ad_id",
            maxId: {
              $max: "$_id",
              
            },
            // pushes all related data which have ad_id values as the same as grouped value
            items: {
              $push: "$$ROOT",
              
            },
            
          },
          
        },
        {
          $unwind: "$items",
          
        },
        {
          // filter items based on maxId
          $match: {
            $expr: {
              $eq: [
                "$maxId",
                "$items._id"
              ],
              
            },
            
          },
          
        },
        {
          $project: {
            _id: 0,
            ad_id: "$_id",
            status: "$items.status",
            
          },
          
        },
        
      ],
      
    },
    
  },
  {
    $unwind: "$salesSum",
    
  },
  {
    $unwind: "$recentStatus",
    
  },
  {
    $match: {
      $expr: {
        $eq: [
          "$salesSum.ad_id",
          "$recentStatus.ad_id"
        ],
        
      },
      
    },
    
  },
  {
    $project: {
      ad_id: "$salesSum.ad_id",
      sales: "$salesSum.sales",
      status: "$recentStatus.status",
      
    },
    
  },
  
])

    Try it on Mongo PlayGround

    Login or Signup to reply.
  2. 0

    Here’s one way you could do it.

    db.collection.aggregate([
  {
    "$group": {
      "_id": "$ad_id",
      "statusObj": {
        // get the last status
        "$max": {
          "date": "$date",
          "_id": "$_id",
          "status": "$status"
        }
      },
      "sales": {
        "$sum": {
          // only sum sales in date range
          "$cond": [
            {"$and": [
                {"$gte": ["$date", "2023-07-28"]},
                {"$lte": ["$date", "2023-07-29"]}
              ]
            },
            "$sales",
            0
          ]
        }
      }
    }
  },
  { // output desired fields
    "$project": {
      "_id": 0,
      "ad_id": "$_id",
      "sales": 1,
      "status": "$statusObj.status"
    }
  }
])

    Try it on mongoplayground.net.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search