PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How can I limit number of items in array that comes from inner join?

I have the next query, what returns me categories with an array of games that assignment to them. How can I limit number of games in that array?

SELECT categories.*, COALESCE(jsonb_agg(g.*) FILTER (WHERE g.name IS NOT NULL), '[]') AS games FROM categories
    LEFT JOIN games_categories gc ON categories.id = gc.category_id
    LEFT JOIN games g ON gc.game_id = g.id
    GROUP BY categories.id;
Tags:

2

Answers


  1. 0

    This can be done using the window function ROW_NUMBER() to assign a unique number to each row in a group, and then selecting only the top N rows from each group :

    -- This is to get 2 games per category : 
SELECT category_id, COALESCE(jsonb_agg(s.*) FILTER (WHERE name IS NOT NULL), '[]') AS games
FROM (
  SELECT c.id as category_id, g.*, ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY gc.game_id) as rn
  FROM categories c
  LEFT JOIN games_categories gc ON c.id = gc.category_id
  LEFT JOIN games g ON gc.game_id = g.id
) AS s
WHERE rn <=2
GROUP BY category_id

    For this data set :

    create table categories (
  id int,
  name varchar(10)
);

insert into categories values
(1, 'CAT 1'),
(2, 'CAT 2');

create table games_categories  (
  category_id int,
  game_id int
);

insert into games_categories values
(1, 10),(1, 12),(1, 13),(1, 14),(2, 15),(2, 16),(2, 17);

create table games  (
  id  int,
  name varchar(10)
);

insert into games  values
(10, 'A'),(12, 'B'),(13, 'C'),(14, 'D'),(15, 'E'),(16, 'F'),(17, 'G');

    Results :

    category_id games
1   [{"id": 10, "rn": 1, "name": "A", "category_id": 1}, {"id": 12, "rn": 2, "name": "B", "category_id": 1}]
2   [{"id": 15, "rn": 1, "name": "E", "category_id": 2}, {"id": 16, "rn": 2, "name": "F", "category_id": 2}]

    Demo here

    Login or Signup to reply.
  2. 0

    I would suggest like this – row numbering and using the ON condition, number of games limited to 5 in the example:

    SELECT c.*, COALESCE(jsonb_agg(t.*) FILTER (WHERE t.name IS NOT NULL), '[]') AS games 
FROM categories c
LEFT JOIN
(
  SELECT g.*, row_number() OVER (partition by gc.category_id) rn
  FROM games_categories gc
  LEFT JOIN games g ON gc.game_id = g.id
) t 
ON c.id = t.category_id AND t.rn <= 5
GROUP BY c.id;

    You may also add an order by in the window definition in order to control which games to be picked into the array.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search