Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE primari (
  id BIGINT NOT NULL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE secondary (
  primary_id BIGINT NOT NULL references primari (id),
  id BIGINT PRIMARY KEY NOT NULL,
  name TEXT NOT NULL
);

CREATE INDEX tertiary_secondary_id_idx ON secondary (primary_id);

CREATE TABLE tertiary (
  primary_id BIGINT NOT NULL references primari (id),
  id BIGINT PRIMARY KEY NOT NULL,
  name TEXT NOT NULL
);

CREATE INDEX tertiary_primary_id_idx ON tertiary (primary_id);

-- Create some very basic data

INSERT INTO primari (id, name) VALUES (1, 'One'), (2, 'Two');
INSERT INTO secondary (primary_id, id, name) VALUES
  (1, 1, 'OneOne'), (1, 2, 'OneTwo'), (1, 3, 'OneThree');
INSERT INTO tertiary (primary_id, id, name) VALUES
  (1, 1, 'OneOne'), (2, 2, 'TwoTwo'), (2, 3, 'TwoThree');

CREATE TABLE



CREATE TABLE



CREATE INDEX



CREATE TABLE



CREATE INDEX



INSERT 0 2



INSERT 0 3



INSERT 0 3


-- first attempt: multisets on the SELECT

SELECT
  primari.*,
  (
    SELECT
      JSONB_PRETTY(COALESCE(
        JSONB_AGG(JSONB_BUILD_OBJECT('id', s.id, 'primary_id', s.primary_id, 'name', s.name)),
        JSONB_BUILD_ARRAY()
      ))
      FROM (SELECT secondary.* FROM secondary WHERE secondary.primary_id = primari.id) AS s
  ) AS secondaries,
  (
    SELECT
      JSONB_PRETTY(COALESCE(
        JSONB_AGG(JSONB_BUILD_OBJECT('id', t.id, 'primary_id', t.primary_id, 'name', t.name)),
        JSONB_BUILD_ARRAY()
      ))
      FROM (SELECT tertiary.* FROM tertiary WHERE tertiary.primary_id = primari.id) AS t
  ) AS tertiaries
FROM primari;


id
name
secondaries
tertiaries


1
One
[
    {
        "id": 1,
        "name": "OneOne",
        "primary_id": 1
    },
    {
        "id": 2,
        "name": "OneTwo",
        "primary_id": 1
    },
    {
        "id": 3,
        "name": "OneThree",
        "primary_id": 1
    }
]
[
    {
        "id": 1,
        "name": "OneOne",
        "primary_id": 1
    }
]

2
Two
[
]
[
    {
        "id": 2,
        "name": "TwoTwo",
        "primary_id": 2
    },
    {
        "id": 3,
        "name": "TwoThree",
        "primary_id": 2
    }
]



SELECT 2


SELECT
  primari.*,
  JSONB_PRETTY(COALESCE(secondary_sq.secondaries, JSONB_BUILD_ARRAY())) AS secondaries,
  JSONB_PRETTY(COALESCE(tertiary_sq.tertiaries, JSONB_BUILD_ARRAY())) AS tertiaries
FROM primari
LEFT OUTER JOIN (
  SELECT
    secondary.primary_id,
    COALESCE(
      JSONB_AGG(JSONB_BUILD_OBJECT('id', id, 'primary_id', primary_id, 'name', name)),
      JSONB_BUILD_ARRAY()
    ) AS secondaries
  FROM secondary
  GROUP BY primary_id
) AS secondary_sq
ON secondary_sq.primary_id = primari.id
LEFT OUTER JOIN (
  SELECT
    tertiary.primary_id,
    COALESCE(
      JSONB_AGG(JSONB_BUILD_OBJECT('id', id, 'primary_id', primary_id, 'name', name)),
      JSONB_BUILD_ARRAY()
    ) AS tertiaries
  FROM tertiary
  GROUP BY primary_id
) AS tertiary_sq
ON tertiary_sq.primary_id = primari.id;


id
name
secondaries
tertiaries


1
One
[
    {
        "id": 1,
        "name": "OneOne",
        "primary_id": 1
    },
    {
        "id": 2,
        "name": "OneTwo",
        "primary_id": 1
    },
    {
        "id": 3,
        "name": "OneThree",
        "primary_id": 1
    }
]
[
    {
        "id": 1,
        "name": "OneOne",
        "primary_id": 1
    }
]

2
Two
[
]
[
    {
        "id": 2,
        "name": "TwoTwo",
        "primary_id": 2
    },
    {
        "id": 3,
        "name": "TwoThree",
        "primary_id": 2
    }
]



SELECT 2
id	name	secondaries	tertiaries
1	One	[ { "id": 1, "name": "OneOne", "primary_id": 1 }, { "id": 2, "name": "OneTwo", "primary_id": 1 }, { "id": 3, "name": "OneThree", "primary_id": 1 } ]	[ { "id": 1, "name": "OneOne", "primary_id": 1 } ]
2	Two	[ ]	[ { "id": 2, "name": "TwoTwo", "primary_id": 2 }, { "id": 3, "name": "TwoThree", "primary_id": 2 } ]