MongoDB - find entries where field with specific name is present, nested anywhere

Question

I have a mongoDB database, where some entries have a field of a name "rx" + value of another field in this entry (let's call it rxid), so the final name of this field could look like "rx1234" or "rx2836". Inside this field there is a list of dictionaries which MAY contain fields with names "A" and "B". My task is to find how many entries in this database have at least one non-empty field named A or B, no matter where it is nested. I tried to search for nested queries, however it always requires to specify the "parent" field, which in my case is a combination of rx and a value of other field, so it is not a constant name.

My database schema look something like:

{ 
    "_id" : 1, 
    "rxid" 1234, 
    "rx1234" : [
        {
            "A" : "somevalue", 
            "B" : "someothervalue",  
        }, 
        {
            "A" : "somevalue2",
        }
    ]
},
{ 
    "_id" : 2, 
    "rxid" 2345
}

In this case I expect to count how many objects are structured like _id = 1 and how many like _id = 2 (there may be other fields inside but they are irrelevant)

Answer 1

Refer this

It checks for rxCustomNumber field and then checks for A/B inside that field. You can simply add a count pipeline to get the counts.

db.collection.aggregate([
  {
    "$project": {
      "concatValue": {//Form the dynamic field dynamically
        "$concat": [
          "rx",
          {
            "$toString": "$rxid"
          }
        ]
      },
      "data": {
        "$objectToArray": "$$ROOT"
      }
    }
  },
  {//Restructure the field
    $unwind: "$data"
  },
  {
    $match: {
      $and: [//Check for the match
        {
          $expr: {
            "$eq": [
              "$concatValue",
              "$data.k"
            ]
          }
        }
      ]
    }
  },
  {//Print the existence
    "$project": {
      "data.v.A": 1,
      "data.v.B": 1
    }
  }
])