CODEWITHSUNDEEP
mongodb
arcseldon
arcseldon

Reputation: 37155

Mongo DB - how to query for id dependent on oldest date in array of a field

Lets say I have a collection called phone_audit with document entries of the following form - _id which is the phone number, and value containing items that always contains 2 entries (id, and a date).

Please see below:

{
        "_id" : {
            "phone_number" : "+012345678"
        },
        "value" : {
            "items" : [
                {
                    "_id" : "c14b4ac1db691680a3fb65320fba7261",
                    "updated_at" : ISODate("2016-03-14T12:35:06.533Z")
                },
                {
                    "_id" : "986b58e55f8606270f8a43cd7f32392b",
                    "updated_at" : ISODate("2016-07-23T11:17:53.552Z")
                }
            ]
        }
},
......

I need to get a list of _id values for every entry in that collection representing the older of the two items in each document.

So in the above - result would be [c14b4ac1db691680a3fb65320fba7261,...]

Any pointers at the type of query to execute would be v.helpful even if the exact syntax is not correct.

Upvotes: 1

Views: 363

Answers (1)

Wake
Wake

Reputation: 1696

With aggregate(), you can $unwind value.items, $sort by update_at, then use $first to get the oldest:

[
{
    "$unwind": "$value.items"
},
{ 
    "$sort": { "value.items.updated_at": 1 } 
},
{
    "$group":{
        _id: "$_id.phone_number",
        oldest:{$first:"$value.items"}
    }
},
{
    "$project":{
        value_id: "$oldest._id"
    }
}
]

Upvotes: 3

Related Questions