Postgres 16

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

select version();

version

PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

SELECT 1

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');

CREATE TABLE

INSERT 0 2

CREATE TABLE

INSERT 0 7

CREATE TABLE

INSERT 0 7

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

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

SELECT 2

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