current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.
up vote 2 down vote favorite
1

I have the following table MyTable:

 id │ value_two │ value_three │ value_four 
────┼───────────┼─────────────┼────────────
  1 │ a         │ A           │ AA
  2 │ a         │ A2          │ AA2
  3 │ b         │ A3          │ AA3
  4 │ a         │ A4          │ AA4
  5 │ b         │ A5          │ AA5

I want to query an array of objects { value_three, value_four } grouped by value_two. value_two should be present on its own in the result. The result should look like this:

 value_two │                                                                                    value_four                                                                                 
───────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 a         │ [{"value_three":"A","value_four":"AA"}, {"value_three":"A2","value_four":"AA2"}, {"value_three":"A4","value_four":"AA4"}]
 b         │ [{"value_three":"A3","value_four":"AA3"}, {"value_three":"A5","value_four":"AA5"}]

It does not matter whether it uses json_agg() or array_agg().

However the best I can do is:

with MyCTE as ( select value_two, value_three, value_four from MyTable ) select value_two, json_agg(row_to_json(MyCTE)) value_four from MyCTE group by value_two;

Which returns:

 value_two │                                                                                    value_four                                                                                 
───────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 a         │ [{"value_two":"a","value_three":"A","value_four":"AA"}, {"value_two":"a","value_three":"A2","value_four":"AA2"}, {"value_two":"a","value_three":"A4","value_four":"AA4"}]
 b         │ [{"value_two":"b","value_three":"A3","value_four":"AA3"}, {"value_two":"b","value_three":"A5","value_four":"AA5"}]

With an extra value_two key in the objects, which I would like to get rid of. Which SQL (Postgres) query should I use?

share|improve this question

1 Answer 1

up vote 5 down vote accepted

row_to_json() with a ROW expression would do the trick:

SELECT value_two
     , json_agg(row_to_json((value_three, value_four))) AS value_four
FROM   MyTable 
GROUP  BY value_two;

You loose original column names, though. Casting to a well-known composite type would avoid that. (The row type of a TEMP TABLE serves, too!) 

CREATE TYPE foo AS (value_three text, value_four text);  -- once

SELECT value_two
     , json_agg(row_to_json((value_three, value_four)::foo)) AS value_four
FROM   tbl
GROUP  BY value_two;

Or use a subselect instead of the ROW expression. More verbose, but without type cast:

SELECT value_two
     , json_agg(row_to_json(
          (SELECT t FROM (SELECT value_three, value_four) 
                           AS t(value_three, value_four))
       )) AS value_four
FROM   tbl
GROUP  BY value_two;

Alternative syntax:

(SELECT t FROM (SELECT value_three AS value_three, value_four AS value_four) t)

More explanation in Craig's related answer:

SQL Fiddle.

json_build_object() in Postgres 9.4

You will love this new function in the upcoming version 9.4. Then it works like:

SELECT value_two, json_agg(
        json_build_object('value_three', value_three
                       , 'value_four' , value_four)
               ) AS value_four
FROM   MyTable 
GROUP  BY value_two;

Quoting the release notes:

  • Add new JSON functions to allow for the construction of arbitrarily complex JSON trees (Andrew Dunstan, Laurence Rowe)

    New functions include json_array_elements_text(), json_build_array(), json_object(), json_object_agg(), json_to_record(), and json_to_recordset().

share|improve this answer
    
Thanks, but this returns the object with automatically generated keys: {"f1":"a","f2":"AA"}. How to rename f1 to value_three? – ehmicky Oct 21 '14 at 12:41
    
@ehmicky: Right, if you want the column names, too, you need to cast the row to a well-known composite type. I'll add some more. – Erwin Brandstetter Oct 21 '14 at 12:42
1  
A sub-select, or even OP's cte could be a more convenient way for aliasing the row's column names. stackoverflow.com/questions/13227142/… – pozs Oct 21 '14 at 12:52
    
@pozs: Good point (and link), I added a code variant accordingly. – Erwin Brandstetter Oct 21 '14 at 13:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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