Referencing JOIN columns within a subquery from an aggregate function

Question

I'm trying to reference columns in JOIN tables of a subquery from an aggregate function, as follows:

WITH my_activities AS 
   (SELECT activities.*, owner.* 
    FROM "activities" 
    INNER JOIN relationships AS owner 
        ON activities.owner_id = owner.person_id) 
SELECT owner_id, json_agg(jsonb_build_object('id', owner.id)) AS data 
from my_activities 
GROUP BY owner_id

This approach results in:

ERROR:  missing FROM-clause entry for table "owner"

I've tried a bunch of approaches to this, with no luck:

Referencing 'my_activities.owner.id' in the json_build_object instead of just 'owner.id' results in the same error.

My goal here is to end up with groups, where each group contains the Activity record and all of the 'relationships' records that are associated with it. I'll then create a FeedActivity record referencing the Activity record, and storing all the related Relationships in a data column.

Ideas?

Answer 1

The cte you use has its own name (my_activities), so the subsequent select statement can only refer to that name. It is no longer possible, through the cte, to refer back to the original tables.

WITH my_activities
AS (
    SELECT activities.*
        , owners.*
    FROM activities
    INNER JOIN relationships AS owners ON activities.owner_id = owners.person_id
    )
SELECT my_activities.owner_id
    , json_agg(jsonb_build_object('id', my_activities.id)) AS data
FROM my_activities
GROUP BY my_activities.owner_id

nb: although "owner" is a non-reserved word in postgres I would avoid using it if for no other reason than to avoid confusion.

The other thing to avoid is over-reliance on those asterix. Way better to literally specify the columns you need - and only the columns you need. e.g.

WITH my_activities
AS (
    SELECT activities.owner_id
        , owners.id
    FROM activities
    INNER JOIN relationships AS owners ON activities.owner_id = owners.person_id
    )
SELECT my_activities.owner_id
    , json_agg(jsonb_build_object('id', my_activities.id)) AS data
FROM my_activities
GROUP BY my_activities.owner_id

However in the query as provided there really is no benefit at all in using a cte, and if you did it in a more traditional manner then you would refer to the original tables:

SELECT activities.owner_id
    , json_agg(jsonb_build_object('id', owners.id)) AS data
FROM activities
INNER JOIN relationships AS owners ON activities.owner_id = owners.person_id
GROUP BY activities.owner_id