mongo db : get sub array from an array of objects with values within a range in a single document

Question

What I have:

• A mongodb collection, say collection1
• collection1 contains two columns viz. id and data
• id is int but the twist is data is an array of objects and its keys are string but numeric
• collection1 contains only one document (for the sake of this question)

collection1

{
    "_id" : 1,
    "data" : [ 
        {
            "k1" : "001",
            "k2" : "v1"
        }, 
        {
            "k1" : "002",
            "k2" : "v2"
        }, 
        {
            "k1" : "004",
            "k2" : "v3"
        }, 
        {
            "k1" : "010",
            "k2" : "v4"
        }, 
        {
            "k1" : "015",
            "k2" : "v5"
        }
    ]
}

What I want to have:

I want to fetch all objects from the data array which have k1 from 003 to 012 both included, from the document with _id = 1

What I'm getting on searching on internet:

I got to know about aggregate function in mongo and $match, $project, $filter, etc. I'm new to mongo and unable to get the intuition behind aggregation.

I know I can achieve this through custom functions, but it would contain a for loop which will impact the performance. There must a better way to achieve it but I'm not able to figure it out.

Note: I also want to achieve the same with pymongo

Answer 1

Sorry I am not aware of pymongo but below query is the basic idea (as long as you put all MongoDB operators inside double quotes "" directly using this query must work):

Try this:

db.collection1.aggregate([
    {
        "$match": { "_id": 1 }
    },
    {
        "$addFields": {
            "data": {
                "$filter": {
                    "input": "$data",
                    "as": "item",
                    "cond": {
                        "$and": [
                            { "$gte": [{ "$toInt": "$$item.k1" }, 3] },
                            { "$lte": [{ "$toInt": "$$item.k1" }, 12] }
                        ]
                    }
                }
            }
        }
    }
]);

Output:

{
    "_id" : 1,
    "data" : [
        {
            "k1" : "004",
            "k2" : "v3"
        },
        {
            "k1" : "010",
            "k2" : "v4"
        }
    ]
}