How to aggregate distinct items in mongo db to separate array?

Question

I have an API endpoint that checks the mongo db and returns in a following structure for example:

{
  "Name": "Test"
  "Location": "Whatever",
  "customerServices": [
      {
        "id": "test",
        "cusId": "test",
        "serviceAdr": "test",
        "serviceCounty": "West",
        "name": "test"
      },
      {
        "id": "test",
        "cusId": "test",
        "serviceAdr": "test",
        "serviceCounty": "West",
        "name": "test"
      },
      {
        "id": "test",
        "cusId": "test",
        "serviceAdr": "test",
        "serviceCounty": "East",
        "name": "test"
      }
  ]
}

What I want to do is to add aggregation to the pipeline that would create additional field-serviceCounties and include distinct serviceCounty values from the customerServices array. Like the following:

{
  "Name": "Test"
  "Location": "Whatever",
  "customerServices": [
      {
        "id": "test",
        "cusId": "test",
        "serviceAdr": "test",
        "serviceCounty": "West",
        "name": "test"
      },
      {
        "id": "test",
        "cusId": "test",
        "serviceAdr": "test",
        "serviceCounty": "West",
        "name": "test"
      },
      {
        "id": "test",
        "cusId": "test",
        "serviceAdr": "test",
        "serviceCounty": "East",
        "name": "test"
      }
  ],
  "serviceCounties": [
    {
      "countyName":"East"
    },
    {
      "countyName":"West"
    }
] 
}

I have tried the following, but it didn't work. There something I'm missing or doing wrong:

                'serviceCounties': {
                    '$reduce': {
                        'input': '$services.event.services',
                        'initialValue': [],
                        'in': [{
                            "countyName": { '$concatArrays': ["$$value.serviceCounty", "$$this"] }
                        }]
                    }
                }

Any ideas how to do it ?

I don't know mongodb, but in the json format, you can parse it to an object and process it then push back to mongodb? Otherwise, you might check mongodb query too. — Alfred Luu
– Alfred Luu, Commented Jan 20, 2021 at 6:15
In my case I need mongo aggregation specifically, but thanks — D_Asti
– D_Asti, Commented Jan 20, 2021 at 6:22

Pinky Promise · Accepted Answer · 2021-01-20 06:54:08Z

1

You could try this.

db.collection.aggregate([
  {
    $addFields: {
      "serviceCounties": [
        {
          $map: {
            input: "$customerServices",
            in: {
              "countyName": "$$this.serviceCounty"
            }
          }
        }
      ]
    }
  }
])

Playground

$addfields to add serviceCounties array
$map to get the customerServices values.

answered Jan 20, 2021 at 6:54

Pinky Promise

2354 silver badges24 bronze badges

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

Collectives™ on Stack Overflow

How to aggregate distinct items in mongo db to separate array?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related