mongodb - Merge object arrays based on key

Question

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)

@nimrod serok I can't know that the order of relatedPeople and relatedPeople2 will be the same each time, I don't think — Jason
– Jason, Commented Dec 8, 2022 at 12:26

nimrod serok · Accepted Answer · 2022-12-08 12:50:04Z

2

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

edited Dec 8, 2022 at 12:50

answered Dec 8, 2022 at 12:36

nimrod serok

16.1k2 gold badges15 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jason Over a year ago

I did not know about $indexOfArray. It's just what I needed! Thanks!!

Collectives™ on Stack Overflow

mongodb - Merge object arrays based on key

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related