Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE comments (
  id INT,
  user_id INT,
  from_id INT, 
  value VARCHAR(255),
  rating INT,
  order_id INT
  );
INSERT INTO comments VALUES
(1  , 1        , 2           , 'Ком'  , 5         , 1),
(2  , 2        , 1           , 'Ком2' , 2         , 1),
(3  , 3        , 4           , 'Ком3' , 5         , 2),
(4  , 4        , 3           , 'Ком4' , 2         , 2);
SELECT * FROM comments;

CREATE TABLE users (id INT, name VARCHAR(255));
INSERT INTO users VALUES
(1 , 'Никита'),
(2 , 'Владимир'),
(3 , 'Влад'),
(4 , 'Андрей');
SELECT * FROM users;

CREATE TABLE



INSERT 0 4




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



SELECT 4



CREATE TABLE



INSERT 0 4




id
name


1
Никита

2
Владимир

3
Влад

4
Андрей



SELECT 4


SELECT users.name, 
       comments.value,
       comments.rating,
       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;


name
value
rating
order_id
num


Никита
Ком
5
1
comment1

Владимир
Ком2
2
1
comment2

Влад
Ком3
5
2
comment1

Андрей
Ком4
2
2
comment2



SELECT 4


WITH
cte 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
) 
SELECT jsonb_build_object('order_id', order_id) ||
       jsonb_object_agg(num, single_user) single_order
FROM cte
GROUP BY order_id;


single_order


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

{"comment1": {"name": "Влад", "value": "Ком3", "rating": 5}, "comment2": {"name": "Андрей", "value": "Ком4", "rating": 2}, "order_id": 2}



SELECT 2


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;


final_data


[{"comment1": {"name": "Никита", "value": "Ком", "rating": 5}, "comment2": {"name": "Владимир", "value": "Ком2", "rating": 2}, "order_id": 1}, {"comment1": {"name": "Влад", "value": "Ком3", "rating": 5}, "comment2": {"name": "Андрей", "value": "Ком4", "rating": 2}, "order_id": 2}]



SELECT 1

name	value	rating	order_id	num
Никита	Ком	5	1	comment1
Владимир	Ком2	2	1	comment2
Влад	Ком3	5	2	comment1
Андрей	Ком4	2	2	comment2