Mongodb Question posted in
The official documentation can be found here.

How to group two arrays in mongoDB?

I have a collection of documents books:

{
  title: "My book",
  authors: [{id: 1, date: "2023-12-20"},{id: 2, date: "2023-12-21"}],
  authorsDetails: [{id: 2, name:"Author 2", age: 25}, {id: 1, name: "Author 1", age: 38}],
}

I need to do an aggregation and the result must return the following result:

{
  title: "My book",
  authors: [{id: 1, date: "2023-12-20", name: "Author 1"},{id: 2, date: "2023-12-21", name: "Author 2"}],
}

Basically I need to add some informations from authorsDetails to authors. In this case only the name.

NOTE: The two arrays are not necessary sorted in the same way.

I tried an $addFields pipe + $map but it is not enough. I tried this:

const pipeline = [
    //...
    {
      $addFields: {
        indices: { $range: [0, { $size: '$authors' }] },
      },
    },
    {
      $addFields: {
        admins: {
          $map: {
            input: '$indices',
            as: 'index',
            in: {
              detail: { $arrayElemAt: ['$authorsDetails', '$$index'] },
              author: { $arrayElemAt: ['$authors', '$$index'] },
            },
          },
        },
      },
    },
    //...
]

This works only if the arrays are sorted in the same way. Also I need to do another map to fix this as I expect.

Do you have any idea on how to do this?

Tags:

2

Answers


  1. 0

    Under the assumption that the ids in 2 arrays are 1 to 1 correspondence(i.e. the id in author will only map to 1 entry in authorDetails Array and there is no missing/duplicate entry), we can first $sortArray to align the entries. Then use $zip and $mergeObjects to combine the array entries.

    db.collection.aggregate([
  {
    "$set": {
      "authors": {
        "$sortArray": {
          "input": "$authors",
          "sortBy": {
            "id": 1
          }
        }
      },
      "authorsDetails": {
        "$sortArray": {
          "input": "$authorsDetails",
          "sortBy": {
            "id": 1
          }
        }
      }
    }
  },
  {
    "$set": {
      "authors": {
        "$zip": {
          "inputs": [
            "$authors",
            "$authorsDetails"
          ]
        }
      }
    }
  },
  {
    "$set": {
      "authors": {
        "$map": {
          "input": "$authors",
          "as": "a",
          "in": {
            "$mergeObjects": [
              {
                $first: "$$a"
              },
              {
                $last: "$$a"
              }
            ]
          }
        }
      }
    }
  },
  {
    $unset: "authorsDetails"
  },
  {
    "$merge": {
      "into": "collection",
      "on": "_id",
      "whenMatched": "replace"
    }
  }
])

    Mongo Playground

    Login or Signup to reply.
  2. 0

    Here is a simple approach where you $concatArrays first , then you can $unwind the new array and $group back together based on authors.id and then $mergeObjects within that $group stage. I had to do another $group stage to keep hold of that title but it does the job. Hopefully someone sees a more efficient way so that it can be improved.

    Key point here is you don’t need to rely on any sorting.

    db.collection.aggregate([
  {
    $project: {
      title: 1,
      authors: {
        $concatArrays: [
          "$authors",
          "$authorsDetails"
        ]
      }
    }
  },
  {
    $unwind: "$authors"
  },
  {
    $group: {
      _id: {
        _id: "$authors.id",
        title: "$title"
      },
      authors: {
        $mergeObjects: "$authors"
      }
    }
  },
  {
    $group: {
      _id: "$_id.title",
      authors: {
        $push: "$authors"
      }
    }
  },
  {
    $set: {
      title: "$_id"
    }
  },
  {
    $unset: [
      "_id",
      "authors.age"
    ]
  }
])

    See HERE for a working example.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search