database - mongodb array aggregation extract to parent

Question

I am trying to get first date from inner array in mongodb object and add it to it's parent with aggregation. Example:

car: {
  "model": "Astra",
  "productions": [
    "modelOne": {
      "dateOfCreation": "2019-09-30T10:15:25.026+00:00",
      "dateOfEstimation": "2017-09-30T10:15:25.026+00:00",
      "someOnterInfo": "whatever"
    },
    "modelTwo": {
      "dateOfCreation": "2017-09-30T10:15:25.026+00:00",
      "dateOfEstimation": "2019-09-30T10:15:25.026+00:00",
      "someOnterInfo": "whatever"
    }
  ]
}

to be turned in

car: {
  "model": "Astra",
  "earliestDateOfEstimation": "2017-09-30T10:15:25.026+00:00",
  "earliestDateOfCreation": "2017-09-30T10:15:25.026+00:00"
}

How can I achieve that?

标签： databasemongodbnosqlaggregateaggregation

Answer 1

I'm assuming that modelOne and modelTwo are unknown when you start your aggregation. The key step is to run $map along with $objectToArray in order to get rid of those two values. Then you can just use $min to get "earliest" values:

db.collection.aggregate([
    {
        $addFields: {
            dates: {
                $map: {
                    input: "$car.productions",
                    in: {
                        $let: {
                            vars: { model: { $arrayElemAt: [ { $objectToArray: "$$this" }, 0 ] } },
                            in: "$$model.v"
                        }
                    }
                }
            }
        }
    },
    {
        $project: {
            _id: 1,
            "car.model": 1,
            "car.earliestDateOfEstimation": { $min: "$dates.dateOfEstimation" },
            "car.earliestDateOfCreation": { $min: "$dates.dateOfCreation" },
        }
    }
])

Mongo Playground

EDIT:

First step can be simplified if there's always modelOne, 'modelTwo'... (fixed number)

db.collection.aggregate([
    {
        $addFields: {
            dates: { $concatArrays: [ "$car.productions.modelOne", "$car.productions.modelTwo" ] }
        }
    },
    {
        $project: {
            _id: 1,
            "car.model": 1,
            "car.earliestDateOfEstimation": { $min: "$dates.dateOfEstimation" },
            "car.earliestDateOfCreation": { $min: "$dates.dateOfCreation" },
        }
    }
])

Mongo Playground (2)