MongoDB aggregation filter array of subdocuments by missing field

Question

Given the following collection:

db.test.insertOne(
    {
        test: [
            {a: "1", b: "2"},
            {a: "3"}
        ]
    }
)

How can you filter out the second document (with the non existing field b) of the test array inside an aggregation?

The aggregation

db.test.aggregate([
    {
        $project: {
            test: {
                $filter: {
                    input: "$test",
                    cond: {
                        $ne: ["$$this.b", null]
                    }
                }
            }
        }
    }
])

still returns the subdocument with the not existing field b:

{
    "_id": {"$oid": "xyz"},
    "test": [
      {
        "a": "1",
        "b": "2"
      },
      {
        "a": "3"
      }
    ]
  }

I'd expect the following result:

{
    "_id": {"$oid": "xyz"},
    "test": [
      {
        "a": "1",
        "b": "2"
      }
    ]
  }

Obviously my pipeline is more complex than that, but the problem boils down to this small example.

Joe · Accepted Answer · 2020-06-26 10:01:31Z

4

Null and missing are different types in BSON. To test for missing, compare with the $type:

                    cond: {
                        $ne: [{$type:"$$this.b"}, "missing"]
                    }

Playground

answered Jun 26, 2020 at 10:01

Joe

29k4 gold badges25 silver badges50 bronze badges

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

2 Comments

Stezy Over a year ago

Thanks for this, this solves the problem. But how is this working then: docs.mongodb.com/manual/tutorial/query-for-null-fields Shouldn't this logic apply everywhere?

Joe Over a year ago

A special case was added to the query language so that null would match nonexistent, but that appears to be the only place.

Collectives™ on Stack Overflow

MongoDB aggregation filter array of subdocuments by missing field

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related