CODEWITHSUNDEEP
mongodbmongodb-query
Digvijay Rathore
Digvijay Rathore

Reputation: 715

How to find specific array elements in mongoDb document wih query and filter with range?

I have a collection which stores document in below format: 

/* 1 */
{
    "_id" : ObjectId("5cc1a108c5475b9e91bb7830"),
    "item" : "journal",
    "instock" : [ 
        {
            "warehouse" : "A",
            "qty" : 5.0
        }, 
        {
            "warehouse" : "C",
            "qty" : 15.0
        }
    ]
}

/* 2 */
{
    "_id" : ObjectId("5cc1a108c5475b9e91bb7831"),
    "item" : "notebook",
    "instock" : [ 
        {
            "warehouse" : "C",
            "qty" : 5.0
        }
    ]
}

/* 3 */
{
    "_id" : ObjectId("5cc1a108c5475b9e91bb7832"),
    "item" : "paper",
    "instock" : [ 
        {
            "warehouse" : "A",
            "qty" : 60.0
        }, 
        {
            "warehouse" : "B",
            "qty" : 15.0
        }
    ]
}

/* 4 */
{
    "_id" : ObjectId("5cc1a108c5475b9e91bb7833"),
    "item" : "planner",
    "instock" : [ 
        {
            "warehouse" : "A",
            "qty" : 40.0
        }, 
        {
            "warehouse" : "B",
            "qty" : 5.0
        }
    ]
}

/* 5 */
{
    "_id" : ObjectId("5cc1a108c5475b9e91bb7834"),
    "item" : "postcard",
    "instock" : [ 
        {
            "warehouse" : "B",
            "qty" : 15.0
        }, 
        {
            "warehouse" : "C",
            "qty" : 35.0
        },
        {
            "warehouse" : "M",
            "qty" : 50.0
        }
    ]
}

/* 6 */
{
    "_id" : ObjectId("5cc1a16ea794ebd5fc278172"),
    "item" : "dig",
    "instock" : [ 
        {
            "warehouse" : "A",
            "qty" : 5.0
        }
    ]
}

I want to find specific doc's array which has qty greater than specified value and less than the other specified value.

For e.g. I want item "postcard" and instock value greater than 10 and less than 40, so it return me the doc of postcard with instock array only containing one element which is matched like the output will be:

/* 1 */
    {
        "_id" : ObjectId("5cc1a108c5475b9e91bb7834"),
        "item" : "postcard",
        "instock" : [ 
            {
                "warehouse" : "B",
                "qty" : 15.0
            }, 
            {
                "warehouse" : "C",
                "qty" : 35.0
            }
        ]
    }

Upvotes: 2

Views: 43

Answers (1)

mickl
mickl

Reputation: 49945

You can use $match for document level filtering and $filter to apply your condition on an array:

db.collection.aggregate([
    {
        $match: { item: "postcard" }
    },
    {
        $addFields: {
            instock: {
                $filter: {
                    input: "$instock",
                    cond: {
                        $and: [
                            { $gt: [ "$$this.qty", 10 ] },
                            { $lt: [ "$$this.qty", 30 ] }
                        ]
                    }
                }
            }
        }
    }
])

Mongo Playground

Upvotes: 1

Related Questions