如何巧妙从PostgreSQL数据库Schema生成JSON对象？

当然有！我之前帮同事处理过类似需求，PostgreSQL的系统表+原生JSON函数组合起来完全能搞定，不用额外工具，一条查询就能输出你要的格式。

其实就是利用PostgreSQL自带的系统视图information_schema.columns拿到所有表和列的元数据，再用JSON聚合函数把这些数据拼成你想要的嵌套结构。

这个查询会生成当前public schema下所有表的Schema JSON（你可以按需调整过滤条件）：

SELECT json_build_object(
  'schema', json_agg(
    json_build_object(
      table_name, json_build_object(
        'columns', json_agg(
          json_build_object(column_name, json_build_object('type', data_type))
        )
      )
    )
  )
) AS schema_json
FROM information_schema.columns
WHERE table_schema = 'public' -- 指定要生成Schema的schema，比如public
GROUP BY table_name
ORDER BY table_name;

• information_schema.columns：这是PostgreSQL内置的系统视图，存储了数据库中所有表的列信息，包括表名、列名、数据类型这些核心字段，完全满足需求。
• json_build_object：用来构建单个JSON对象，比如把列名和类型拼成{"id": {"type": "int"}}这种结构。
• json_agg：把多行数据聚合为一个JSON数组，比如把某张表的所有列对象聚合成columns对应的数组。
• GROUP BY table_name：确保每个表的列都被单独聚合，不会和其他表的列混在一起。

如果你需要更灵活的输出，可以调整查询：

• 只生成特定表：在WHERE子句里加AND table_name IN ('books', 'orders')。
• 包含更多列属性：比如是否可为空、默认值，修改内层的json_build_object即可：

json_build_object(
  column_name, json_build_object(
    'type', data_type,
    'nullable', is_nullable = 'YES',
    'default', column_default
  )
)

假设你有个books表，结构是id int, title varchar(255), publish_date date，执行查询后会输出类似这样的结果：

{
  "schema": [
    {
      "books": {
        "columns": [
          {"id": {"type": "integer"}},
          {"title": {"type": "character varying"}},
          {"publish_date": {"type": "date"}}
        ]
      }
    }
  ]
}

内容的提问来源于stack exchange，提问作者throwaway2018