query for number of array elements containing some field in mongodb

Question

I have a collection of documents that look like this:

{
   my_array : [
      { 
         name : "...", 
         flag : 1 // optional
      },
      ...
   ]
}

How can I query for documents that exactly one of their elements contain the flag field?

This will return documents where my_array length is exactly 1:

db.col.find({ "my_array" : { $size : 1 } })

This will return documents where at least one of my_array's object contain the flag field:

db.col.find({ "my_array.flag" : { $exists : true } })

And this will look for documents where flag is an array of size one:

db.col.find({ "my_array.flag" : { $size : 1 } })

I want to somehow combine the first two queries, checking for some inner field existence, and then querying for the size of the filtered array

Answer 1

You can try $redact with $filter for your query.

$filter with $ifNull to keep the matching element followed by $size and $redact and compare result with 1 to keep and else remove the document.

db.col.aggregate(
    [{
        $match: {
            "my_array.flag": {
                $exists: true
            }
        }
    }, {
        $redact: {
            $cond: {
                if: {
                    $eq: [{
                        $size: {
                            $filter: {
                                input: "$my_array",
                                as: "array",
                                cond: {
                                    $eq: [{
                                        $ifNull: ["$$array.flag", null]
                                    }, "$$array.flag"]
                                }
                            }
                        }
                    }, 1]
                },
                then: "$$KEEP",
                else: "$$PRUNE"
            }
        }
    }]
)

Answer 2

Use Aggregation framework in mongodb,

pipeline stage 1: Match ,

          checking for some inner field existence,

         { "my_array.flag" : { $exists : true } }

pipleline stage 2: Project,

         querying for the size of the filtered array,

         {$project:{ "my_array":{$size:"$my_array"}}}

mongoshell Command ,

    db.col.aggregate([{
        $match: {
            "my_array.flag": {
                $exists: true
            }
        }
    }, {
        $project: {
            "my_array": {
                $size: "$my_array"
            }
        }
    }])

Answer 3

Following the current answers about using aggregate, here's a working solution using aggregation:

db.col.aggregate([
{ $match : { "my_array.flag" : { $exists : true } } },
{ $unwind : "$my_array" },
{ $match : { "my_array.flag" : { $exists : true } } },
{ $group : { _id : "$_id", count_flags : { $sum : 1 } } },
{ $match : { "count_flags" : 1 } }
])

Here are the steps explained:

1. Filter to only documents that contain at least one flag field (this will use my index to dramatically reduce the number of documents I need to process)
2. Unwind the my_array array, to be able to process each element as a separate doc
3. Filter the separated docs from elements not containing flag
4. Group again by _id, and count the elements (that contain the flag field)
5. Filter by the new count_flags field

I don't really like this solution, it seems like a lot of steps to get something that should have basic support in a standard mongo find query.