Home > Blockchain >  How to get average of two values which are in different objects?
How to get average of two values which are in different objects?

Time:03-03

My data looks like:

[
  {
    "_id": ObjectId("6213baa5a013b7c5f1232e23"),
    "birthDate": ISODate("1973-05-01T13:30:15Z"),
    "surname": "Johnson",
    "name": "Emma",
    "registerDate": ISODate("1900-06-11T17:30:15Z"),
    "children": [
      {
        "birthDate": ISODate("1993-05-21T16:30:15Z"),
        "surname": "Johnson",
        "name": "Liam"
      },
      {
        "birthDate": ISODate("1994-01-21T15:30:15Z"),
        "surname": "Johnson",
        "name": "Olivia",
        "registerDate": ISODate("2019-09-14T12:30:15Z")
      }
    ],
    "city": "Houston"
  }
]

I want to calculate registration ages of persons who have registerDate. In this example, I should get average of registration ages of Emma(parent) and Olivia(child). I tried this. I calculated the registration ages and added them as a field. I got Olivia's registration age. However, I have duplicate registration ages of Emma. I want to use one of them. How can I get average of registration ages which belong to Emma and Olivia. I mean (5 1)/2=3. You can see these values in the link that added.

CodePudding user response:

You can first use $map to get ages array. Then use $avg to get average age.

$avg ignores non-numeric values, including missing values. If all of the operands for the average are non-numeric, $avg returns null since the average of zero values is undefined.

[
  {
    "$addFields": {
      "ages": {
        "$map": {
          "input": "$children",
          "as": "child",
          "in": {
            $dateDiff: {
              startDate: "$$child.registerDate",
              endDate: "$$NOW",
              unit: "year"
            }
          }
        }
      }
    }
  },
  {
    "$addFields": {
      averageAge: {
        "$avg": {
          "$concatArrays": [
            "$ages",
            [
              {
                $dateDiff: {
                  startDate: "$registerDate",
                  endDate: "$$NOW",
                  unit: "year"
                }
              }
            ]
          ]
        }
      }
    }
  }
]

Mongo playground

  • Related