使用PostgreSQL的array_to_json直接生成JSON是否为不良实践？

你的这种直接从PostgreSQL查询构建JSON结构的做法非常实用，尤其适合快速交付的Web应用，思路完全没问题，但可以从写法优化、场景适配等角度进一步完善。

优点 #

• 减少前端工作量：直接拿到可用的JSON结构，省去JavaScript中转换视图模型的代码，大幅提升开发效率，正如你提到的节省时间。
• 降低数据传输成本：只返回业务需要的字段和结构，避免传输冗余数据，减少带宽消耗。
• 利用数据库聚合能力：复杂的关联、聚合逻辑在数据库层完成，比前端处理更高效，尤其是数据量较大时。

需要注意的问题 #

• 前后端耦合：数据库函数直接绑定了前端的JSON结构，后续前端需求变更时，必须修改数据库代码，增加了前后端的依赖度。
• 调试复杂度：多层嵌套的JSON构建语句比普通SQL更难调试，出现结构错误时定位问题会更麻烦。
• 性能瓶颈：如果嵌套层级极深或数据量极大，ROW_TO_JSON+ARRAY_AGG的组合可能引发性能问题，需要关注索引和执行计划。

优化后的SQL写法 #

你的原SQL存在冗余查询（如重复扫描users表计算total），可以简化为更直观的版本：

方式1：使用json_build_object构建顶层结构 #

CREATE OR REPLACE FUNCTION get_data(_team_id UUID) RETURNS JSON AS
$$
BEGIN
    RETURN json_build_object(
        'total', (SELECT COUNT(*) FROM users WHERE active IS TRUE AND team_id = _team_id),
        'data', (
            SELECT ARRAY_TO_JSON(ARRAY_AGG(json_build_object(
                'id', id,
                'name', name,
                'emails', (SELECT ARRAY_TO_JSON(ARRAY_AGG(json_build_object('email', email))) FROM emails WHERE user_id = users.id)
            )))
            FROM users
            WHERE active IS TRUE AND team_id = _team_id
            ORDER BY name
            LIMIT 5 OFFSET 0
        )
    );
END;
$$ LANGUAGE plpgsql;

方式2：简化嵌套子查询的ROW_TO_JSON写法 #

CREATE OR REPLACE FUNCTION get_data(_team_id UUID) RETURNS JSON AS
$$
BEGIN
    RETURN (
        SELECT ROW_TO_JSON(result)
        FROM (
            SELECT
                (SELECT COUNT(*) FROM users WHERE active IS TRUE AND team_id = _team_id) AS total,
                (
                    SELECT ARRAY_TO_JSON(ARRAY_AGG(ROW_TO_JSON(user_row)))
                    FROM (
                        SELECT
                            id,
                            name,
                            (SELECT ARRAY_TO_JSON(ARRAY_AGG(ROW_TO_JSON(email_row)))
                             FROM (SELECT email FROM emails WHERE user_id = users.id) email_row) AS emails
                        FROM users
                        WHERE active IS TRUE AND team_id = _team_id
                        ORDER BY name
                        LIMIT 5 OFFSET 0
                    ) user_row
                ) AS data
        ) result
    );
END;
$$ LANGUAGE plpgsql;

这两种写法都避免了原SQL中的冗余关联，可读性更强，执行效率也更高。

适用场景与建议 #

• 中小规模/快速迭代应用：你的方案完全适用，能最大化开发效率，不用过度担心耦合问题。
• 大型/高并发应用：可以考虑折中方案——数据库返回结构化的关系数据，后端服务（如Node.js）负责转换为JSON结构，解耦前后端，但会增加后端开发成本。
• 性能优化：务必给users(team_id, active)、emails(user_id)建立索引，避免全表扫描；用EXPLAIN ANALYZE分析查询执行计划，排查潜在性能瓶颈。

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