title: How to apply array_agg/array_to_json to a query with modified columns
body: <p>I am using <code>array_to_json</code> in combination with <code>array_agg</code> to format certain results in PostgreSQL as JSON.  This works fine for queries when I want to return the default value of a query (all columns, unmodified).  But I'm stumped how I could use <code>array_agg</code> to create a JSON object for a query where I want to modify some of the output. </p>
  
  <p>Here's an example:</p>
  
  <pre><code>CREATE TABLE temp_user ( 
     user_id  serial PRIMARY KEY,
     real_name text
  );
  CREATE TABLE temp_user_ip (
     user_id  integer,
     ip_address text
  );
  INSERT INTO temp_user (user_id, real_name) VALUES (1, 'Elise'),  (2, 'John'), (3, NULL);
  INSERT INTO temp_user_ip (user_id, ip_address) VALUES (1, '10.0.0.4'),  (2, '10.0.0.7'), (3, '10.0.0.9');
  </code></pre>
  
  <p>The following query works fine:</p>
  
  <pre><code># SELECT array_to_json(array_agg(temp_user)) as users from temp_user;
                                              users                                                
  -----------------------------------------------------------------------------------------------------
   [{"user_id":1,"real_name":"Elise"},{"user_id":2,"real_name":"John"},{"user_id":3,"real_name":null}]
  </code></pre>
  
  <p>But let's say that I don't like the null value appearing for user 3.  I'd rather see the string "User logged in from $ip" instead.  </p>
  
  <p>I can do this:</p>
  
  <pre><code># 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;
  </code></pre>
  
  <p>And I get the following results:</p>
  
  <pre><code> user_id |             name             
  ---------+------------------------------
         1 | Elise
         2 | John
         3 | User logged in from 10.0.0.9
  </code></pre>
  
  <p>Which is great.  But I can't figure out how to manipulate this data into JSON format like the first example.  </p>
  
  <p>The desired output is:</p>
  
  <pre><code>[{"user_id":1,"name":"Elise"},{"user_id":2,"name":"John"},{"user_id":3,"name":"User logged in from 10.0.0.9"}]
  </code></pre>
  
  <p>This doesn't work:</p>
  
  <pre><code># select array_to_json(array_agg ( (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)));
  ERROR:  subquery must return only one column
  </code></pre>
  
  <p>I can't figure out any way to get the data into a format that <code>array_agg</code> accepts.  I even tried creating a custom type which matched the format of temp_user and trying to <code>array_agg</code> calls to the type constructor, which returned the same error.  The error doesn't make sense to me - if the subquery is aggregated, then it shouldn't matter if it returns more than one column.   Any advice? </p>