MongoDB multidimensional array projection

Question

I just started learning MongoDB and can't find a solution for my problem.

Got that document:

> db.test.insert({"name" : "Anika", "arr" : [ [11, 22],[33,44] ] })

Please note the "arr" field which is a multidimensional array.

Now I'm looking for a query that returns only the value of arr[0][1] which is 22. I tried to achieve that by using $slice, however I don't know how to address the second dimension with that.

> db.test.find({},{_id:0,"arr":{$slice: [0,1]}})
{ "name" : "ha", "arr" : [ [ 11, 22 ] ] }

I also tried

> db.test.find({},{_id:0,"arr":{$slice: [0,1][1,1]}})
{ "name" : "ha", "arr" : [ [ 11, 22 ] ] }

The desired output would be either

22

or

{"arr":[[22]]}

Thank you

---

EDIT:

After reading the comments I think that I've simplified the example data too much and I have to provide more information:

1. There are many more documents in the collection like that one that I've provided. But they all have the same structure.
2. There are more array elements than just two
3. In the real world the array contains really long texts (500kb-1mb), so it is very expansive to transmit the whole data to the client.
4. Before the aggregation I will do a query by the 'name' field. Just skipped that in the example for the sake of simplicity.
5. The target indexes are variable, so sometimes I need to know the value of arr[0][1], the next time it is arr[1][4]

---

example data:

> db.test.insert({"name" : "Olivia", "arr" : [ [11, 22, 33, 44],[55,66,77,88],[99] ] })
> db.test.insert({"name" : "Walter", "arr" : [ [11], [22, 33, 44],[55,66,77,88],[99] ] })
> db.test.insert({"name" : "Astrid", "arr" : [ [11, 22, 33, 44],[55,66],[77,88],[99] ] })
> db.test.insert({"name" : "Peter",  "arr" : [ [11, 22, 33, 44],[55,66,77,88],[99] ] })

example query:

> db.test.find({name:"Olivia"},{"arr:"...})

Answer 1

You can use the aggregation framework:

db.test.aggregate([
    { $unwind: '$arr' },
    { $limit: 1 },
    { $project: { _id: 0, arr: 1 } },
    { $unwind: '$arr' },
    { $skip: 1 },
    { $limit: 1 }
])

Returns:

{ "arr": 22 }

Edit: The original poster has modified my solution to suit his needs and came up with the following:

db.test.aggregate([
    { $match: { name:"Olivia" } },
    { $project: { _id: 0,arr: 1 } },
    { $unwind: '$arr' },
    { $skip: 1 },
    { $limit:1 },
    { $unwind: "$arr" },
    { $skip: 2 },
    { $limit: 1 }
])

This query will result in { arr: 77 } given the extended data provided by the OP. Note that $skip and $limit are needed to select the right elements in the array hierarchy.

Answer 2

The $slice form you ask for does not do multi-dimentional arrays. Each array is considered individually, and is therefore not supported that way by the current $slice.

As such it is actually done a lot shorter on indexed "first" and "last" values than has been suggested using .aggregate(), and presently:

db.test.aggregate([
    { "$unwind": "$arr" },
    { "$group": {
        "_id": "$_id",
        "arr": { "$first": "$arr" }
    }},
    { "$unwind": "$arr" },
    { "$group": {
        "_id": "$_id",
        "arr": { "$last": "$arr" }
    }}
])

But in future releases of MongoDB ( currently works in development branch 3.18 as of writing ) you have $arrayElemAt as an operator for the aggregation framework which works like this:

db.test.aggregate([
    { "$project": {
        "arr": {
            "$arrayElemAt": [
                { "$arrayElemAt": [ "$arr", 0 ] },
                1
            ]
        }
    }}
])

Both basically come to the same { "arr": 22 } result, though the future available form works quite flexibly on array index values, rather than first and last.