Filter array element which does not have a specific field in MondoDB aggregation

Question

I have a people collection in mongodb like below.

MongoDB version:4.0.13

{
    "_id" : ObjectId("5dcbeb97e184e0f527c6a3e7"),
    "name" : "nithin",
    "age" : 35.0,
    "addresses" : [ 
        {
            "street" : "xyz",
            "city" : "Tvm"
        }, 
        {
            "street" : "pqr",
            "city" : "KLM"
        }, 
        {
            "street" : "abc"
        }
    ]
}


{
    "_id" : ObjectId("5dcbebe4e184e0f527c6a3e8"),
    "name" : "akash",
    "age" : 21.0,
    "addresses" : [ 
        {
            "street" : "lon",
            "city" : "London"
        }, 
        {
            "street" : "par",
            "city" : "paris"
        }, 
        {
            "street" : "abc"
        }
    ]
}

I need to filter out all the addresses which doesn't have city property. What i achieved so far is this

db.getCollection('People').aggregate([

    {
        $project:
        {
            "name":1,
            "age":1,
            "addresses":
            {
                $filter:
                {
                    input:"$addresses",
                    as:"address",
                    cond:{$not:["$$address.city"]}
                }
            }

        }   


    }

])

Result: I am getting all addresses without city property(this is exactly the opposite of what i need). I couldn't find any help from SO. how to negate a not result

I also tried this

        $filter:
        {
            input:"$addresses",
            as:"address",
            cond:{"$exists":["$$address.city":true]}
        }

Result:

Error: Line 14: Unexpected token :

See ths post on MongoDB aggregate by field exists. The answer by @Delcon shows two options; the second one will work as a $filter's condition. — prasad_
– prasad_, Commented Nov 13, 2019 at 15:12

thev0id · Accepted Answer · 2019-11-13 15:09:54Z

1

It is not necessary to have aggregation in this case. You can just use find with $elemMatch operator:

db.people.find({
    addresses: {$elemMatch : {city: {$exists: false}}}
})

But if you want to use aggregation then you can do match on the same query:

db.people.aggregate([
    {$match: {addresses: {$elemMatch : {city: {$exists: false}}}}}
])

Alternatively you can do it like shown below, but I assume elemMatch will work faster:

db.people.aggregate([
{$unwind: '$addresses'},
{$match: {"addresses.city" : {$exists: false}}}
{$group: { _id : '$_id', addresses: {$addToSet: "$addresses"}}}
])

edited Nov 13, 2019 at 15:09

answered Nov 13, 2019 at 15:00

thev0id

1951 silver badge6 bronze badges

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

Collectives™ on Stack Overflow

Filter array element which does not have a specific field in MondoDB aggregation

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related