10

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?

Community
  • 1
  • 1
Hugheth
  • 1,159
  • 10
  • 14

1 Answers1

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();
Wizard
  • 4,161
  • 1
  • 12
  • 12
  • 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) – Hugheth Nov 02 '14 at 10:03
  • @Hugheth, not very clear about your point. Can you post a document to describe it? – Wizard Nov 02 '14 at 10:07
  • 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 – Hugheth Nov 02 '14 at 10:13
  • @Hugheth, Do you mean you just want to return one element of **kingdoms** field? Then which one you want to return, the first one? – Wizard Nov 02 '14 at 10:19
  • Yes, though it would be good to have a general answer for the nth kingdom ie. any particular kingdom – Hugheth Nov 02 '14 at 10:21
  • @Hugheth, Just updated it. To return the n-th kingdom, it should be something different between kingdoms using to select. If they are the structures as the question, returning anyone is equivalent. – Wizard Nov 02 '14 at 10:38
  • @Hugheth, **$redact** walks all hierarchies sharing the same condition, that is why I use an **$or** clause to make sure it can walk to the bottom - {type:"castle"}. If your document structure is more complicated with more conditions, the **$or** may be more complicated. – Wizard Nov 02 '14 at 10:46
  • thanks for the help. I guess an explicit **'position'** field in the kingdom documents would solve the position problem - this also has the added benefit that I can update the returned castles. All our sub-documents also have **unique primary key field names** e.g. kingdoms have kingdom_id and buildings have building_id so I hopefully I can use $exists inside the $redact conditions to ensure they only apply at a certain level of the hierarchy. – Hugheth Nov 02 '14 at 10:59