Complex Mongo Query

Question

Refer to question Match conditions and latest date from array whose good solution is provided by Neil Lunn.

db.chat.find().pretty().limit(2)
{
    "_id" : ObjectId("593921425ccc8150f35e7662"),
    "user1" : 1,
    "user2" : 2,
    "messages" : [
        {
            "sender" : 1,
            "datetime" : ISODate("2017-05-01T00:00:00Z"),
            "body" : "hiii 120"
        },
        {
            "sender" : 1,
            "datetime" : ISODate("2017-06-01T00:00:00Z"),
            "body" : "hiii 121"
        },
        {
            "sender" : 2,
            "datetime" : ISODate("2017-06-10T00:00:00Z"),
            "body" : "hiii 2"
        }
    ]
}
{
    "_id" : ObjectId("593921425ccc8150f35e7663"),
    "user1" : 1,
    "user2" : 3,
    "messages" : [
        {
            "sender" : 1,
            "datetime" : ISODate("2017-05-02T00:00:00Z"),
            "body" : "hiii 130"
        },
        {
            "sender" : 1,
            "datetime" : ISODate("2017-06-02T00:00:00Z"),
            "body" : "hiii 131"
        },
        {
            "sender" : 3,
            "datetime" : ISODate("2017-06-10T00:00:00Z"),
            "body" : "hiii 2"
        }
    ]
}

The problem statement here is I need list of users where messages.sender=1 order by datetime desc.

i.e output :

    {
        "sender" : 1,
        "receiver" : 2,
        "datetime" : ISODate("2017-06-02T00:00:00Z"),
        "body" : "hiii 131"
    },
    {
        "sender" : 1,
        "receiver" : 2,
        "datetime" : ISODate("2017-06-01T00:00:00Z"),
        "body" : "hiii 121"
    },                      
    {
        "sender" : 1,
        "receiver" : 3,
        "datetime" : ISODate("2017-05-02T00:00:00Z"),
        "body" : "hiii 130"
    },
    {
        "sender" : 1,
        "receiver" : 3,
        "datetime" : ISODate("2017-05-01T00:00:00Z"),
        "body" : "hiii 120"
    },

Tried many query but not able to get the desired output.

Explanation the output :

db.chat.find().pretty().limit(2)
{
    "_id" : ObjectId("593921425ccc8150f35e7662"),
    "user1" : 1,
    "user2" : 2,
    "messages" : [
        {
            "sender" : 1,
            **"datetime" : ISODate("2017-05-01T00:00:00Z"),**--- message will apear at No4 as sender=1. Also we need data of user1/user2 which is not equal to 1 i.e 2.
            "body" : "hiii 120"
        },
        {
            "sender" : 1,
            **"datetime" : ISODate("2017-06-01T00:00:00Z"),**--- message will apear at No2 as sender=1. Also we need data of user1/user2 which is not equal to 1 i.e 2.
            "body" : "hiii 121"
        },
        {
            "sender" : 2,
            "datetime" : ISODate("2017-06-10T00:00:00Z"),
            "body" : "hiii 2"
        }
    ]
}
{
    "_id" : ObjectId("593921425ccc8150f35e7663"),
    "user1" : 1,
    "user2" : 3,
    "messages" : [
        {
            "sender" : 1,
            **"datetime" : ISODate("2017-05-02T00:00:00Z"),** --- message will apear at No3 as sender=1. Also we need data of user1/user2 which is not equal to 1 i.e 3.
            "body" : "hiii 130"
        },
        {
            "sender" : 1,
            **"datetime" : ISODate("2017-06-02T00:00:00Z"),** --- message will apear at top as sender=1 and of max datetime. Also we need data of user1/user2 which is not equal to 1 i.e 3.
            "body" : "hiii 131"
        },
        {
            "sender" : 3,
            "datetime" : ISODate("2017-06-10T00:00:00Z"),
            "body" : "hiii 2"
        }
    ]
}

I don't get it. Is this just the content from within the arrays of is there something that is supposed to be done to it? Can you elaborate on how the data is supposed to be selected? — Neil Lunn, Jun 23 '17 at 12:56
Yes Its content inside the array + other user which is not the sender ... Most importantly need to sort based on datetime of all matched rows of messages.... — , Jun 23 '17 at 13:02
Could you be a bit more descriptive please? You basically wrote two sentences in the question. It needs more explanation than that. For example there are items in the source that are not in the output. People reading deserve to understand how the output is achieved. — Neil Lunn, Jun 23 '17 at 13:03

p.streef · Accepted Answer · 2017-06-26T08:05:55.953

1

Tested with the data you supplied. This works:

db.getCollection('test').aggregate([
{$unwind:"$messages"},
{$match : {"messages.sender" : 1}},
{$project:{
    "_id" : 0, 
    "sender" : "$messages.sender", 
    "datetime" : "$messages.datetime",
    "body" : "$messages.body",
    "receiver" : {$cond : {if: { $eq : ["$messages.sender","$user1"]}, then: "$user2", else: "$user1"}},
    }
},
{$sort : {"datetime" : 1}}
])

$unwind will create a seperate object for all messages.

$match keeps only the ones with sender = 1

$project turns the data into the format you requested.

Because we want the receiver to be either user 1 or user 2 depending on sender, we need the $cond statement.

EDIT: Add $sort, because I just read your comment about a need for sorting by datetime

edited Jun 26 '17 at 08:05

answered Jun 26 '17 at 07:29

p.streef

3,652
3
26
50

can we do this without using unwind as it a costly operation ? – Jun 27 '17 at 13:35
It might be possible with a lot more work. But perhaps if time is an issue the suggested output is the problem. Perhaps this problem is better fixed on the application side than in the database? – p.streef Jun 27 '17 at 13:41

Complex Mongo Query

1 Answers1