How to concatenate multiple string fields into single field using MongoDB Aggregation?

user_10211423 0
Viewed 234

Let's suppose I have an incoming data in the following format:

{
  "Name": "Test"
  "Location": "Whatever",
  "customerServices": [
      {
        "id": "test",
        "cusId": "test",
        "adr": "Adr 1",
        "serviceCounty": "Center",
        "area": "village"
      },
      {
        "id": "test",
        "cusId": "test",
        "adr": "adr2",
        "serviceCounty": "West",
        "area": "city"
      },
      {
        "id": "test",
        "cusId": "test",
        "adr": "test",
        "serviceCounty": "West",
        "area": "test"
      }
  ]
}

Any ideas, how to write an aggregation query which would:

  1. Create a new field called 'serviceAreas'. Type: list
  2. For each item in the 'customerServices'. It would select: adr, serviceCounty and area fields.
  3. Append them together into a single string and add to the newly created serviceAreas field.
  4. It would select and do the operation only for distinct serviceCounty items

So the end result would be like this:

{
  "Name": "Test"
  "Location": "Whatever",
  "customerServices": [
      {
        "id": "test",
        "cusId": "test",
        "adr": "Adr 1",
        "serviceCounty": "Center",
        "area": "village"
      },
      {
        "id": "test",
        "cusId": "test",
        "adr": "adr2",
        "serviceCounty": "West",
        "area": "city"
      },
      {
        "id": "test",
        "cusId": "test",
        "adr": "test",
        "serviceCounty": "West",
        "area": "test"
      }
  ],
  "serviceAreas": [
       "Adr 1, Center, village", "adr2, West, city"
]
}

Any help is appreciated!

Here is what I have tried, but it didn't work out:

'serviceAreas': {
    '$reduce': {
        'input': '$serviceImpactHistory.event.services',
        'initialValue': [],
        'in': {
            '$setUnion': [
                '$$value', {'$concat': ['$$this.serviceCounty', '$$this.adr', '$$this.area']}
            ]
        }
    }
},
aggregation-framework pymongo mongodb python
1 Answers

You don't need to use $setUnion, just create your string with $concat

db.collection.aggregate([
  {
    $addFields: {
      serviceAreas: {
        "$reduce": {
          "input": "$customerServices",
          "initialValue": "",
          "in": {
            "$concat": [
              "$$value",
              "$$this.serviceCounty",
              ", ",
              "$$this.adr",
              ", ",
              "$$this.area",
              ", "
            ]
          }
        }
      }
    }
  }
])

Try it here

user_22768980
Related