MongoDB project into an array

Question

I have array of objects (nested), and I want to pull a couple of the elements from those nested objects, and return these fields in an array. Below is my document structure (mocked it from original data)

"detailLine": [
    {
        "name": "first",
        "value": {
            "lineNumber": 1,
            "subLineCode": " ",
            "detailLineCharges": {
                "allowedAmount": {
                    "amount": "11111",
                    "reasonCode": “aaaah”
                }
            }
        }
    },
    {
        "name": "first",
        "value": {
            "detailLineCharges": {
                "allowedAmount": {
                    "amount": "22222",
                    "reasonCode": “BBBB”
                }
            }
        }
    }
]

I would like to see my results something like this

details: [
  {
    amount:”11111”,
    reasonCode : “aaaah”
  },
  {
    amount : “22222”,
    reasonCode : “BBBB”
  }
]

I tried

 db.collection.aggregate([
   {
     $match: {
       _id: "123456"
     }
   }, 
   {
     $project: {
       details: {
         $push {
           amount: "$detailLine.value.detailLineCharges.allowedAmount.amount",
           reasoncode: "$detailLine.value.detailLineCharges.allowedAmount.reasonCode"
         }
       }
     }
   }
])

but I am getting this error

Error: command failed: { "ok" : 0, "errmsg" : "invalid operator '$push'", "code" : 15999 } : aggregate failed :

I want to use $project because I am using it to pull too many other fields so that is why I did not try $group for this one particular scenario.

Can anyone help how to get those two fields in an Array?

Sede · Accepted Answer · 2016-06-30 19:28:56Z

Well, all you need is $project your documents and "reshape" them using the $map operator.

db.coll.aggregate( 
    [ 
        { "$match": { "_id": "123456" } },
        { "$project": { 
            "detailLine": { 
                "$map": { 
                    "input": "$detailLine", 
                    "as": "dline", 
                    "in": { 
                        "amount": "$$dline.value.detailLineCharges.allowedAmount.amount", 
                        "reasonCode": "$$dline.value.detailLineCharges.allowedAmount.reasonCode" 
                    } 
                } 
           } 
        }} 
    ]
)

DAXaholic · Accepted Answer · 2016-06-30 18:33:00Z

With the following pipeline it gives you the desired result, although I use $group. Of course you may need to adjust the group identifier.

db.getCollection('yourCollection').aggregate([
  {     
      $unwind: {
          path: "$detailLine"
      }
  },
  {
      $project: {
          allowedAmount: "$detailLine.value.detailLineCharges.allowedAmount"
      }
  },
  {
      $group: {
          _id: "x",
          details: {
              $push: "$allowedAmount"
          }
      }
  }
])

Result

{
    "_id" : "x",
    "details" : [ 
        {
            "amount" : "11111",
            "reasonCode" : "aaaah"
        }, 
        {
            "amount" : "22222",
            "reasonCode" : "BBBB"
        }
    ]
}

No he said he wanted to use $project bc of the number of fields and therefore didn't try $group. I interpret it as his clear preference is using $project but not as $group must not be used. Therefore I wrote 'although I use $group'; it was best effort bc it still may help. As there is now a an answer without $group I do not see any problem as this one was rightly accepted.

Gowtham Sooryaraj · Accepted Answer · 2018-03-08 16:35:39Z

You forgot to add a colon in $push

$project: {
   details: {
     $push {
       amount: "$detailLine.value.detailLineCharges.allowedAmount.amount",
       reasoncode: "$detailLine.value.detailLineCharges.allowedAmount.reasonCode"
     }
   }
 }

changed to

$project: {
   details: {
     $push: {
       amount: "$detailLine.value.detailLineCharges.allowedAmount.amount",
       reasoncode: "$detailLine.value.detailLineCharges.allowedAmount.reasonCode"
     }
   }
 }

Collectives™ on Stack Overflow

MongoDB project into an array

3 Answers 3

Comments

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Linked

Related