PostgreSQL关联子表返回JSON优化咨询：现有查询能否简化？

你可以用以下几种更简洁的写法实现相同效果，同时提升查询的可读性与性能：

方法1：LEFT JOIN + json_agg 聚合 #

这是最简洁的实现方式，直接通过json_agg函数将关联的功能行转换为JSON数组，避免原查询的多层嵌套转换：

SELECT 
  v.*,
  COALESCE(json_agg(f) FILTER (WHERE f.id IS NOT NULL), '[]'::json) AS features
FROM app_version v
LEFT JOIN app_feature f ON f.version = v.id
GROUP BY v.id;

• json_agg(f)直接将app_feature的行对象聚合为JSON数组
• FILTER (WHERE f.id IS NOT NULL)用于排除版本无关联功能时生成的[null]结果
• COALESCE确保无关联功能时返回空JSON数组[]

方法2：LATERAL子查询 + json_agg #

如果需要对关联的功能数据做更灵活的筛选或字段定制，这种写法会更直观：

SELECT 
  v.*,
  COALESCE(f.features, '[]'::json) AS features
FROM app_version v
LEFT JOIN LATERAL (
  SELECT json_agg(f) AS features
  FROM app_feature f
  WHERE f.version = v.id
  -- 可在此添加额外过滤条件，比如 WHERE f.type = 'New Feature'
) f ON true;

LATERAL子查询允许针对每个版本单独处理其关联的功能数据，扩展性更强。

原数据表结构与测试数据 #

CREATE TABLE app_version(
    id SERIAL PRIMARY KEY,
    major INT NOT NULL,
    mid INT NOT NULL,
    minor INT NOT NULL,
    date DATE,
    description VARCHAR(256),
    status VARCHAR(24)
);

CREATE TABLE app_feature(
    id SERIAL PRIMARY KEY,
    version INT,
    description VARCHAR(256),
    type VARCHAR(24),
    CONSTRAINT FK_app_feature_version FOREIGN KEY(version) REFERENCES app_version(id)
);

INSERT INTO app_version (major, mid, minor, date, description, status) VALUES (0,0,0, current_timestamp, 'initial test', 'PENDING');
INSERT INTO app_feature (version, description, type) VALUES (1, 'store features', 'New Feature');
INSERT INTO app_feature (version, description, type) VALUES (1, 'return features as json', 'New Feature');

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