Conditionally reduce two array fields in Mongo Aggregation

Question

I have a collection which looks like below:

{
  "_id": 1,
  "user": "xyz",
  "sentence": "I watch movies and web series.",
  "nouns": [
    "movies",
    "web series"
  ],
  "verbs": [
    "watch"
  ]
},
{
  "_id": 2,
  "user": "xyz",
  "sentence": "movies are good way to relax",
  "nouns": [
    "movies"
  ],
  "verbs": [
    "relax"
  ]
}

There are two array fields, nouns and verbs for each user's sentences. I want to group the documents by user field and separately count the number of each distinct elements in nouns and verbs arrays. I have tried the following query (if you wan't you can skip to the last stage of this aggregation):

db.collection.aggregate([
  {
    $group: {
      _id: "$user",
      sentence: {
        $push: "$sentence"
      },
      verbs: {
        $push: "$verbs"
      },
      nouns: {
        $push: "$nouns"
      }
    }
  },
  {
    $project: {
      verbs: {
        $reduce: {
          input: "$verbs",
          initialValue: [],
          in: {
            $concatArrays: [
              "$$value",
              "$$this"
            ]
          }
        }
      },
      nouns: {
        $reduce: {
          input: "$nouns",
          initialValue: [],
          in: {
            $concatArrays: [
              "$$value",
              "$$this"
            ]
          }
        }
      },
      sentence: 1
    }
  },
  {
    $project: {
      nouns_count_temp: {
        $map: {
          input: "$nouns",
          as: "c",
          in: {
            k: "$$c",
            v: 1
          }
        }
      },
      verbs_count_temp: {
        $map: {
          input: "$verbs",
          as: "c",
          in: {
            k: "$$c",
            v: 1
          }
        }
      },
      sentence: 1
    }
  },
  {
    $project: {
      sentence: 1,
      noun_count: {
        $reduce: {
          input: "$nouns_count_temp",
          initialValue: [],
          in: {
            $cond: [
              {
                $in: [
                  {
                    k: "$$this.k",
                    v: "$$this.v"
                  },
                  "$$value"
                ]
              },
              {
                $add: [
                  "$$value.$.v",
                  1
                ]
              },
              {
                $concatArrays: [
                  "$$value",
                  [
                    {
                      k: "$$this.k",
                      v: "$$this.v"
                    }
                  ]
                ]
              }
            ]
          }
        }
      },
      verb_count: {
        $reduce: {
          input: "$verbs_count_temp",
          initialValue: [],
          in: {
            $cond: [
              {
                $in: [
                  {
                    k: "$$this.k",
                    v: "$$this.v"
                  },
                  "$$value"
                ]
              },
              {
                $add: [
                  "$$value.$.v",
                  1
                ]
              },
              {
                $concatArrays: [
                  "$$value",
                  [
                    {
                      k: "$$this.k",
                      v: "$$this.v"
                    }
                  ]
                ]
              }
            ]
          }
        }
      }
    }
  }
])

I am facing problem in the last state of the aggregation. I want to know, if there is any better way to use $cond in $reduce, so that I can conditionally reduce the arrays.

My expected output is like below:

{
  "_id": "xyz",
  "noun_count": {
    "movies": 2,
    "web series": 1
  },
  "sentence": [
    "I watch movies and web series.",
    "movies are good way to relax"
  ],
  "verb_count": {
    "relax": 1,
    "watch": 1
  }
}

Here is the MongoPlayGroundLink, that I have tried.

user_3710490 · Answer

Unfortunately, we can't build dynamic key:value object within $reduce operator.

Workaround: We combine nouns and verbs in a single array and count how many times they are repeated.

db.collection.aggregate([
  {
    $group: {
      _id: "$user",
      sentence: {
        $push: "$sentence"
      },
      verbs: {
        $push: "$verbs"
      },
      nouns: {
        $push: "$nouns"
      }
    }
  },
  {
    $project: {
      sentence: 1,
      verbs: {
        $reduce: {
          input: "$verbs",
          initialValue: [],
          in: {
            $concatArrays: [
              "$$value",
              "$$this"
            ]
          }
        }
      },
      nouns: {
        $reduce: {
          input: "$nouns",
          initialValue: [],
          in: {
            $concatArrays: [
              "$$value",
              "$$this"
            ]
          }
        }
      }
    }
  },
  {
    $addFields: {
      mix: {
        $concatArrays: [
          "$verbs",
          "$nouns"
        ]
      }
    }
  },
  {
    $unwind: "$mix"
  },
  {
    $group: {
      _id: {
        user: "$_id",
        word: "$mix"
      },
      count: {
        $sum: 1
      },
      sentence: {
        $first: "$sentence"
      },
      verbs: {
        $first: "$verbs"
      },
      nouns: {
        $first: "$nouns"
      }
    }
  },
  {
    $group: {
      _id: "$_id.user",
      data: {
        $push: {
          k: "$_id.word",
          v: "$count"
        }
      },
      verbs: {
        $first: "$verbs"
      },
      nouns: {
        $first: "$nouns"
      },
      sentence: {
        $first: "$sentence"
      }
    }
  },
  {
    $project: {
      _id: 1,
      sentence: 1,
      noun_count: {
        $arrayToObject: {
          $filter: {
            input: "$data",
            as: "data",
            cond: {
              $in: [
                "$$data.k",
                "$nouns"
              ]
            }
          }
        }
      },
      verb_count: {
        $arrayToObject: {
          $filter: {
            input: "$data",
            as: "data",
            cond: {
              $in: [
                "$$data.k",
                "$verbs"
              ]
            }
          }
        }
      }
    }
  }
])

MongoPlayground | Alternative solution

Note: MapReduce solution is slower then aggregation

user_3879440 · Answer

Playground link - https://mongoplayground.net/p/dC4adNChwyD

The query I use:

db.collection.aggregate([
  {
    $group: {
      _id: "$user",
      sentence: {
        $push: "$sentence"
      },
      verbs: {
        $push: "$verbs"
      },
      nouns: {
        $push: "$nouns"
      }
    }
  },
  {
    $project: {
      verbs: {
        $reduce: {
          input: "$verbs",
          initialValue: [],
          in: {
            $concatArrays: [
              "$$value",
              "$$this"
            ]
          }
        }
      },
      nouns: {
        $reduce: {
          input: "$nouns",
          initialValue: [],
          in: {
            $concatArrays: [
              "$$value",
              "$$this"
            ]
          }
        }
      },
      sentence: 1
    }
  },
  {
    "$unwind": "$nouns"
  },
  {
    "$group": {
      "_id": {
        "_id": "$_id",
        "noun": "$nouns"
      },
      "sentence": {
        "$first": "$sentence"
      },
      "key": {
        "$first": "$_id"
      },
      "verbs": {
        "$first": "$verbs"
      },
      "count": {
        "$sum": 1
      }
    }
  },
  {
    "$group": {
      "_id": "$key",
      "sentence": {
        "$first": "$sentence"
      },
      "verbs": {
        "$first": "$verbs"
      },
      "nouns": {
        $push: {
          k: "$_id.noun",
          v: "$count"
        }
      }
    }
  },
  {
    $project: {
      _id: 1,
      sentence: 1,
      verbs: 1,
      nouns: {
        $arrayToObject: "$nouns"
      }
    }
  },
  {
    "$unwind": "$verbs"
  },
  {
    "$group": {
      "_id": {
        "_id": "$_id",
        "verb": "$verbs"
      },
      "sentence": {
        "$first": "$sentence"
      },
      "key": {
        "$first": "$_id"
      },
      "nouns": {
        "$first": "$nouns"
      },
      "count": {
        "$sum": 1
      }
    }
  },
  {
    "$group": {
      "_id": "$key",
      "sentence": {
        "$first": "$sentence"
      },
      "nouns": {
        "$first": "$nouns"
      },
      "verbs": {
        $push: {
          k: "$_id.verb",
          v: "$count"
        }
      }
    }
  },
  {
    $project: {
      _id: 1,
      sentence: 1,
      nouns: 1,
      verbs: {
        $arrayToObject: "$verbs"
      }
    }
  }
])

user_2502126 · Answer

Altenative approach: "do almost nothing in the database."

The OP wishes to find a unique set of nouns and verbs, the count of each, and an array of sentences, grouped by user. There is no filtering here, only agg. A guiding principle is to make efficient the use of the DB engine to find and filter things to pass over the wire, not just agg. And we can see, there is lot of tortuous agg. And in the end, we want a concatentation of all the sentences which is arguably the bulk of the bytes coming across the wire, whether in each doc or packaged in one doc in a big array. Let's look at two scenarios. In each, the result will be (I expanded upon the OP input set a bit):

{
    "xyz" : {
        "nouns_count" : {
            "movies" : 3,
            "baseball stats" : 1,
            "web series" : 1
        },
        "verbs_count" : {
            "watch" : 2,
            "reap" : 1,
            "relax" : 1
        },
        "sentences" : [
            "I watch movies and reap baseball stats",
            "I watch movies and web series",
            "movies are a good way to relax"
        ]
    },
    "abc" : {
        "nouns_count" : {
            "corn" : 1,
            "hay" : 1
        },
        "verbs_count" : {
            "reap" : 2
        },
        "sentences" : [
            "I reap corn",
            "I reap hay"
        ]
    }
}

Scenario A: Very few number of unique user in DB, e.g. count of group(user) is almost same as count() AND the number of identical nouns and identical verbs is few.

In this scenario, the number of unique docs passed over the wire is nearly the same so let more come through and let the DB engine do NOTHING. Just do a find() and rework the objects and arrays on the client side. The same work is being done but frankly it is easier to do will a full programming language and it is far less impactful on the DB:

var xx = {};
db.foo.find().forEach(function(d) {  // Just find!  VERY fast for DB engine!
    var k = d['user'];
    if(undefined == xx[k]) {
        xx[k] = {
          nouns_count: {},
          verbs_count: {},
          sentences: [] // just an array!                                    
        }
    }
    qq = xx[k]; // makes things a little simpler to read...                   

    ['nouns','verbs'].forEach(function(pfx) {
        fld = pfx + "_count";
        d[pfx].forEach(function(v) {
            if(undefined == qq[fld][v]) {
                 qq[fld][v] = 0;
            }
            qq[fld][v] += 1;
        });
    });

    qq['sentences'].push(d['sentence']);
});

Scenario B: Very LARGE number of unique user in DB, e.g. count of group(user) is much smaller than count() AND the number of identical nouns and identical verbs is few.

In this case, it may make sense to let the DB do the agg to reduce the number of docs flowing across the wire. Keep in mind, though that the desire to move over the concatenated sentence array means 10 docs of user A with a sentence vs. 1 doc of user A with 10 sentences does not yield much difference. We still "postprocess" on the client side:

c = db.foo.aggregate([
{$group: {_id: "$user",
          "nouns": {$push: "$nouns"},
          "verbs": {$push: "$verbs"},
          "sentences": {$push: "$sentence"}
    }}
                      ]);

var xx = {};
while(c.hasNext()) { // Each _id is the unique user.
    d = c.next();
    var k = d['_id'];
    xx[k] = {
        nouns_count: {},
        verbs_count: {},
        sentences: [] // just an array!                                           
    }
    qq = xx[k]; // makes things a little simpler to read...                       

    //  Incoming nouns and verbs are now array of arrays because of group, so extra loop is needed:

    ['nouns','verbs'].forEach(function(pfx) {
        fld = pfx + "_count";
            d[pfx].forEach(function(arr) {
                    arr.forEach(function(v) {
                            if(undefined == qq[fld][v]) {
                        qq[fld][v] = 0;
                            }
                            qq[fld][v] += 1;
                        });
        });
        });

    d['sentences'].forEach(function(s) {
            qq['sentences'].push(s);
    });
}

Conditionally reduce two array fields in Mongo Aggregation

3 Answers