How to apply array_agg/array_to_json to a query with modified columns

PostgreSQL now has a function json_agg that can be used in lieu of array_to_json(array_agg( ... )) but actually behaves better in some cases. See "Array_agg in postgres selectively quotes" and the documentation: "Aggregate Functions".

Here is the modified query:

SELECT 
    json_agg(row(t.*)) AS users 
FROM 
    (
        SELECT user_id, 
            CASE 
                WHEN real_name IS NULL 
                THEN (
                    SELECT 'User logged in from ' || ip_address 
                    FROM temp_user_ip 
                    WHERE user_id = temp_user.user_id
                ) ELSE real_name 
            END AS name 
        FROM temp_user
    ) t
;

You can separate the aggregate call from the subquery and use the row constructor for generating the compound data:

SELECT 
    array_to_json(array_agg(row(t.*))) AS users 
FROM 
    (
        SELECT user_id, 
            CASE 
                WHEN real_name IS NULL 
                THEN (
                    SELECT 'User logged in from ' || ip_address 
                    FROM temp_user_ip 
                    WHERE user_id = temp_user.user_id
                ) ELSE real_name 
            END AS name 
        FROM temp_user
    ) t
;

You can also check this on SQLFiddle.