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?

Not with the RESTHeart API it isn't. The problem is that there are "named keys" under each "message" key. MongoDB 3.4 has features that would make this possible however. For my money though, I would not worry about it and neither should you. Such a trivial change represents a small amount of bytes saving in each transfer, and the end result structure is easy to manipulate in client code to the form you want. A lot easier in fact than the aggregation operations to do so. — Neil Lunn, Jun 13 '17 at 12:11
So the aggregation that I have now is enough to make the request in the client? And than its possible to get what I want from it? @NeilLunn — R6ch1RD, Jun 13 '17 at 13:38

score 1 · Accepted Answer · answered Jun 13 '17 at 21:11

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.

Yess, this is what I was searching for. Thanks! – R6ch1RD Jun 14 '17 at 12:17 — R6ch1RD, Jun 14 '17 at 12:17

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

1 Answers1