how to group data entries with same id into a single entry?-CodePudding

I am a beginner in MySQL as well as Typeorm. So my query returns data with the same ID like:

[
  {
    id: "1",
    name: "john",
    place: "San Francisco"
  },
  {
    id: "1",
    name: "john",
    place: "Mumbai"
  }
]

Now I want data where there is an entry with a unique id, let's say:

[
 {
  id: "1",
  name: "john",
  place: ["San Francisco", "Mumbai"]
  }
]

can someone help me, how do I groupBy to achieve this result?

CodePudding user response：

I doubt that you can get an array, but you could use group_concat. https://mariadb.com/kb/en/group_concat/ The query would be something like

SELECT `id`, group_concat(`name`), group_concat(`place`) FROM <table_name> GROUP BY `id`

if the name doesn't need to be concatenated

SELECT `id`, `name`, group_concat(`place`) FROM <table_name> GROUP BY `id`

And then in your code you can split that string in array. Either use ',' which I think it's the default separator or use a custom one like '!#$!'

CodePudding user response：

With MySQL you can use GROUP_CONCAT:

SELECT 
  id, name, GROUP_CONCAT(place) 
FROM 
  <table_name> 
GROUP BY 
  id

With TypeScript you can use Array.prototype.reduce():

const data = [{id: "1",name: "john",place: "San Francisco"},{id: "1",name: "john",place: "Mumbai"}]

const dataHash = data.reduce((a, { id, name, place }) => {
  a[id] = a[id] || { id, name, place: [] } 
  a[id].place.push(place)
  return a
}, {})
const result = Object.values(dataHash)

console.log(result)