CODEWITHSUNDEEP
mongodbaggregation-framework
gb5256
gb5256

Reputation: 191

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

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.

Upvotes: 0

Views: 657

Answers (1)

prasad_
prasad_

Reputation: 14287

... 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, "" ] } } 
        } 
      }
    }
  }
])

Upvotes: 1

Related Questions