mongodb - Merge object arrays based on key-CodePudding

In a mongodb database, I have the following data:

// db.people
[
  {
    _id: ObjectId("..."),
    id: 111111111,
    name: "George",
    relatedPeople: [{ id: 222222222, relation: "child" }],
    // A bunch of other data I don't care about
  },
  {
    _id: ObjectId("..."),
    id: 222222222,
    name: "Jacob",
    relatedPeople: [{ id: 111111111, relation: "father" }],
    // A bunch of other data I don't care about
  },
  {
    _id: ObjectId("..."),
    id: 333333333,
    name: "some guy",
    relatedPeople: [],
    // A bunch of other data I don't care about
  },
]

I would like to query the people, and select only the fields I've shown, but have extra data in relatedPeople (id relation name)

So the desired output would be:

[
  {
    _id: ObjectId("..."),
    id: 111111111,
    name: "George",
    relatedPeople: [{ id: 222222222, relation: "child", name: "Jacob" }],
  },
  {
    _id: ObjectId("..."),
    id: 222222222,
    name: "Jacob",
    relatedPeople: [{ id: 111111111, relation: "father", name: "George" }],
  },
  {
    _id: ObjectId("..."),
    id: 333333333,
    name: "some guy",
    relatedPeople: [],
  },
]

I can get something close, with this query:

db.people.aggregate([
  // { $match: { /** ... */ }, },
  {
    $lookup: {
      from: "people",
      let: { relatedPeopleIds: "$relatedPeople.id" },
      pipeline: [
        { $match: { $expr: { $in: ["$id", "$$relatedPeopleIds"] } } },
        {
          $project: {
            id: 1,
            name: 1,
          },
        },
      ],
      as: "relatedPeople2",
    },
  },
  {
    $project: {
      id: 1,
      name: 1,
      relatedPeople: 1,
      relatedPeople2: 1,
    }
  }
]);

But the data is split between two fields. I want to merge each object in the arrays by their id, and place the result array in relatedPeople

I found this question, but that merge is done over a range and uses $arrayElementAt which I can't use
I also tried looking at this question, but I couldn't get the answer to work (Kept getting empty results)

CodePudding user response：

You can add one step using $arrayElementAt with $indexOfArray:

db.people.aggregate([
  // { $match: { /** ... */ }, },
  {$project: {id: 1, name: 1, relatedPeople: 1}},
  {$lookup: {
      from: "people",
      let: { relatedPeopleIds: "$relatedPeople.id" },
      pipeline: [
        { $match: { $expr: { $in: ["$id", "$$relatedPeopleIds"] } } },
        {
          $project: {
            id: 1,
            name: 1,
          },
        },
      ],
      as: "relatedPeople2",
    },
  },
  {$set: {
      relatedPeople: {$map: {
          input: "$relatedPeople",
          in: {$mergeObjects: [
              "$$this",
              {$arrayElemAt: [
                  "$relatedPeople2",
                  {$indexOfArray: ["$relatedPeople2.id", "$$this.id"]}
                ]}
          ]}
      }}
  }},
  {$unset: "relatedPeople2"}
])

See how it works on the playground example