How to get depended max value from another max value?-CodePudding

Assume there are documents with following structure in the collection:

{
  _id: ObjectId("63af57637d4f4258c1ba460b"),
  metadata: {
    billYear: 2022,
    billNumber: 1
  }
},
{
  _id: ObjectId("63af57637d4f4258c1ba460c"),
  metadata: {
    billYear: 2022,
    billNumber: 2
  }
},
{
  _id: ObjectId("63af57637d4f4258c1ba460d"),
  metadata: {
    billYear: 2023,
    billNumber: 1
  }
}

I need to get the max value of billYear and within this year the max value of billNumber. So in this example the max year is 2023 and the max value in this year is 1.

I tried this attempt:

Data.aggregate( [ { $max : { billNumber : "$billYear" } } ] )

Update

Data.aggregate([{ $group: { _id: null, maxBillYear: { $max: "$metadata.billYear" }}} ])

gives me the max year value:

[ { _id: null, maxBillYear: 2023 } ]

So I would think of running a second query with this year to get the max value for the number. But is it possible to do this directly in a single query?

CodePudding user response：

as for your first attempt to get the max year you were not accessing value correctly if you try like this it will work

Data.aggregate([{ $group: { _id: null, maxBillYear: { $max: "$metadata.billYear" }}} ])

Now the second part of your question to get the max value for the number in single query you can try like this

To get the max bill year from the data

maxBillYear = Data.aggregate([{ $group: { _id: null, maxBillYear: { $max: "$metadata.billYear" }}} ]).first().maxBillYear

To get the max value for the number of bills in a year

Data.aggregate([{ $group: { _id: "$metadata.billYear", maxBillNumber: { $max: "$metadata.billNumber" }}} ])

Yes you can get only max number result on single query

db.collection.aggregate([
  {
    $group: {
      _id: "$metadata.billYear",
      maxBillNumber: {
        $max: "$metadata.billNumber"
      }
    }
  },
  {
    $sort: {
      _id: -1
    }
  },
  {
    $limit: 1
  }
])