mongodb Querying wildcarded nested objects

Question

I have a collection called Event, I have created a Mongo Playground with some sample data here: https://mongoplayground.net/p/DNfmIMLIs2N

I am trying to query a collection where the value of an unknown key matches my query value. I have 2 practical examples.

Example 1: Each event has a "pricingDict" which is a keyed object, the key is the currency and the value is the amount, eg. {usd : 40.50, aud: 60.25}. I want to be able to find all events where they have a pricingDict with a value greater than 0, regardless of the currency.

What I was hoping to do was something like this: db.collection("Event").find({"pricingDict.*": { $gt: 0 }}) Based on the sample data above I would expect this to return event2 and event3

Example 2: That same event class will have a keyed object called "eventManagers", the key will be the userId, and the value will be an object, which in turn will have a key called role. I want to be able to find all events where they have a user with the role of "Admin"

What I was hoping to do was something like this: db.collection("Event").find({"eventManagers.*.role": "Admin"}) Based on the sample data above I would expect this to return event1 and event3

It would be better if you can provide testing data and expected result in a valid JSON by editing your own question. Ask a question like [this](https://stackoverflow.com/questions/71835902/excract-only-the-lookups-return-mongoose) would be more clarify for us to solve your problem. Make sure that your testing data can come up with your expected result in certain logic. — YuTing, Apr 20 '22 at 00:53
I have updated to add a https://mongoplayground.net/p/DNfmIMLIs2N which shows some sample data. — Josh, Apr 20 '22 at 01:05

score 0 · Accepted Answer · answered Apr 20 '22 at 01:16

Use $objectToArray to convert your object to array of k-v tuples. Then use $filter to perform the query on the converted array.

Query 1

db.collection.aggregate([
  {
    "$match": {
      $expr: {
        $ne: [
          {
            "$filter": {
              "input": {
                "$objectToArray": "$pricingDict"
              },
              "as": "pd",
              "cond": {
                $gt: [
                  "$$pd.v",
                  0
                ]
              }
            }
          },
          []
        ]
      }
    }
  }
])

Here is the Mongo playground for your reference.

db.collection.aggregate([
  {
    "$match": {
      $expr: {
        $ne: [
          {
            "$filter": {
              "input": {
                "$objectToArray": "$eventManagers"
              },
              "as": "em",
              "cond": {
                $eq: [
                  "$$em.v.role",
                  "Admin"
                ]
              }
            }
          },
          []
        ]
      }
    }
  }
])

Here is the Mongo playground for your reference.

mongodb Querying wildcarded nested objects

1 Answers1