Get ElemMatch in Mongo Aggregation to find null or non existing array objects

Question

Lets say I have in Mongo 4.2 a data like below. Please notice that each document consists of a RESULT array which may contain one or more or null sub-objects.

{ "_id" : 1, "results" : [ { "product" : "a", "score" : "1" },
                           { "product" : "b", "score" : "5" } ] }

{ "_id" : 2, "results" : [ { "product" : "a", "score" : "8" },
                           { "product" : "b", "score" : "7" } ] }

{ "_id" : 3, "results" : [ { "product" : "a", "score" : "" },
                           { "product" : "b", "score" :  } ] }

{ "_id" : 4, "results" : [ { "product" : "b", "score" : "8" } ] }

{ "_id" : 5, "results" : [  ] }

I am right now trying to get an Aggregation Pipeline with an ElemMatch to find all Docs which have a Product "A" with a score bigger than "". That one works and I am using this right now:

{ "results": { $elemMatch: { "product": "a", "score": {  $gt : ''} } } }

But I can not manage to get it the other way around, like all Docs that have no score for product A. Which would mean I would expect from above example docs to get Doc #3 and #4 and #5.

I can get # 3 with this:

{ "results": { $elemMatch: { "product": "a", "score":  '' } } }

And #5 with this:

{  "results.score": null }

But I can not get #4. And of course it would be the best to get Doc #3 and #4 and #5 with just one query. So is there a way to do that?

Thanks for any help.

prasad_ · Accepted Answer · 2021-12-20 09:15:02Z

1

... it would be the best to get Doc #3 and #4 and #5 with just one query

You can use this query filter:

db.collection.aggregate([
  { 
    $match: {
      results: { 
        $not: { 
          $elemMatch: { product: "a", score: { $nin: [ null, "" ] } } 
        } 
      }
    }
  }
])

answered Dec 20, 2021 at 9:15

prasad_

14.4k2 gold badges27 silver badges44 bronze badges

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

Collectives™ on Stack Overflow

Get ElemMatch in Mongo Aggregation to find null or non existing array objects

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