How to merge objects inside an array based on a condition?

Try like this:

%dw 2.0
output application/json
---
payload groupBy ($.Unique_ID) pluck $[0] map{
    "countHouses": $.countHouses default 0,
    "countFlats": $.countFlats default 0,
    "countStadiums": $.countStadiums default 0,
    "Name": $.Name,
    "Unique_ID": $.Unique_ID
  }

There might be something wrong with the input payload as not all members contains Unique_ID.

  {
    "countFlats": 1,
    "Name": "Efac",
    "Unique_ID__c": "31124"
  },

I have replaced it with

  {
    "countFlats": 1,
    "Name": "Efac",
    "Unique_ID": "31124"
  },

You can make use of groupby, pluck, reduce and mergeWith to group the array, reduce and merge the resulting array into a single object.

%dw 2.0
import mergeWith from dw::core::Objects
output application/json
---
payload groupBy $.Unique_ID pluck $ map ($ reduce ((item, acc={
    countHouses: 0,
    countFlats: 0,
    countStadiums: 0
}) -> acc mergeWith item))

This will result to:

[
  {
    "countHouses": 1,
    "countFlats": 4,
    "countStadiums": 1,
    "Name": "Bob",
    "Unique_ID": "12345"
  },
  {
    "countStadiums": 5,
    "countFlats": 1,
    "countHouses": 245,
    "Name": "Efac",
    "Unique_ID": "31124"
  },
  {
    "countFlats": 0,
    "countStadiums": 0,
    "countHouses": 300,
    "Name": "Pox",
    "Unique_ID": "18110"
  }
]

try this :

Input :

[
  {
    "countHouses": 1,
    "Name": "Bob",
    "Unique_ID": "12345"
  },
  {
    "countFlats": 4,
    "Name": "Bob",
    "Unique_ID": "12345"
  },
  {
    "countStadiums": 5,
    "Name": "Efac",
    "Unique_ID": "31124"
  },
  {
    "countStadiums": 1,
    "Name": "Bob",
    "Unique_ID": "12345"
  },
  {
    "countFlats": 1,
    "Name": "Efac",
    "Unique_ID": "31124"
  },
  {
    "countHouses": 245,
    "Name": "Efac",
    "Unique_ID": "31124"
  },
  {
    "countHouses": 300,
    "Name": "Pox",
    "Unique_ID": "18110"
  }
]

dataweave script:

%dw 2.0
output application/json
---
payload groupBy ($.Unique_ID) pluck $ map ((item, index) -> {
    "countHouses": item.countHouses[0] default 0,
    "countFlats": item.countFlats[0] default 0,
    "countStadiums": item.countStadiums[0] default 0,
    "Name": item.Name[0],
    "Unique_ID": item.Unique_ID[0]
})

output:

    [
  {
    "countHouses": 1,
    "countFlats": 4,
    "countStadiums": 1,
    "Name": "Bob",
    "Unique_ID": "12345"
  },
  {
    "countHouses": 245,
    "countFlats": 1,
    "countStadiums": 5,
    "Name": "Efac",
    "Unique_ID": "31124"
  },
  {
    "countHouses": 300,
    "countFlats": 0,
    "countStadiums": 0,
    "Name": "Pox",
    "Unique_ID": "18110"
  }
]

enter image description here