mongodb aggregate multi count

user_11415513 0
Viewed 51

i have users and each user have these data for exmp :

userOne = [
  
  fruits : ['banana','apple','pineapple','somethingElse'],
  colors : ['red','blue','green'],
  habit : ['read','sing','sleep']
]

i want to return something like this :

fruits: {hasBanana:11, hasPineapple:22, ......}
colors: {hasRed:11, hasBlue:0, ......}
habit : {hasRead:2, hasSleep:10}

i was going to do it this way

db.getCollection('users').aggregate([
    {$group : {_id: {tags:"$banana"}}},
    {$match : {"_id.banana":"banana"}},
    {$count : 'hasBanana'}      
])

will return this:

{
    "hasBanana" : 59
}

and repeat it manytime but i believe there is short way to do it

aggregation-framework mongodb aggregate
1 Answers

Query

  • add to each array the type (3x the same code)
  • concatArrays to make all 1 array and unwind (we have the type saved so we don't lose it)
  • $facet because query is like 3 queries, and its complex to mix them(i wrote one without facet, worked but was so complicated)
  • again the facet code is same code 3x
  • arrayToObject to make those data in keys like "hasBanana" : 2

*query is big but its real size is the 1/3 of it, so its not really big, also saving data on keys and here nested data on keys("fruits","hasBanana" are both data nested keys) makes things harder in general

Test code here

aggregate(
[{"$set": 
    {"fruits": 
      {"$map": {"input": "$fruits", "in": {"type": "fruits", "v": "$$this"}}}}},
  {"$set": 
    {"colors": 
      {"$map": {"input": "$colors", "in": {"type": "colors", "v": "$$this"}}}}},
  {"$set": 
   {"habit": {"$map": {"input":"$habit","in":{"type":"habit", "v": "$$this"}}}}},
  {"$project": 
    {"data": {"$concatArrays": ["$fruits", "$colors", "$habit"]}}},
  {"$unwind": {"path": "$data"}},
  {"$project": {"_id": 0, "type": "$data.type", "v": "$data.v"}},
  {"$facet": 
    {"fruits": 
      [{"$match": {"$expr": {"$eq": ["$type", "fruits"]}}},
        {"$group": {"_id": "$v", "count": {"$sum": 1}}},
        {"$project": 
          {"_id": 0, "k": {"$concat": ["has", "$_id"]}, "v": "$count"}}],
      "colors": 
      [{"$match": {"$expr": {"$eq": ["$type", "colors"]}}},
        {"$group": {"_id": "$v", "count": {"$sum": 1}}},
        {"$project": 
          {"_id": 0, "k": {"$concat": ["has", "$_id"]}, "v": "$count"}}],
      "habit": 
      [{"$match": {"$expr": {"$eq": ["$type", "habit"]}}},
        {"$group": {"_id": "$v", "count": {"$sum": 1}}},
        {"$project": 
          {"_id": 0, "k": {"$concat": ["has", "$_id"]}, "v": "$count"}}]}},
  {"$set": 
    {"fruits": {"$arrayToObject": ["$fruits"]},
      "colors": {"$arrayToObject": ["$colors"]},
      "habit": {"$arrayToObject": ["$habit"]}}}])
user_48826920
Related