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?

Wizard · Accepted Answer · 2014-11-02 10:35:22Z

3

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();

edited Nov 2, 2014 at 10:35

answered Nov 1, 2014 at 14:51

Wizard

4,4311 gold badge17 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Hugheth Over a year ago

Thanks Wizard - is it possible to specify these conditions at different levels of the hierarchy simply? Say for argument a kingdom had a key type: "castle" then it would $$DESCEND even though it isn't matching a building's type? (or a similar situation if that example isn't valid)

Wizard Over a year ago

@Hugheth, not very clear about your point. Can you post a document to describe it?

Hugheth Over a year ago

The main difference between this answer and my use case is that it doesn't filter to a single kingdom - instead it returns all the castles of the user

Wizard Over a year ago

@Hugheth, Do you mean you just want to return one element of kingdoms field? Then which one you want to return, the first one?

Hugheth Over a year ago

Yes, though it would be good to have a general answer for the nth kingdom ie. any particular kingdom

|

Collectives™ on Stack Overflow

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

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related