Using MongoDB's positional operator $ in a deeply nested document query

Question

Is it possible to use positional operator '$' in combination with a query on a deeply-nested document array?

Consider the following nested document defining a 'user':

{
   username: 'test',
   kingdoms: [

      {
          buildings: [

              {
                  type: 'castle'

              },
              {
                  type: 'treasury'
              },

              ...

          ]

      },

      ...

   ] 
}

We'd like to return the 'castles' for a particular user e.g. in a form:

{
    kingdoms: [{

        buildings: [{

            type: 'castle'

        }]

    }]
}

Because you cannot use the $ operator twice (https://jira.mongodb.org/browse/server-831) I know that I can't also query for a particular kingdom, so I'm trying to write a find statement for the nth kingdom.

This seems to make sense when updating a deeply-nested sub-document (Mongodb update deeply nested subdocument) but I'm having less success with the find query.

I can return the first kingdom's buildings with the query:

db.users.findOne(
    { username: 'test' },
    { kingdoms: {$slice: [0, 1]}, 'kingdom.buildings': 1 }
);

But this returns all the buildings of that kingdom.

Following the single-level examples of position operator I'm trying a query like this:

db.users.findOne(
    { username: 'test', 'kingdoms.buildings.type': 'castle' },
    { kingdoms: {$slice: [n, 1]}, 'kingdom.buildings.$': 1 }
);

so as to be in the form:

db.collection.find( { <array.field>: <value> ...}, { "<array>.$": 1 } ) 

as described in the documentation http://docs.mongodb.org/manual/reference/operator/projection/positional/#proj.S

However this fails with the error:

Positional operator does not match the query specifier

Presumably because kingdoms.buildings isn't considered an array. I've also tried kingdoms.0.buildings

It is confusing because this appears to work for updates (according to Mongodb update deeply nested subdocument)

Have I just got the syntax wrong or is this not supported? If so is there a way to achieve something similar?

Answer 1

You get an error from

db.users.findOne(
    { username: 'test', 'kingdoms.buildings.type': 'castle' },
    { kingdoms: {$slice: [n, 1]}, 'kingdom.buildings.$': 1 }
);

because there is a spelling mistake ("kingdom.buildings.$" should be "kingdoms.buildings.$").
However, this way can not accomplish what you expect.
$ is always aimed at kingdoms in the path of kingdoms.buildings - the first array.

This is a way that should be able to solve the problem.
(V2.6+ required)

db.c.aggregate([ {
    $match : {
        username : 'test',
        'kingdoms.buildings.type' : 'castle'
    }
}, {
    $project : {
        _id : 0,
        kingdoms : 1
    }
}, {
    $redact : {
        $cond : {
            "if" : {
                $or : [ {
                    $gt : [ "$kingdoms", [] ]
                }, {
                    $gt : [ "$buildings", [] ]
                }, {
                    $eq : [ "$type", "castle" ]
                } ]
            },
            "then" : "$$DESCEND",
            "else" : "$$PRUNE"
        }
    }
} ]).pretty();

---

To only reserve the first element of kingdoms,

db.c.aggregate([ {
    $match : {
        username : 'test',
        'kingdoms.buildings.type' : 'castle'
    }
}, {
    $redact : {
        $cond : {
            "if" : {
                $or : [ {
                    $gt : [ "$kingdoms", [] ]
                }, {
                    $gt : [ "$buildings", [] ]
                }, {
                    $eq : [ "$type", "castle" ]
                } ]
            },
            "then" : "$$DESCEND",
            "else" : "$$PRUNE"
        }
    }
}, {
    $unwind : "$kingdoms"
}, {
    $group : {
        _id : "$_id",
        kingdom : {
            $first : "$kingdoms"
        }
    }
}, {
    $group : {
        _id : "$_id",
        kingdoms : {
            $push : "$kingdom"
        }
    }
}, {
    $project : {
        _id : 0,
        kingdoms : 1
    }
} ]).pretty();