CODEWITHSUNDEEP
arraysmongodbaggregation-framework
Jonathan
Jonathan

Reputation: 4918

Sort by deep document field in MongoDb

I have a collection called Visitor which has an array of chats and each array has a document called user.
I need to find some documents on this collection and sort them by if they have some specific user in their chats first.

The path for the user id is:
chats.user._id

where: 

chats // array
  user // document
    _id // ObjectId

The below script does sort the documents correctly, however, it expands the chats array and multiplies the document for each chat in the array.

I only need the sorting, so can I sort and not use the unwind pipeline or make it somehow not multiply the documents? 

db.getCollection('Visitor').aggregate([
{$unwind: "$chats"},
{ $match: {'event._id':ObjectId('5c942a3591deb389bfd92579'), 'chats.enabled': {$exists: true}}},
{ 
    "$project": {
        "_id": 1,
        "chats.user._id": 1,
        "weight": {
            "$cond": [
                { "$eq": [ "$chats.user._id", ObjectId("5c942a3591deb389bfd92579") ] },
                10,
                0
            ]
        }
    }
},
{ "$sort": { "weight": -1 } },
])

EDIT: I don't need to sort the inner array, but sort the find command by checking if a specific user is in the chats array.

Some sample of Visitor collection:

[
{
    "_id" : ObjectId("5c9a3a1bd86e0ba64106e90e"),
    "event" : {
        "_id" : ObjectId("5c942a3591deb389bfd92579")
    },
    "chats" : [ 
        {
            "enabled" : false,
            "user" : {
                "_id" : ObjectId("5c81232f09a923b559763418")
            },
            "_id" : ObjectId("5c9a3a1bd86e0ba64106e915")
        }
    ]
},
{
    "_id" : ObjectId("5c9a3a35d86e0ba64106e950"),
    "event" : {
        "_id" : ObjectId("5c942a3591deb389bfd92579")
    },
    "chats" : [ 
        {
            "enabled" : true,
            "user" : {
                "_id" : ObjectId("5c81232f09a923b559763418")
            },
            "_id" : ObjectId("5c9a3a35d86e0ba64106e957")
        }, 
        {
            "enabled" : true,
            "user" : {
                "_id" : ObjectId("5c942a3591deb389bfd92579")
            },
            "_id" : ObjectId("5c9a3a34d86e0ba64106e91d")
        }
    ]
}
]

In the above sample, I need to make the second document to be sorted first because it has the user with the _id ObjectId("5c942a3591deb389bfd92579").

Upvotes: 1

Views: 103

Answers (1)

mickl
mickl

Reputation: 49945

The problem here is that using $unwind you modify initial structure of your documents (you will get one document per chats. I would suggest using $map to get an array of weights based on specified userId and then you can use $max to get final weight

db.col.aggregate([
    { $match: {'event._id':ObjectId('5c942a3591deb389bfd92579'), 'chats.enabled': {$exists: true}}},
    { 
        "$project": {
            "_id": 1,
            "chats.user._id": 1,
            "weight": {
                $max: { $map: { input: "$chats", in: { $cond: [ { $eq: [ "$$this.user._id", ObjectId("5c942a3591deb389bfd92579") ] }, 10, 0  ] } } }
            }
        }
    },
    { "$sort": { "weight": -1 } },
])

Upvotes: 1

Related Questions