1

I'm trying to convert an arbitrary subquery to JSON in PostgreSQL.

json_agg() seems like the obvious tool for the job. For example:

select json_agg(a) from (
    select count(lead_source), lead_source from leads_table group by lead_source order by count desc
) as a;

However, data is structured as the_data[row_number]["column_name"]. Suppose I want data structured as the_data["column_name"][row_number]. That is, I want the transpose structure of json_agg(). How do I do that?

My best effort is:

select row_to_json(row(array_to_json(array_agg(count)), array_to_json(array_agg(lead_source)))) from (
    select count(lead_source), lead_source from leads_table group by lead_source order by count desc
) as a;

But this has (at least) two drawbacks:

  1. It doesn't work for an arbitrary subquery. The column names must be hardcoded each time array_agg() is used.
  2. The column names become f1, f2, ...
1

This will take care of the f1,f2,f3 name issue, but unfortunately can't think of an easy way for an arbitrary query.

 SELECT row_to_json(r) from 
      (SELECT array_to_json(array_agg(count)) As count, 
              array_to_json(array_agg(lead_source)) As lead_source
      FROM (
        SELECT count(lead_source), lead_source from leads_table group by lead_source    
          ORDER BY count desc
    ) as a) AS r;

This one is not quite as short but will allow you to to put an arbitrary in your a subquery and output the columns. (so solving both issue 1 and issue 2). It piggy backs on hstore so if you don't have hstore installed, you'll need to do a: CREATE EXTENSION hstore;

SELECT  ('{' || string_agg(sv, ',') || '}')::json
 FROM (SELECT ('"' || (kv).key || '"' || ':' 
   || array_to_json(array_agg((kv).value) ) )   As sv
FROM (SELECT each(hstore(a)) As kv from 
  (
   SELECT count(lead_source), lead_source from leads_table group by lead_source    
      ORDER BY count desc
   ) as a) As r
 GROUP By (kv).key) As r2;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.