Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

extract subarray value in mongodb

MongoDB noob here...

I have a collection as follows...

    > db.students.find({_id:22},{scores:[{type:'exam'}]}).pretty()
    {
        "_id" : 22,
        "scores" : [
            {
                "type" : "exam",
                "score" : 75.04996547553947
            },
            {
                "type" : "quiz",
                "score" : 10.23046475899236
            },
            {
                "type" : "homework",
                "score" : 96.72520512117761
            },
            {
                "type" : "homework",
                "score" : 6.488940333376703
            }
        ]
    }

how do I display only the quiz score via mongo shell?

like image 209
thefonso Avatar asked Nov 19 '12 11:11

thefonso


1 Answers

You have some syntax in your original example which probably isn't doing what you expect .. that is, it looks like your intent was to only match scores for a specific type ('exam' in your example, 'quiz' by your description).

Below are some examples using the MongoDB 2.2 shell.

$elemMatch projection

You can use the $elemMatch projection to return the first matching element in an array:

db.students.find(
    // Search criteria
    { '_id': 22 },

    // Projection
    { _id: 0, scores: { $elemMatch: { type: 'exam' } }}
)

The result will be the matching element of the array for each document, eg:

{ "scores" : [ { "type" : "exam", "score" : 75.04996547553947 } ] }

Aggregation Framework

If you want to display more than one matching value or reshape the result document instead of returning the full matching array element, you can use the Aggregation Framework:

db.students.aggregate(
    // Initial document match (uses index, if a suitable one is available)
    { $match: {
        '_id': 22, 'scores.type' : 'exam'
    }},

    // Convert embedded array into stream of documents
    { $unwind: '$scores' },

    // Only match scores of interest from the subarray
    { $match: {
        'scores.type' : 'exam'
    }},

    // Note: Could add a `$group` by _id here if multiple matches are expected

    // Final projection: exclude fields with 0, include fields with 1
    { $project: {
        _id: 0,
        score: "$scores.score"
    }}
)

The result in this case includes would be:

{ "result" : [ { "score" : 75.04996547553947 } ], "ok" : 1 }
like image 124
Stennie Avatar answered Oct 23 '22 14:10

Stennie