How to grouped data from arrays of documents and combine it by field?

I have DB with this structure:

{
    country: 'USA',
    invoices: [
        { client: 'Cliet_1', docNum: '23123j' },
        { client: 'Cliet_2', docNum: '34123j' },
        { client: 'Cliet_1', docNum: '3453412df' }
    ]
},
{
    country: 'Canada',
    invoices: [
        { client: 'Cliet_3', docNum: '23123j' },
        { client: 'Cliet_4', docNum: '34123j' },
        { client: 'Cliet_4', docNum: '3453412df' }
    ]
},
{
    country: 'USA',
    invoices: [
        { client: 'Cliet_1', docNum: '23123j' },
        { client: 'Cliet_2', docNum: '34123j' },
        { client: 'Cliet_5', docNum: '3453412df' }
    ]
}

I can’t figure out how to transform it into it:

[
    { country: 'USA', clients: ['Cliet_1', 'Cliet_2', 'Cliet_5'] },
    { country: 'Canada', clients: ['Cliet_3', 'Cliet_4'] },
]

Seems pretty easy and I could do it by JS but it would be better to use Mongo

You can do it like this:

  • $group - to group documents per country
  • $reduce with $setUnion - to get an array of unique values of clients
db.collection.aggregate([
  {
    "$group": {
      "_id": "$country",
      "invoices": {
        "$addToSet": "$invoices.client"
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "country": "$_id",
      "clients": {
        "$reduce": {
          "input": "$invoices",
          "initialValue": [],
          "in": {
            "$setUnion": [
              "$$value",
              "$$this"
            ]
          }
        }
      }
    }
  }
])

Working example

1 Like

Thank you so much!
It works as I expected. The only thing to do is to replace countries with real ids and take real entities by them.

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.