CODEWITHSUNDEEP
mongodb
Antonio O.
Antonio O.

Reputation: 555

Sort by subdocuments fields in mongo

Given the following collection:

[
    {
        name: User1
        metadata: [
            {k:"score", v: 5},
            {k:"other", v: 10}
        ]
    },
    {
        name: User2
        metadata: [
            {k:"score", v: 1},
            {k:"other", v: 1}
        ]
    },
    {
        name: User3
        metadata: [
            {k:"score", v: 2},
            {k:"other", v: 0}
        ]
    }
]

How can you sort these items by their "score"? I can sort by the metadata.v field, but I am not sure how to only consider the "v" values for subdocuments that match "k":"score"

The expected output would be:

[
    {
        name: User2
        metadata: [
            {k:"score", v: 1},
            {k:"other", v: 1}
        ]
    },
    {
        name: User3
        metadata: [
            {k:"score", v: 2},
            {k:"other", v: 0}
        ]
    },
    {
        name: User1
        metadata: [
            {k:"score", v: 5},
            {k:"other", v: 10}
        ]
    }
]

Upvotes: 5

Views: 2087

Answers (3)

Neo-coder
Neo-coder

Reputation: 7840

Match your criteria using $elemMatch in aggregate and sort by metadata.v check below query

db.collection.aggregate({"$match":{"metadata":{"$elemMatch":{"k":"score"}}}},
                        {"$sort":{"metadata.v":1}}).pretty()

Upvotes: 0

DAXaholic
DAXaholic

Reputation: 35338

An alternative solution could look like this, whereby the final projection stage is optional if you can live with an additional scoredata property. 

db.yourCollection.aggregate([
    {
        $project: {
            name: 1,
            metadata: 1,
            scoredata: {
                $filter: {
                    input: '$metadata',
                    as: 'metadoc',
                    cond: {
                        $eq: [ '$$metadoc.k', 'score' ]
                    }
                }
            }
        }
    },
    {
        $sort: {
            scoredata: 1
        }
    },
    {
        $project: {
            name: 1,
            metadata: 1            
        }
    }
])

Output looks like this 

/* 1 */
{
    "_id" : ObjectId("5796387b3360e0a2e9dd9fc3"),
    "name" : "User2",
    "metadata" : [ 
        {
            "k" : "score",
            "v" : 1
        }, 
        {
            "k" : "other",
            "v" : 1
        }
    ]
}

/* 2 */
{
    "_id" : ObjectId("5796387b3360e0a2e9dd9fc4"),
    "name" : "User3",
    "metadata" : [ 
        {
            "k" : "score",
            "v" : 2
        }, 
        {
            "k" : "other",
            "v" : 0
        }
    ]
}

/* 3 */
{
    "_id" : ObjectId("5796387b3360e0a2e9dd9fc2"),
    "name" : "User1",
    "metadata" : [ 
        {
            "k" : "score",
            "v" : 5
        }, 
        {
            "k" : "other",
            "v" : 10
        }
    ]
}

Upvotes: 3

JohnnyHK
JohnnyHK

Reputation: 311835

You can do this with aggregate by projecting a copy of each document along with its metadata before unwinding the metadata for filtering and sorting:

db.test.aggregate([
    // Project a copy of each document along with its metadata
    {$project: {doc: '$$ROOT', metadata: '$metadata'}},
    // Duplicate the docs, one per metadata element
    {$unwind: '$metadata'},
    // Filter to just the score metadata for sorting
    {$match: {'metadata.k': 'score'}},
    // Sort on the score values
    {$sort: {'metadata.v': 1}},
    // Project just the original docs in their sorted order
    {$project: {_id: 0, doc: '$doc'}}
])

Output:

{ 
    "doc" : {
        "_id" : ObjectId("57962f891be8975795eee18a"), 
        "name" : "User2", 
        "metadata" : [
            {
                "k" : "score", 
                "v" : 1.0
            }, 
            {
                "k" : "other", 
                "v" : 1.0
            }
        ]
    }
}
{ 
    "doc" : {
        "_id" : ObjectId("57962f891be8975795eee18b"), 
        "name" : "User3", 
        "metadata" : [
            {
                "k" : "score", 
                "v" : 2.0
            }, 
            {
                "k" : "other", 
                "v" : 0.0
            }
        ]
    }
}
{ 
    "doc" : {
        "_id" : ObjectId("57962f891be8975795eee189"), 
        "name" : "User1", 
        "metadata" : [
            {
                "k" : "score", 
                "v" : 5.0
            }, 
            {
                "k" : "other", 
                "v" : 10.0
            }
        ]
    }
}

You could alter the final $project to reshape the documents to promote the doc fields back to the top level if needed.

Upvotes: 1

Related Questions