Merging two joined tables and output as JSON?

Question

I want to SELECT entities and an uploads JSON array column containing all of their uploads, along with the cover and profile fields for each upload.

So far, I can get the array of uploads, but I am unable to add the two other fields from the entitiesXuploads table.

A basic representation of my three tables is as such:

Entities

• id (int8)
• name (varchar)

Uploads

• id (uuid)
• title (varchar)
• versions (jsonb)

entitiesXuploads

• entityId (int8)
• uploadId (uuid)
• cover (bool)
• profile (bool)

My query:

SELECT
    e.id,
    e.name,
    COALESCE(jsonb_agg(up) FILTER (WHERE up."id" IS NOT NULL), '[]') as uploads

FROM 
    entities as e
    LEFT JOIN "entitiesXuploads" as exu ON exu."entityId" = e."id"
    LEFT JOIN "uploads" as up ON up."id" = exu."uploadId"

GROUP BY
    e.id,
    e.name

Which returns rows with:

• id (entity id)
• name (entity name)
• uploads (json array of uploads)

Answer 1

demo: db<>fiddle

SELECT 
    e.id,
    e.name,
    jsonb_agg(
        jsonb_build_object(
            'upload_id', upload_id, 
            'title', title, 
            'versions', versions,
            'cover', cover,
            'profile', profile
        )
    ) AS uploads
FROM
    entities e
JOIN entities_uploads eu ON e.id = eu.entity_id
JOIN uploads u ON eu.upload_id = u.id
GROUP BY e.id, e.name

You can use jsonb_build_object() to create the object you are expecting for each record. After that you can group the records using the jsonb_agg() aggregate function to merge these objects into one array.