Search code examples
mysqlgroup-by

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?

Solution

  • 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;