Aggregation mongodb - Search field and value from sub-object in object

Question

I want to filter out a field with the values. I have made an aggregation with the RESTHeart API. I want to know if the status is accepted or rejected.

The data:

{
    "body": {
        "VERIFIED_MESSAGE": {
            "072ade7d42d871d4fe": {
                "messageId": "bcd1d991-aa63",
                "create": 1486546629585,
                "status": "accept",     //this is what I want to know
                "comment": null
            }
        }
    }
},
{
    "body": {
        "VERIFIED_MESSAGE": {
            "595d0a56cff27": {
                "messageId": "595d0a56c",
                "create": 1486566646197,
                "status": "reject.all",    
                "comment": null
            }
        }
    }
},
{
    "body": {
        "VERIFIED_MESSAGE": {
            "52ffd09bf5bd541602a": {
                "messageId": "1dadce1d",
                "create": 1486568943752,
                "status": "accept",
                "comment": null
            }
        }
    }
}

The aggregation:

{   
    "stages": [
        {"$match": {
            "body.VERIFIED_MESSAGE": {
                "$exists": true, 
                "$ne": null 
            },
        }},
        {"$project": {
            "_id": 0,
            "body.VERIFIED_MESSAGE": 1,
        }},
        {"$group": {
            "_id": null,
            "verified": {
                "$push": {
                    "message": "$body.VERIFIED_MESSAGE"
                }
            }
        }},
        {"$project": {
            "_id": 0,
            "verified": 1,
        }},
    ],
    "type": "pipeline"
}

Which results in this:

{
    "verified": [{
        "message": {
            "072ade7d42d871d4fe": {
                "messageId": "bcd1d991-aa63",
                "create": 1486546629585,
                "status": "accept",
                "comment": null
            }
        }
    },
    {
        "message": {
            "595d0a56c": {
                "messageId": "595d0a56c",
                "create": 1486566646197,
                "status": "reject.all",
                "comment": null
            }
        }
    },
    {
        "message": {
            "52ffd09bf5bd541602a": {
                "messageId": "1dadce1d",
                "create": 1486568943752,
                "status": "accept",
                "comment": null
            }
        }
    },
]

I have put it in an array but at this point I don't what to do next because the object value changes with each new entry. Is there a way to skip this?

What I want:

{
    "verified": [{
        "message": {
            "messageId": "bcd1d991-aa63",
            "create": 1486546629585,
            "status": "accept",
         }
    },
    {
        "message": {
            "messageId": "595d0a56cff27c1a2fbf3d29e6986688c49d511d",
            "create": 1486566646197,
            "status": "reject.all",
         }
    },
    {
         "message": {
             "messageId": "1dadce1d-a5a6-4d01-b0cf-f34c5e6219eb",
             "create": 1486568943752,
             "status": "accept",
         }
    }]
}

So is this possible?

Answer 1

Try this stage in aggregation to convert the format you have to the one you want:

{$project:{
    message:{$arrayElemAt:[
      {$map:{
          input:{$objectToArray:"$body.VERIFIED_MESSAGE"}, 
          in:{
              messageId:"$$this.k", 
              create:"$$this.v.create", 
              status:"$$this.v.status"}}},
      0
    ]}
}}

The $objectToArray expression requires you are running against MongoDB server version 3.4.4 or later.