CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
nonagon
nonagon

Reputation: 3483

Mongo query for embedded document with key other than a certain value

I have a mongo (3.6) collection that looks like this (the ISODates are set to various times, the actual times aren't important here):

{_id: 0}
{_id: 1, a: null}
{_id: 2, a: {}}
{_id: 3, a: {"0": ISODate("...")}}
{_id: 4, a: {"1625": ISODate("...")}}
{_id: 5, a: {"0": ISODate("..."), "1625": ISODate("...")}}
{_id: 6, a: {"7": ISODate("..."), "900": ISODate("...")}}

I want to query for documents where the a field (always an embedded document if present) contains at least one key that has a value other than "0". So in my example it would return documents 4, 5 and 6.

I know I can do this with a $where clause, but is there a way to do it without using $where?

Upvotes: 1

Views: 344

Answers (1)

whoami - fakeFaceTrueSoul
whoami - fakeFaceTrueSoul

Reputation: 17915

You can try below aggregation query :

db.collection.find({
    $and: [
      { a: { $type: "object" } },
      { a: { $ne: {} } }, // This is optional but might improve performance by remove empty objects `{}` to next step.
      { $expr: { $gt: [ { $size: { $filter: { input: { $objectToArray: "$a" }, cond: { $ne: [ "$$this.k", "0" ] } } } }, 0 ] } }
    ]
  })

Test : mongoplayground

Note : You can try this query if you don't want to execute a function in $where, this query use $expr to execute aggregation expression with in query language.

Ref : aggregation-pipeline

Upvotes: 1

Related Questions