Postgres JOIN child as JSON-CodePudding

i have a node server accessing a postgres database through a npm package, pg, and have a working query that returns the data, but i think it may be able to be optimized. the data model is of versions and features, one version has many feature children. This query pattern works in a few contexts for my app, but it looks clumsy. is there a cleaner way?

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

CodePudding user response：

You could move the JSON aggregation into a view, then join to the view:

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;

The use that view in a join:

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

CodePudding user response：

The subquery in FROM clause may not be needed.

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;

CodePudding user response：

And my 5 cents. Pls. note that id is primary key of app_version so it's possible to group by app_version.id only.

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;