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?

Your expected output looks off to me. Shouldn't you be expecting JSON arrays? E.g. [10, 11] ? — Tim Biegeleisen
– Tim Biegeleisen, Commented Jan 27, 2018 at 8:20

Tim Biegeleisen · Accepted Answer · 2018-01-27 09:20:28Z

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;

edited Jan 27, 2018 at 9:20

answered Jan 27, 2018 at 8:24

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ravi · Accepted Answer · 2018-01-27 08:14:07Z

0

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.

answered Jan 27, 2018 at 8:14

Ravi

31.6k44 gold badges124 silver badges180 bronze badges

Comments

ScaisEdge · Accepted Answer · 2018-01-27 08:14:52Z

0

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

answered Jan 27, 2018 at 8:14

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Comments

Sebastian Landwehr · Accepted Answer · 2024-01-17 22:05:40Z

0

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 }].

answered Jan 17, 2024 at 22:05

Sebastian Landwehr

4092 silver badges8 bronze badges

Collectives™ on Stack Overflow

Mysql group by multiple columns with different values

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related