Find sample documents in mongo where a nested field matches a value

Question

I have millions of records in collection and here are some sample documents

{
    _id: ObjectId("003fjasf0234j03j0349")
    FirstLayer: {
        SecondLayer: {
            Status: {
                9428f:{
                    active: "Active"
                }
            }
        }
    }
}

{
    _id: ObjectId("qg3o4034034nr34343")
    FirstLayer: {
        SecondLayer: {
            Status: {
                9428f:{
                    active: "Active"
                }
            }
        }
    }
}

{
    _id: ObjectId("293je2odjojwe0f23")
    FirstLayer: {
        SecondLayer: {
            Status: {
                00fekw:{
                    active: "Not Active"
                }
            }
        }
    }
}

I want to fetch a few sample documents from monogdb where the active: "Active". I came up with this:

db.Collection.aggregate([
  {$match:{"FirstLayer.SecondLayer.Status.9428f.active": "Active"}},
  {$sample:{size: 50}}
  ])

It's working but the issue is the sub document inside Stauts {} have different names for documents (ex: "9428f", "00fekw" in the given sample documents). So I can't hard code the path to "active" field.

Is there any other way to do it?

Answer 1

I don't think is there any straight way to handle this situation in MongoDB,

You can try,

Approach 1:

• $set to add a field Status to assign value of Status after converting it to array in key-value format using $objectToArray
• $match match Status condition
• $unset remove Status field because its not needed
• $sample your documents

db.collection.aggregate([
  { $set: { Status: { $objectToArray: "$FirstLayer.SecondLayer.Status" } } },
  { $match: { "Status.v.active": "Active" } },
  { $unset: "Status" },
  { $sample: { size: 50 } }
])

Playground

Approach 2:

Condition match with expression $expr, you can use aggregation operators in condition,

• $let to create a vars status to get status array from object and in filter status array and get active status,
• $size get size of above filter result
• expression will check if size is not zero then return result

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $let: {
          vars: { status: { $objectToArray: "$FirstLayer.SecondLayer.Status" } },
          in: {
            $size: {
              $filter: {
                input: "$$status",
                cond: { $eq: ["$$this.v.active", "Active"] }
              }
            }
          }
        }
      }
    }
  },
  { $sample: { size: 50 } }
])

Playground