CODEWITHSUNDEEP
pythonmongodbaggregation-frameworkpymongo
D_Asti
D_Asti

Reputation: 69

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

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']}
            ]
        }
    }
},

Upvotes: 3

Views: 1137

Answers (1)

matthPen
matthPen

Reputation: 4363

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

Upvotes: 2

Related Questions