CODEWITHSUNDEEP
mysqlgroup-by
Saef Myth
Saef Myth

Reputation: 297

Mysql group by multiple columns with different values

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?

Upvotes: 0

Views: 2588

Answers (4)

Sebastian Landwehr
Sebastian Landwehr

Reputation: 399

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522752

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;

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133400

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

Upvotes: 0

Ravi
Ravi

Reputation: 31437

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.

Upvotes: 0

Related Questions