CODEWITHSUNDEEP
jsonpostgresqljsonbpg
Lazhar
Lazhar

Reputation: 1458

Merging two joined tables and output as JSON?

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

Uploads

entitiesXuploads

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:

Upvotes: 1

Views: 541

Answers (1)

S-Man
S-Man

Reputation: 23706

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.

Upvotes: 1

Related Questions