MongoDB - Convert array elements as new fields

Question

I have mongodb collection with documents like below:

{"name": "person1", "parents": [
    {"relationship": "father", "name": "father1", "age": 40},
    {"relationship": "mother", "name": "mother1", "age": 42}
]}

{"name": "person2", "parents": [
    {"relationship": "father", "name": "father2", "age": 50},
    {"relationship": "mother", "name": "mother2", "age": 45}
]}

I am trying to flatten the documents like below:

{"name": "person1",  "father_name": "father1", "father_age": 40, "mother_name": "mother1", "mother_age": 42}
{"name": "person2",  "father_name": "father2", "father_age": 50, "mother_name": "mother2", "mother_age": 45}

Basically, I want to take certain fields from every element (object) of parents array and make them as new fields in output documents. How can I achieve this?

I wrote aggregation pipeline like below:

[
    {
      $project: {
        _id: 0,
        name: 1,
        father_name: {
          $cond: {
            if: {
              $eq: ["$parents.relationship", "father"],
            },
            then: "$parents.name",
            else: null,
          },
        },
        father_age: {
          $cond: {
            if: {
              $eq: ["$parents.relationship", "father"],
            },
            then: "$parents.age",
            else: null,
          },
        },
        mother_name: {
          $cond: {
            if: {
              $eq: ["$parents.relationship", "mother"],
            },
            then: "$parents.name",
            else: null,
          },
        },
        mother_age: {
          $cond: {
            if: {
              $eq: ["$parents.relationship", "mother"],
            },
            then: "$parents.age",
            else: null,
          },
        },
      },
    },
  ]

But it is giving output like below, which is not as expected:

{
  "name": "person1",
  "father_name": null,
  "father_age": null,
  "mother_name": null,
  "mother_age": null
}

Answer 1

One option for a generic solution is:

db.collection.aggregate([
  {$set: {
      parents: {$map: {
          input: "$parents",
          as: "i",
          in: {$arrayToObject: {$reduce: {
                input: {$objectToArray: "$$i"},
                initialValue: [],
                in: {$concatArrays: [
                    "$$value",
                    {$cond: [
                        {$ne: ["$$this.k", "relationship"]},
                        [
                          {k: {$concat: ["$$i.relationship", "_", "$$this.k"]},
                           v: "$$this.v"}
                        ],
                        []
                    ]}
                ]}
            }}}
      }}
  }},
  {$replaceRoot: {
      newRoot: {$mergeObjects: [
          "$$ROOT",
          {$reduce: {
              input: "$parents",
              initialValue: {},
              in: {$mergeObjects: ["$$value", "$$this"]}
          }}
      ]}
  }},
  {$unset: "parents"}
])

See how it works on the playground example

*The generic solution supports adding fields or relationship types without adding any stages