Merge aggregation in mongodb

Question

I need to write a code that solves the following question:

On the actors collection, show the actors whose career has been the longest.

I start from the following .json file: https://drive.google.com/file/d/1VKuhce2ofjLjYEoND_Fz2wqcYHAf6kkZ/view

//unwind
    db.getCollection("Tarea").find({})
    fase1 = { $unwind: "$cast"}
    etapas = [fase1]
    db.Tarea.aggregate( etapas )

//out
    fase1 = { $unwind: "$cast" }
    query2 = { "_id": 0 }
    fase2 = { $project: query2 }
    fase3 = { $out: "actors" }
    etapas = [ fase1, fase2, fase3 ]
    db.Tarea.aggregate( etapas )

So I created the actors collection. And now I need to know how long the actors have been active. I suppose it could be done by grouping the actors in the _id column and creating a new column that subtracts:

'Most recent movie year - First movie year'

to later order that same column from highest to lowest, thus obtaining the "career time" of each actor.

I don't know if there is another simpler way to do it.

Thanks to everyone beforehand.

Answer 1

not sure about the coding language of the op but here's a c# version if anybody's interested:

using MongoDB.Entities;
using MongoDB.Entities.Core;
using System.Linq;

namespace StackOverflow
{
    public class movie : Entity
    {
        public string title { get; set; }
        public int year { get; set; }
        public string[] cast { get; set; }
    }

    public class Program
    {
        private static void Main(string[] args)
        {
            new DB("test", "localhost");

            var res = DB.Queryable<movie>() // for official driver use: collection.AsQueryable()

                        .SelectMany(m => m.cast,
                                   (m, a) => new { actor = a, m.year })

                        .GroupBy(x => x.actor)

                        .Select(g => new
                        {
                            actor = g.Key,
                            careerLength = g.Max(x => x.year) - g.Min(x => x.year)
                        })

                        .OrderByDescending(x => x.careerLength)

                        .Take(100)

                        .ToArray();
        }
    }
}

Answer 2

There are Many Many ways you could do this, with that said - I would use this pipeline:

db.collection("Tarea").aggregate([
    {
        $unwind: "$cast"
    },
    {
        $group: {
            _id: "$cast",
            last: {$max: "$year"},
            first: {$min: "$year"}
        }
    },
    {
        $project: {
            actor: "$_id",
            careerLength: {$subtract: ["$last", "$first"]}
        }
    },
    {
        $sort: {
            careerLength: -1
        }
    }
]);