We have data stored in MongoDB by country code. Our document looks like the following,

[
    {
        title: '1',
        US: {
            data: { lastReportDate: '2021-09-09' } // will be fetched
        },
        GB: {
            data: { lastReportDate: '2021-09-04' }
       }
    },
    {
        title: '2',
        US: {
            data: { lastReportDate: '2021-09-07' } // will NOT be fetched
        }
    },
    {   
        title: '3',
        US: {
            data: null // will NOT be fetched
        }
    },
    {
        title: '4',
        US: {
            data: null
        }
        GB: {
            data: { lastReportDate: '2021-09-08' } // will be fetched
        },
        NZ: {
            data: { lastReportDate: '2021-09-04' }
        }
    },
    {
        title: '5',
        GB: {
            data: null
        },
        NZ: {
            data: { lastReportDate: '2021-09-06' }  // will be fetched
        }
    }
]

I want to fetch the titles which have the latest dates according to the countries. For EX: in the above DB, we have the latest date for US as '2021-09-09', so I want to fetch all the titles which match this date in lastReportDate. For GB, the latest date is '2021-09-08' and for NZ, its '2021-09-06'.

We have around 180 countries in one document and I want to hit the DB minimum times. So can we build a query that can us latest dates for different countries and then query the Database according to that.

CodePudding user response：

You can try below aggregation:

db.collection.aggregate([
    {
        $project: {
            doc: {
                $objectToArray:"$$ROOT"
            },
            title: "$title"
        }
    },
    {
        $unwind: "$doc"
    },
    {
        $match: {
            "doc.k": { $nin: [ "_id", "title" ] }
        }
    },
    {
        $group: {
            _id: "$doc.k",
            maxDate: { $max: "$doc.v.data.lastReportDate" },
            titles: { $push: { date: "$doc.v.data.lastReportDate", title: "$title" } }
        }
    },
    {
        $project: {
            _id: 0,
            country: "$_id",
            maxTitles: { $filter: { input: "$titles", cond: { $eq: [ "$$this.date", "$maxDate" ] } } }
        }
    }
])

The challenge here is that your countries are represented as keys of your document so you need to start with $obectToArray operator which in conjunction with $unwind will give you a list of countries with corresponding dates and titles.

Once you have them you can use $group to get $max date and then use $filter to get titles related to max date.

Mongo Playground