Pymongo: How to access the field value of an object in a nested array in a Projection?

Question

I'm trying to code a projection in pymongo (3.3.0, Python 3.6.0, MongoDB 3.2).

The original data is approximately of the form:

{
    "_id" : ObjectId("592fd5ac1aee05abb6104912"),
    "Results" : {
            "SomeData" : 1234,
            "ResultAllPoints" : {
                "MyArray" : [
                     {
                         "Value" : 0.5,
                         "Result" : 1,
                     },
                     {
                          "Value" : 1.5,
                          "Result" : 1,
                     }
                     {
                          "Value" : 1.7,
                          "Result" : 1,
                     }
                ]
            }
    }
}

I want to access the value stored in the field "Value" the second array entry of "MyArray" and use it as the value of a new field.

Using the MongoDB Shell, the command

db.my_collection.findOne().Results.ResultAllPoints.myArray[1].Value

gives me exactly the value I want to have in my resulting collection.

However, in my projection code, neither

{"newName" : "$Results.ResultAllPoints.myArray[1].Value"}

nor

{"newName" : "$Results.ResultAllPoints.myArray.1.Value"}

are working. In the first case, "newName" does not appear in the result at all the second case results in an empty array as the content of "newName".

I already know that I can use

{"$arrayElemAt": [ "$Results.ResultAllPoints.MyArray", 1]}

to access the Object containing the required information. But how can I access the content of "Value" afterwards?

Any help would be greatly appreciated! :)

EDIT: This is not a duplicate of Retrieve only the queried element in an object array in MongoDB collection, as I don't know the content of "Value" beforehand and therefore cannot use '$elemMatch'.

score 0 · Answer 1 · answered Jun 01 '17 at 13:38

0

According to above mentioned description please try executing following aggregate query in MongoDB shell

db.my_collection.aggregate(

    // Pipeline
    [
        // Stage 1
        {
            $match: { "_id" : ObjectId("592fd5ac1aee05abb6104912")}
        },

        // Stage 2
        {
            $unwind: "$Results.ResultAllPoints.MyArray"
        },

        // Stage 3
        {
            $skip: 1
        },

        // Stage 4
        {
            $limit: 1
        }

    ]
);

answered Jun 01 '17 at 13:38

Rubin Porwal

3,736
1
23
26

Dear Rubin Porwal, as I described above, I don't have any problems accessing the desired field in the Shell. I am searching for a pymongo expression which can be used in a projection and which does the same as `Results.ResultAllPoints.myArray[1‌].Value` in the Shell. – Mol1hua Jun 02 '17 at 08:01

score 0 · Accepted Answer · answered Jun 08 '17 at 06:50

While I wish there was a simpler solution, I found a workaround by adding a second projection in the pipeline. The first one contains all my other projection items with their new names and projects the containing array "myArray":

"newName_temp" : {"$arrayElemAt": [ "$Results.ResultAllPoints.MyArray", 1 ]}

The second projection copies all items in the first one to keep them and then accesses the "Value" content via

"newName" : "$newName_temp.Value"

This is quite lengthy, so I am always open to better solutions!

score 0 · Answer 3 · answered Jan 18 '18 at 20:38

Here is another solution. I'm new at MongoDB but this seems to work:

db.junk.aggregate(    
    [
        {
            $project: {
              newName: {
                $let: {
                  vars: {
                    x: {
                      $arrayElemAt: ["$Results.ResultAllPoints.MyArray", 1]
                    }
                  },
                  in: "$$x.Value"      
                }
              }
            }
        },
    ] 
);

This solution also seems to do away with a second projection. I'd also love to hear from folks skilled in MongoDB is there is something intrinsically wrong with the solution.

Pymongo: How to access the field value of an object in a nested array in a Projection?

3 Answers3