Mongodb Query, search array elements inside document grouping by and counting each element

Question

Suppose I have a collection with multiple documents with the following structure:

{
  _id: "id12345",
  objects: ["123","456", "789"]
}

Now I want to create a query where I pass an array of objects and get the count of occurrence in documents for each of the elements of the array, something like this:

input of query: ["123","321"] output:

{
  "123": 1,
  "321": 0
}

I want to do this just by one query, passing an array, I know I could do this easily one by one instead of an array but that is not the purpose.

s7vr · Accepted Answer · 2018-08-21 16:45:05Z

1

You can use below aggregation in 3.6.

$match to filter documents where there is at least one array match between input array and objects array.

$project with $map to iterate the input array and compare with each array element in objects array and return 0 and 1 depending on the match.

$unwind with $group to count the occurrences of array element across all documents.

db.colname.aggregate([
 {"$match":{"objects":{"$in":["123","321"]}}},
 {"$project":{
   "keyandcount":{
     "$map":{
       "input":["123","321"],
       "in":{
         "k":"$$this",
         "v":{"$cond":[{"$in":["$$this","$objects"]},1,0]}
       }
     }
   }
 }},
 {"$unwind":"$keyandcount"},
 {"$group":{"_id":"$keyandcount.k","count":{"$sum":"$keyandcount.v"}}}
])

You can add below two stages at the end to format the response to key value pair.

{"$group":{_id: null,"keycountpair":{"$mergeObjects":{"$arrayToObject":[[["$_id","$count"]]]}}}},
{"$replaceRoot":{"newRoot":"$keycountpair"}}

edited Aug 21, 2018 at 16:45

answered Aug 21, 2018 at 13:23

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

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

3 Comments

Luis Miguel Over a year ago

HI @Veeram, thanks for the answer it works as expected, I need to implement this using spring data MongoDB, i know that "Aggregation" spring operation this is possible but I'm having some problems with the $map operation, using spring "mapItemsOf" function, can you give me an idea of how this should look like ?

s7vr Over a year ago

You are welcome. I don't think you would be able to use mapItemsOf as it only takes field reference or aggregation expression not a static array. You need to prepare the aggregation expression. Something like

project().and(new AggregationExpression() {                         @Override                         public Document toDocument(AggregationOperationContext aggregationOperationContext) {                             $map code here                         }                     }).as("keyandcount")

s7vr Over a year ago

You can use the sample from here and adjust to your need.

Akrion · Accepted Answer · 2018-08-21 04:41:46Z

In general the query to get this is pretty straight forward since it is not recommended to switch values to keys in the data layer but more in the presentation one.

So this:

db.collection.aggregate([
  {
    $unwind: "$objects"
  },
  {
    $group: {
      _id: "$objects",
      count: {
        $sum: 1
      }
    }
  }
])

Would give you the numbers as _id and a count field with the number of occurrences. Then you can massage that in your app to make it look anyway you would like.

Here is a simple example of the result

Collectives™ on Stack Overflow

Mongodb Query, search array elements inside document grouping by and counting each element

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related