MongoDB: How to group $project fields?

Question

Schema

A single document looks like this:

{
  row: [
    { identifier: 'a', value: '2000-01-01' },
    { identifier: 'b', value: 5 },
    { identifier: 'c', value: 99 },
  ]
}

Wanted result

I'd like to group it by year. More precisely: The value at identifier a should be the year and the value at identifier c should be summed. So, the result should be like:

[
  { x: '2000', y: 105 },
  { x: '2001', y: 67 },
  { x: '2002', y: 99 }
]

Approach

{
  $unwind: '$row'
}, {
  $match: {
    $or: [
      {'row.identifier': 'a'},
      {'row.identifier': 'c'}
    ]
  }
}, {
  $project: {
    x: {
      $cond: {
        if: {$eq: ['$row.identifier', 'a']},
        then: '$row.value',
        else: '$$REMOVE'
      }
    },

    y: {
      $cond: {
        if: {
          $eq: ['$row.identifier', 'c']
        },
        then: '$row.value',
        else: '$$REMOVE'
      }
    }
  }
}, {
  $group: {
    _id: {
      x: { $substr : ['$x', 0, 4 ] }
    },
    y: {
      $sum: '$y'
    }
  }
}

Problem

It doesn't return the wanted result. I don't know how to merge x and y after $project again. Or maybe the whole approach isn't productive.

Answer 1

Use below aggregation query.

[
  {"$unwind":"$row"},
  {"$match":{
    "$or":[
      {"row.identifier":"a"},
      {"row.identifier":"c"}
    ]
  }},
  {"$group":{
    "_id":{
      "x":{
        "$cond":[
          {"$eq":["$row.identifier","a"]},
          {"$substr":["$row.value",0,4]},
          null
        ]
      }
     },
     "y":{
        "$sum":{
          "$cond":[
            {"$eq":["$row.identifier","c"]},
            "$row.value",
            0
          ]
        }
     }
  }}
]

Answer 2

I have tried using $arrayElemAt and group by after unwind and filtering the row with a and c as you have ready done, its getting the desire result.

Step 1: inserted some documents as you have provided:

db.stackoverflow.insert({   row: [     { identifier: 'a', value: '2001-01-01' },     { identifier: 'b', value: 5 },     { identifier: 'c', value: 100 }   ] })

Step 2: See the all elements inserted

    db.stackoverflow.find()
    { "_id" : ObjectId("5aa5440e3e2cfe6e23b2de47"), "row" : [ { "identifier" : "a", "value" : "2000-01-01" }, { "identifier" : "b", "value" : 5 }, { "identifier" : "c", "value" : 99 } ] }
    { "_id" : ObjectId("5aa544143e2cfe6e23b2de48"), "row" : [ { "identifier" : "a", "value" : "2000-01-01" }, { "identifier" : "b", "value" : 5 }, { "identifier" : "c", "value" : 1 } ] }
    { "_id" : ObjectId("5aa544223e2cfe6e23b2de49"), "row" : [ { "identifier" : "a", "value" : "2001-01-01" }, { "identifier" : "b", "value" : 5 }, { "identifier" : "c", "value" : 1 } ] }
    { "_id" : ObjectId("5aa544263e2cfe6e23b2de4a"), "row" : [ { "identifier" : "a", "value" : "2001-01-01" }, { "identifier" : "b", "value" : 5 }, { "identifier" : "c", "value" : 100 } ] }
    { "_id" : ObjectId("5aa545853e2cfe6e23b2de4b"), "row" : [ { "identifier" : "a", "value" : "2001-01-01" }, { "identifier" : "b", "value" : 5 }, { "identifier" : "c", "value" : 1 } ] }

Step 3: Aggregation logic, filtering condition copied from your provided code:

    db.stackoverflow.aggregate([
        {$unwind: "$row"}, 
        {$match: 
            {$or: [ {"row.identifier": "a" }, {"row.identifier": "c"}]}
        },
        {
            $project: {
                yearstring: {
                    $cond: {
                        if: {$eq: ['$row.identifier', 'a']},
                        then: '$row.value',
                        else: '$$REMOVE'
                    }
                },
                valueString: {
                    $cond: {
                        if: { $eq: ['$row.identifier', 'c']},
                        then: '$row.value',
                        else: '$$REMOVE'
                    }
                }
            }
        },
        {
            $group: {
                _id: "$_id",
                "row": {
                    "$push": {
                        "year": "$yearstring",
                        "value": "$valueString"
                    }
                }      
            }
        },
        {
            $project: {
                _id: "$_id",
                year: { $arrayElemAt: [ "$row.year", 0 ] },
                value: {$arrayElemAt: ["$row.value", 0] }          
            }
        },
        {
            $project: {
                _id: 1,
                year: { $substr: [ "$year", 0, 4 ] },
                value: 1         
            }
        },
        {
            $group: {
                _id: "$year",
                y: {$sum: "$value"}
            }
        }
    ])

Step 4: output of the above aggregation:

    { "_id" : "2000", "y" : 100 }
    { "_id" : "2001", "y" : 102 }

You can reduce some stages. Hope you will get ideas how to get your result.

You can get all code on my github repo