mongoDB query for retrieving from nested array collection

Question

{
    "_id" : ObjectId("576155a6cd87b68f7e6e42c9"),
    "First_Name" : "ok",
    "Last_Name" : "jaao",
    "Email" : "[email protected]",
    "Sessions" : [
        {
            "Last_Login" : "Wed, Jun 14, 2016 6:48 PM",
            "Class" : "fb",
            "ID" : "123"
        },
        {
            "Last_Login" : "Wed, Jun 15, 2016 6:48 PM",
            "ID" : "111",
            "Class" : "fb"
        }
    ],
    "Count" : 2
},
{
    "_id" : ObjectId("576155ccf6d8979e7e77df27"),
    "First_Name" : "abc",
    "Last_Name" : "xyz",
    "Email" : "[email protected]",
    "Sessions" : [
        {
            "Last_Login" : "Wed, Jun 15, 2016 6:49 PM",
            "Class" : "fb",
            "ID" : "123"
        }
    ],
    "Count" : 1
}

This is my json structure. and I want a mongoDB query which retrieves every user that has logged in today i.e., whose Last_Login has today's date.

I want my output as:

{
    "_id" : ObjectId("576155a6cd87b68f7e6e42c9"),
    "First_Name" : "ok",
    "Last_Name" : "jaao",
    "Email" : "[email protected]",
    "Sessions" : [
        {
            "Last_Login" : "Wed, Jun 15, 2016 6:48 PM",
            "ID" : "111",
            "Class" : "fb"
        }
    ],
    "Count" : 2
},
{
    "_id" : ObjectId("576155ccf6d8979e7e77df27"),
    "First_Name" : "abc",
    "Last_Name" : "xyz",
    "Email" : "[email protected]",
    "Sessions" : [
        {
            "Last_Login" : "Wed, Jun 15, 2016 6:49 PM",
            "Class" : "fb",
            "ID" : "123"
        }
    ],
    "Count" : 1
}

Dates stored like that will need complex queries that may be ineffective. Consider changing your schema to have Last_Login dates stored as ISODate() — chridam
– chridam, Commented Jun 15, 2016 at 13:39
Last_Login field contains data in the format of moment().format('llll') provided by moment.js library, do i still need to change it to ISODate() ??? — Shravan Kumar Suthaar
– Shravan Kumar Suthaar, Commented Jun 16, 2016 at 6:09
Yes, it's better to store dates in MongoDB as proper Date() types instead of storing them as strings in the above format. — chridam
– chridam, Commented Jun 16, 2016 at 6:18
can u please give me an example of inserting it as an ISODate(), ryt now im using it as : Last_Login : moment.format('llll') — Shravan Kumar Suthaar
– Shravan Kumar Suthaar, Commented Jun 16, 2016 at 7:48
moment() has a toDate() method that you can use as it returns a JavaScript Date object or simply create a new instance of the JS Date object. So you can do it like Last_Login: moment().toDate() or as Last_Login: new Date() — chridam
– chridam, Commented Jun 16, 2016 at 7:52

Luiz de Prá · Accepted Answer · 2016-06-17 14:04:54Z

1

You will need $elemMatch and aggregate.

db.users.aggregate([
    {
        $unwind: "$Sessions"
    },
    {
        $match: {
            "Sessions.Last_Login": {
                $gte: ISODate("2016-06-16T00:00:00.0Z"),
                $lt: ISODate("2016-06-17T00:00:00.0Z")
            }
        }
    },
    {
        $group: {
            _id: {
                _id: "$_id",
                First_Name: "$First_Name",
                Last_Name: "$Last_Name"
            },
            Sessions: {
                $push: "$Sessions"
            }
        }
    },
    {
        $project: {
            _id: "$_id._id",
            First_Name: "$_id.First_Name",
            Last_Name: "$_id.Last_Name",
            Sessions: "$Sessions"
        }
    }
])

So the query will do those steps:

$unwind all Sessions elements
$match documents inside the date range
$group together documents by _id, First_Name, Last_Name
$project documents to look like the original format

I omitted some fields, but you can easily add it in $group and $project steps. And of course you'll need to change the date range.

I'm concerned about the performance of this query in a big collection. Maybe is better if you use the first query I gave and filter the sessions you want in your code.

Edit:

As @chridam said, this query will work only if you change Last_Login to ISODate(), what is recommended.

Edit 2:

Updating the query to use aggregate and match the request of only fetch Sessions inside the date range.

This is the old version:

db.users.filter({
    'Sessions': {
        '$elemMatch': {
            'Last_Login': {
                '$gte': ISODate("2016-06-16T00:00:00.0Z"),
                '$lt': ISODate("2016-06-17T00:00:00.0Z")
            }
        }
    }
})

edited Jun 17, 2016 at 14:04

answered Jun 15, 2016 at 13:48

Luiz de Prá

1,4752 gold badges12 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

chridam Over a year ago

Not so sure if the query will work since the Last_Login date is given by the OP as a string.

Luiz de Prá Over a year ago

@chridam Sure, I didn't notice.

Shravan Kumar Suthaar Over a year ago

it says ISODate() not defined... other alternatives??

Shravan Kumar Suthaar Over a year ago

ok.. got it done using Date() but im not getting the expected output. it outputs all the data inside the matching object, even the yesterday's login time

Luiz de Prá Over a year ago

So you want to return only the last login session?

|

Collectives™ on Stack Overflow

mongoDB query for retrieving from nested array collection

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related