Postgres -- dump out a representation of the public schema in json format

postgres-schema-json

with my_columns as 
  (select table_name,array_agg(json_build_object('column_name', column_name, 'is_nullable', is_nullable, 'data_type', data_type, 'ordinal_position', ordinal_position)) cols 
  from information_schema.columns 
  where table_schema='public' 
  group by table_name),
my_fks as 
  (select distinct r.constraint_name,
         k.table_name from_table,
         c.table_name to_table
  from information_schema.key_column_usage k
  join information_schema.referential_constraints r
  on r.constraint_catalog = k.constraint_catalog and r.constraint_schema = k.constraint_schema and r.constraint_name = k.constraint_name
  join information_schema.constraint_column_usage c
  on c.constraint_catalog = r.constraint_catalog and c.constraint_schema = r.constraint_schema and c.constraint_name = r.constraint_name
  where r.constraint_schema='public'),
my_tablerefs as 
  (select k.from_table, json_agg(k.to_table) referenced_tables
  from my_fks k
  group by k.from_table)
select json_build_object('table',c.table_name,'columns',c.cols,'referenced_tables',r.referenced_tables)
from my_columns c left join my_tablerefs r on c.table_name = r.from_table;