Mysql group by multiple columns with different values

Question

I have as an example this table

id user_id user_name profile_image post_id
1     10       A         a.png         1
2     11       B         x.png         1
3     12       D         j.png         4
4     13       F         g.png         4

What I want is to group rows by post_id, so that the results will be like the following one:

post_id    user_ids      user_names  profile_images
    1      {10,11}        {A,B}      {a.png,x.ping}

I tried using GROUP BY and JOINs, also json_object.
How can I achieve that?

Answer 1

On MySQL version 5.7.22 or later we can use JSON_ARRAYAGG:

SELECT
    post_id,
    JSON_ARRAYAGG(user_id) AS user_ids,
    JSON_ARRAYAGG(user_name) AS user_names,
    JSON_ARRAYAGG(profile_image) AS profile_images
FROM yourTable
GROUP BY post_id;

Answer 2

Use GROUP_CONCAT.

select 
post_id,
GROUP_CONCAT(user_id) as user_ids,
GROUP_CONCAT(username) as usernames,
GROUP_CONCAT(profile_image) as profile_images 
from <your_table> group by post_id;

Then, you can format the output accordingly.

Answer 3

you can use group_concat for grouping values

  select post_id
      , concat( '{', group_concat(user_id), '}' ) 
      , concat( '{', group_concat(user_name), '}' ) 
      , concat( '{', group_concat(profile_images), '}' ) 
  from  
  group by post_id

and format the result using concat

Answer 4

I managed to get it working using JSON_ARRAYAGG(JSON_OBJECT('field1', value1, 'field2', value2)). Will lead to [{ field1: 1, field2: 2 }, { field1: 3, field2: 4 }].