Aggregate count matched conditional elements of nested array

Question

I am new to MongoDB aggregation.

My DB has the following structure:

{
  "_id": "AAABBBCCCDDDD",
  "report":{
    "google": [{
        "report_id": "XXX",
        "detail": [{
            "available": true,
            "status_code": 200,
            "return_code": "ok",
        }, {
            "available": true,
            "status_code": 300,
            "return_code": "ok",
        }, {
            "available": true,
            "status_code": 400,
            "return_code": "ok",
        }]
    }, {
        "report_id": "YYY",
        "detail": [{
            "available": false,
            "status_code": 200,
            "return_code": "ok",
        }, {
            "available": true,
            "status_code": 200,
            "return_code": "ng",
        }, {
            "available": true,
            "status_code": 200,
            "return_code": "ok",
        }]
    }]
  }
}

I want to flatten the documents like this:

{
  "AAABBBCCCDDDD": [{
    "report_id": "XXX",
    "detail": [{
      "available": true,
      "status_code": 200,
      "return_code": "ok",
    }, {
      "available": true,
      "status_code": 300,
      "return_code": "ok",
    }, {
      "available": true,
      "status_code": 400,
      "return_code": "ok",
    }]
  }, {
    "report_id": "YYY",
    "detail": [{
      "available": false,
      "status_code": 200,
      "return_code": "ok",
    }, {
      "available": true,
      "status_code": 200,
      "return_code": "ng",
    }, {
      "available": true,
      "status_code": 200,
      "return_code": "ok",
    }]
  }]
}

then count how many matched available is true and return_code is "ok", returning a structure like this:

{
  "AAABBBCCCDDDD": [{
    "report_id": "XXX",
    "available_count": 3,
  },
  {
    "report_id": "YYY",
    "available_count": 1,
  }]
}

Is there anyway to do this?

The "YYY" available count is actually 2 since there are 2 values of true in available. — Neil Lunn
– Neil Lunn, Commented Jun 12, 2017 at 13:39
The "YYY" available count should be 1 since the second element's return_code value is not "OK". — chenzww
– chenzww, Commented Jun 12, 2017 at 14:24
Fair enough, I missed that condition. Answer amended to include the other condition. I need to sleep now. Have fun. — Neil Lunn
– Neil Lunn, Commented Jun 12, 2017 at 14:40

Neil Lunn · Accepted Answer · 2017-06-13 13:28:03Z

1

With a modern MongoDB 3.4 you can do this with $replaceRoot and $arrayToObject:

db.collection.aggregate([
  { "$replaceRoot": {
    "newRoot": {
      "$arrayToObject": {
        "$concatArrays": [
          [
            { 
              "k": "$_id", 
              "v": {
                "$map": {
                  "input": "$report.google",
                  "as": "el",
                  "in": {
                    "report_id": "$$el.report_id",
                    "available_count": { 
                      "$size": {
                        "$filter": {
                          "input": "$$el.detail",
                          "as": "d",
                          "cond": {
                           "$and": [
                             "$$d.available",
                             { "$eq": [ "$$d.return_code", "ok" ] }
                            ]
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          ]
        ]
      }
    }
  }}
])

But you can basically do this in any version with a little client side code:

db.collection.find().forEach(doc => {
  doc[doc._id] = doc.report.google.map(el => {
    el.available_count = el.detail.filter(d => d.available && d.return_code === "ok").length;
    delete el.detail;
    return el;
  });
  delete doc._id;
  delete doc.report;
  printjson(doc);
})

Both produce the same thing:

{
        "AAABBBCCCDDDD" : [
                {
                        "report_id" : "XXX",
                        "available_count" : 3
                },
                {
                        "report_id" : "YYY",
                        "available_count" : 1
                }
        ]
}

So you don't really need aggregation here at all since it's just really reshaping the document.

Original Data from the Question as asked

{
  "_id": "AAABBBCCCDDDD",
  "report":{
    "google": [{
        "report_id": "XXX",
        "detail": [{
            "available": true,
            "status_code": 200,
            "return_code": "ok",
        }, {
            "available": true,
            "status_code": 300,
            "return_code": "ok",
        }, {
            "available": true,
            "status_code": 400,
            "return_code": "ok",
        }]
    }, {
        "report_id": "YYY",
        "detail": [{
            "available": false,
            "status_code": 200,
            "return_code": "ok",
        }, {
            "available": true,
            "status_code": 200,
            "return_code": "ng",
        }, {
            "available": true,
            "status_code": 200,
            "return_code": "ok",
        }]
    }]
  }
}

edited Jun 13, 2017 at 13:28

answered Jun 12, 2017 at 13:34

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

chenzww Over a year ago

Something was wrong when run it on Mongo Shell. 2017-06-13T03:45:22.636+0800 E QUERY [thread1] Error: command failed: { "ok" : 0, "errmsg" : "$arrayToObject requires an object with keys 'k' and 'v', where the value of 'k' must be of type string. Found type: objectId", "code" : 40394, "codeName" : "Location40394" } : aggregate failed : _getErrorWithCode@src/mongo/shell/utils.js:25:13 doassert@src/mongo/shell/assert.js:16:14 assert.commandWorked@src/mongo/shell/assert.js:370:5 DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1319:5

Neil Lunn Over a year ago

@chenzww The answer does state very clearly at the beginning the to use .aggregate() with $arrayToObject you require MongoDB 3,4. I also state again very clearly that you "should" in fact not be using aggregate at all since the language based code to transform is very trivial and simple.

chenzww Over a year ago

I confirm the version use "mongod --version" in terminal. It return "db version v3.4.4". Then, execute "[{ "$limit": 1 }, {"$project": {"converted": {"$arrayToObject": {"$objectToArray":{"baka": true}}}}}]", and it return "{ "_id" : ObjectId("592b18616cd0bb578c7a9887"), "converted" : { "baka" : true } }". Every thing seems to work fine. What's the problem with my mongodb environment?

Neil Lunn Over a year ago

@chenzww If you have a new question then Ask a new Question. The answer here is given to the question you asked, and works exactly as stated. At the risk of repeating myself for now the third time, not only does the aggregation statement work as listed here, but the lesson you "should" be learning is that you do not use aggregation for this type of operation at all. Manipulating the object requires "three" lines of code compared to the very terse aggregation operation. The output here is as listed run on my system from both methods provided.

chenzww Over a year ago

First, nothing cannot be resolved without aggregation framework. As you said, manipulate the object is quite simple. However, I just want to know a way that just use mongodb. Second, the raised error above may due to the fact that the value of "k" is not a string. It worked when I fill it with a string. Is it difficult to convert _id to string type? Third, I don't know why available_count's value is always be "0" unless I comment out the '"$$d.available",' line.

|

Collectives™ on Stack Overflow

Aggregate count matched conditional elements of nested array

1 Answer 1

Original Data from the Question as asked

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Original Data from the Question as asked

9 Comments

Your Answer

Sign up or log in

Post as a guest