Get a value as JSON including all relationship data in PostgreSQL

I need to get all the related data linked to the table and output it as JSON.

Usually, row_to_json() is used, but if use this, the related PK value is output as is.

To solve this, I can write all the column names myself and use json_build_object(), but this method is very inefficient because there are more than 100 columns and the column names are all different for each table.

Article table

Account table

Team table

I want to receive this result

[
  {
    "id": 1,
    "title": "first",
    "writer": {
      "id": 1,
      "name": "a",
      "team": {
        "id": 5,
        "name": "cl",
        "info": "yeah"
      }
    }
  }
  {
    "id": 2,
    "title": "ct",
    "writer": {
      "id": 2,
      "name": "b",
      "team": {
        "id": 6,
        "name": "ed",
        "info": "yes"
      }
    }
  }
]

Sometimes there are where conditions for data that are related.

select *
from account
where team.id = 5
left join account.team = team.id

It is more complicated when there is account information in the article table and I need to get team information from the account table and do where.

The goal is to reflect the data in the relation to the json without writing all the column names, but the data in the relation should go in the place of the PK data.
This cannot be done with json_agg, json_build_object, or row_to_json().

I can't find how to do this in PostgreSQL 17 docs.

You can use PL/pgSQL to:

• handle recursivity easily
• get an EXECUTE dynamically access foreign tables
• benefit from caching (by tagging your function STABLE)
• fetch your objects in one pass with row_to_json or to_jsonb, then rework only the FK fields on this object before returning it

create or replace function rjson(tname text, id bigint)
returns jsonb stable
language plpgsql
as
$$
    declare
        r jsonb;
        subr jsonb;
        eign record;
    begin
        -- TO DO: prevent infinite recursivity.
        execute format('select to_jsonb(t) from %s t where id = %s', tname, id) into r;
        -- Loop over the fields to expand.
        -- Here we use a declarative table of foreign keys,
        -- but if confident that the FKs declared on the database do not go too deep in bringing up the whole database, one could directly introspect pg_constraint instead.
        for eign in select * from fk where fk.table_name = tname
        loop
            select rjson(eign.to_table, (r->>eign.from_col)::bigint) into subr;
            r = r||jsonb_build_object(eign.from_col, subr);
        end loop;
        return r;
    end;
$$;

select rjson('article', id) from article;

Here is it running in a fiddle.