CODEWITHSUNDEEP
mongodbaggregation-framework
Matt Lightbourn
Matt Lightbourn

Reputation: 597

How to combine two separate aggregations together in same result

I want to take two aggregations and combine them together. "Blakes Seven" kindly helped me with a couple of queries to retrieve first and second array element values but I need the two together.

First one:

db.getCollection('anothertest').aggregate([
{ "$unwind": "$a" },
{ "$group": {
    "_id": "$_id",
    "first": { "$first": "$a" }
}}
])

Give a result of:

{
"result" : [ 
    {
        "_id" : ObjectId("565cc5261506995581569439"),
        "first" : 4
    }
],
"ok" : 1.0000000000000000
}

Second one:

db.getCollection('anothertest').aggregate([
{ "$unwind": "$a" },
// Keeps the first element
{ "$group": {
    "_id": "$_id",
    "first": { "$first": "$a" },
    "a": { "$push": "$a" }
 }},
{ "$unwind": "$a" }
,
// Removes the first element
{ "$redact": {
"$cond":{
"if": { "$ne": [ "$first", "$a" ] },
"then": "$$KEEP",
  "else": "$$PRUNE"
}
}}

Gives the result:

{
"result" : [ 
    {
        "_id" : ObjectId("565cc5261506995581569439"),
        "second" : 2
    }
],
"ok" : 1.0000000000000000
}

What I need is the result of:

{
"result" : [ 
    {
        "_id" : ObjectId("565cc5261506995581569439"),
        "first" : 4
        "second" : 2
    }
],
"ok" : 1.0000000000000000
}

In addition to this, the document it is based on is just an example where the values sit as elements within an array. How would I use this on a nested array? If this is all to complicated for Aggregation, maybe there is an easier way to achieve this using Map Reduce? Thanks for any help provided.

The document for the above is below:

{
"_id" : ObjectId("565cc5261506995581569439"),
"a" : [ 
    4, 
    2, 
    8, 
    71, 
    21
]
}

UPDATE TO MY ORIGINAL POST I have now managed to take the awesome script and make it work for my document which has nested arrays - up to a point.

{ "$match": {objectOriginAPI : "Profit & Loss"}},
{ "$unwind": "$objectRawOriginData.Reports" },
        { "$unwind": "$objectRawOriginData.Reports.Rows" },
        { "$unwind": "$objectRawOriginData.Reports.Rows.Rows" },
        { "$unwind": "$objectRawOriginData.Reports.Rows.Rows.Cells" },
{ "$group": {
    "_id": "$_id",
    "first": { "$first": "$objectRawOriginData.Reports.Rows.Rows.Cells.Value" },
    "a": { "$push": "$objectRawOriginData.Reports.Rows.Rows.Cells.Value" }
}},
{ "$unwind": "$a" },
{"$skip":1},
{ "$group": {
    "_id": "$_id",
    "BalanceName": { "$first": "$first" },
    "BalanceValue": { "$first":      "$objectRawOriginData.Reports.Rows.Rows.Cells.Value" },
}}
])

Actually, accidentally I forgot to update the second $unwind so it remained as "$a". and what happened was I got the following result:

{
"result" : [ 
    {
        "_id" : ObjectId("564d12da1506995581569428"),
        "BalanceName" : "Sales",
        "BalanceValue" : null
    }
],
"ok" : 1.0000000000000000
}

But when I changed the $unwind to match the $unwind for the first value, I get no results at all. I cannot understand why, any ideas? Thanks, Matt

Upvotes: 0

Views: 1833

Answers (1)

joao
joao

Reputation: 4117

You can do that following the same logic as the first aggregation you posted. Note that I used $skip to skip the first array element and get the second. 

db.collection.aggregate([
    { "$match": {
        "_id": ObjectId("565cc5261506995581569439")
    }},
    { "$unwind": "$a" },
    { "$group": {
        "_id": "$_id",
        "first": { "$first": "$a" },
        "a": { "$push": "$a" }
    }},
    { "$unwind": "$a" },
    {"$skip":1},
    { "$group": {
        "_id": "$_id",
        "first": { "$first": "$first" },
        "second": { "$first": "$a" },
    }}
])

Result:

{ 
    "_id" : ObjectId("565cc5261506995581569439"),
    "first" : 4,
    "second" : 2 
}

Your second aggregation is also quite close to the desired output:

db.collection.aggregate([
    { "$match": {
        "_id": ObjectId("565cc5261506995581569439")
    }},
    { "$unwind": "$a" },
    { "$group": {
        "_id": "$_id",
        "first": { "$first": "$a" },
        "a": { "$push": "$a" }
    }},
    { "$unwind": "$a" },
    { "$redact": {
        "$cond":{
        "if": { "$ne": [ "$first", "$a" ] },
            "then": "$$KEEP",
            "else": "$$PRUNE"
    }}},
    { "$limit": 1},
    { "$project": {
        "_id":1,
        "first":1,
        "second":"$a"
    }}
])

EDIT: Also, as they are, both aggregations only work for a single document, thus the necessity to add the initial $match step.

Upvotes: 1

Related Questions