[mongodb] How to filter array in subdocument with MongoDB

I have array in subdocument like this

{
    "_id" : ObjectId("512e28984815cbfcb21646a7"),
    "list" : [
        {
            "a" : 1
        },
        {
            "a" : 2
        },
        {
            "a" : 3
        },
        {
            "a" : 4
        },
        {
            "a" : 5
        }
    ]
}

Can I filter subdocument for a > 3

My expect result below

{
    "_id" : ObjectId("512e28984815cbfcb21646a7"),
    "list" : [
        {
            "a" : 4
        },
        {
            "a" : 5
        }
    ]
}

I try to use $elemMatch but returns the first matching element in the array

My query:

db.test.find( { _id" : ObjectId("512e28984815cbfcb21646a7") }, { 
    list: { 
        $elemMatch: 
            { a: { $gt:3 } 
            } 
    } 
} )

The result return one element in array

{ "_id" : ObjectId("512e28984815cbfcb21646a7"), "list" : [ { "a" : 4 } ] }

and I try to use aggregate with $match but not work

db.test.aggregate({$match:{_id:ObjectId("512e28984815cbfcb21646a7"), 'list.a':{$gte:5}  }})

It's return all element in array

{
    "_id" : ObjectId("512e28984815cbfcb21646a7"),
    "list" : [
        {
            "a" : 1
        },
        {
            "a" : 2
        },
        {
            "a" : 3
        },
        {
            "a" : 4
        },
        {
            "a" : 5
        }
    ]
}

Can I filter element in array to get result as expect result?

This question is related to mongodb filter mongodb-query aggregation-framework

The answer is


Use $filter aggregation

Selects a subset of the array to return based on the specified condition. Returns an array with only those elements that match the condition. The returned elements are in the original order.

db.test.aggregate([
    {$match: {"list.a": {$gt:3}}}, // <-- match only the document which have a matching element
    {$project: {
        list: {$filter: {
            input: "$list",
            as: "list",
            cond: {$gt: ["$$list.a", 3]} //<-- filter sub-array based on condition
        }}
    }}
]);

Above solution works best if multiple matching sub documents are required. $elemMatch also comes in very use if single matching sub document is required as output

db.test.find({list: {$elemMatch: {a: 1}}}, {'list.$': 1})

Result:

{
  "_id": ObjectId("..."),
  "list": [{a: 1}]
}

Examples related to mongodb

Server Discovery And Monitoring engine is deprecated Avoid "current URL string parser is deprecated" warning by setting useNewUrlParser to true MongoNetworkError: failed to connect to server [localhost:27017] on first connect [MongoNetworkError: connect ECONNREFUSED 127.0.0.1:27017] Failed to auto-configure a DataSource: 'spring.datasource.url' is not specified Failed to start mongod.service: Unit mongod.service not found db.collection is not a function when using MongoClient v3.0 MongoError: connect ECONNREFUSED 127.0.0.1:27017 MongoDB: How To Delete All Records Of A Collection in MongoDB Shell? How to resolve Nodejs: Error: ENOENT: no such file or directory How to create a DB for MongoDB container on start up?

Examples related to filter

Monitoring the Full Disclosure mailinglist Pyspark: Filter dataframe based on multiple conditions How Spring Security Filter Chain works Copy filtered data to another sheet using VBA Filter object properties by key in ES6 How do I filter date range in DataTables? How do I filter an array with TypeScript in Angular 2? Filtering array of objects with lodash based on property value How to filter an array from all elements of another array How to specify "does not contain" in dplyr filter

Examples related to mongodb-query

How to join multiple collections with $lookup in mongodb $lookup on ObjectId's in an array how to convert string to numerical values in mongodb How to convert a pymongo.cursor.Cursor into a dict? Mongodb find() query : return only unique values (no duplicates) How to list all databases in the mongo shell? Printing Mongo query output to a file while in the mongo shell MongoDB "root" user How to query nested objects? How to filter array in subdocument with MongoDB

Examples related to aggregation-framework

$lookup on ObjectId's in an array Find duplicate records in MongoDB MongoDB SELECT COUNT GROUP BY mongodb group values by multiple fields How to filter array in subdocument with MongoDB mongodb count num of distinct values per field/key Converting string to date in mongodb MongoDB: Combine data from multiple collections into one..how? Retrieve only the queried element in an object array in MongoDB collection Update MongoDB field using value of another field