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

Question

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
            }
        ]
    }

I think the output you have mentioned is not correct, as per your question it should return only one element of "warehouse":"B" — Sohan
– Sohan, Commented Apr 26, 2019 at 6:25
yes previously the output was not correct but i have edited my question now. — Digvijay Rathore
– Digvijay Rathore, Commented Apr 26, 2019 at 11:33

mickl · Accepted Answer · 2019-04-26 06:14:04Z

1

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

answered Apr 26, 2019 at 6:14

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related