Как сгруппировать ответ?

Question

[WAYNEDEV]

Есть таблица комментариев:

id | user_id | from_id | value | rating | order_id
1  | 1        | 2           | Ком   | 5         | 1
2  | 2        | 1           | Ком2 | 2         | 1
3  | 3        | 4           | Ком3 | 5         | 2
4  | 4        | 3           | Ком4 | 2         | 2

Есть таблица юзеров:
id | name
1 | Никита
2 | Владимир
3 | Влад
4 | Андрей

Как сгруппировать в такой ответ?

[
  {  
      order_id: 1, 
      comment1: { 
         name: 'Никита',
         value: 'Ком',
         rating: 5,
      },
      comment2: { 
         name: 'Владимир',
         value: 'Ком2',
         rating: 2,
      },
   }
]

Помогите пожалуйста составить правильный запрос для такого ответа, буду очень благодарен!

Comments

[Rsa97]

Зависит от сервера, который вы используете. Для MySQL будет одно, для MS SQL другое, для PostgreSQL третье.

[WAYNEDEV]

Rsa97, postgresql

[Akina]

Агрегация в JSON с группировкой по order_id в CTE, затем агрегация в окончательную форму в основном запросе.

Answer 1

[Akina]

WITH
cte1 AS (
  SELECT jsonb_build_object('name',   users.name, 
                           'value',  comments.value,
                           'rating', comments.rating) single_user,
         comments.order_id,
         'comment' || ROW_NUMBER() OVER (PARTITION BY comments.order_id ORDER BY comments.id) num
  FROM users
  JOIN comments ON users.id = comments.user_id
),
cte2 AS (
  SELECT jsonb_build_object('order_id', order_id) ||
         jsonb_object_agg(num, single_user) single_order
  FROM cte1
  GROUP BY order_id
)
SELECT jsonb_agg(single_order) final_data
FROM cte2;

DEMO fiddle