Postgres JOIN child as JSON

Question

我有一个节点服务器通过npm包访问postgres数据库，pg，并有一个返回数据的工作查询，但我认为它可以被优化。数据模型由版本和功能组成，一个版本有许多子功能。这种查询模式在我的应用程序的一些上下文中工作，但它看起来笨拙。有没有更干净的方法?

SELECT 
v.*,
coalesce(
(SELECT array_to_json(array_agg(row_to_json(x))) FROM (select f.* from app_feature f where f.version = v.id) x ), 
'[]'
) as features FROM app_version v

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');

Answer 1

FROM子句中的子查询可能不需要。

select v.*,
coalesce((select array_to_json(array_agg(row_to_json(f))) 
from app_feature f 
where f.version = v.id), '[]') as features 
from app_version v;

Answer 2

还有我的5美分。请注意，id是app_version的主键，因此可以仅按app_version.id分组。

select v.*, coalesce(json_agg(to_json(f)), '[]') as features 
from app_version v join app_feature f on f.version = v.id
group by v.id;

Answer 3

您可以将JSON聚合移动到视图中，然后连接到视图:

create view app_features_json
as
select af.version,
json_agg(row_to_json(af)) as features
from app_feature af
group by af.version;

在join中使用该视图:

SELECT v.*,
fj.features 
FROM app_version v
join app_features_json afj on afj.version = v.id