MongoDB query to return multiple values from an array

Question

I think I'm almost there with what I want to do but I'm falling at the last hurdle. I need to extract multiple values from a nested array along with a count.

I have many documents that looks like this:

{
    "_id" : ObjectId("547db34cd9460c25e6000002"),
    "doc_number" : "500715",
    "error_list" : [ 
        {
            "extractor" : "Code Check",
            "message_number" : "RC9999",
            "message" : "Code is not synchronised"
        }, 
        {
            "extractor" : "Metadata Check",
            "message_number" : "RC1043",
            "message" : "No metadata for document"
        }, 
        {
            "extractor" : "Property Extractor",
            "message_number" : "PE1012",
            "message" : "No properties found"
        }
    ]
}

I can query the collection to get a count of each error code with:

db.errors.aggregate( 
  [ 
    { $unwind : "$error_list" }, 
    { $group : 
        { _id : "$error_list.message_number", 
            count: { $sum : 1 } 
         }
     }
  ] 
);

and return this:

{
    "result" : [ 
        {
            "_id" : "PE1012",
            "count" : 12
        }, 
        {
            "_id" : "RC1043",
            "count" : 2
        }, 
        {
            "_id" : "RC9999",
            "count" : 10
        }
    ],
    "ok" : 1
}

What I would like to add to the results is the message text. I can't quite work out how to do that so any help would be great.

I would like the results to look similar to this:

{
    "result" : [ 
        {
            "_id" : "PE1012",
            "count" : 12,
            "message" : "No properties found"

        }, 
        {
            "_id" : "RC1043",
            "count" : 2,
            "message" : "No metadata for document"
        }, 
        {
            "_id" : "RC9999",
            "count" : 10,
            "message" : "Code is not synchronised"
        }
    ],
    "ok" : 1
}

BatScream · Accepted Answer · 2014-12-03 18:41:03Z

2

You need to make use of the $first or $last operators, if all the records having the same error code would also have the same error message.

db.errors.aggregate( 
  [ 
    { $unwind : "$error_list" }, 
    { $group : { "_id" : "$error_list.message_number",
                 "message":{$first:"$error_list.message"}, 
                 "count": { $sum : 1 }}
     }
  ] 
);

If they have could have different error messages then you need to form an array of error messages using $addToSet.

db.errors.aggregate( 
  [ 
    { $unwind : "$error_list" }, 
    { $group : { "_id" : "$error_list.message_number",
                 "messages":{$addToSet:"$error_list.message"}, 
                 "count": { $sum : 1 }}
     }
  ] 
);

answered Dec 3, 2014 at 18:41

BatScream

19.7k4 gold badges54 silver badges68 bronze badges

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

1 Comment

spannerj Over a year ago

Sorry for my slow reply. This is exactly what I was after, Thank you for your excellent explanation.

Collectives™ on Stack Overflow

MongoDB query to return multiple values from an array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related