Devuelve como una matriz de objects JSON en SQL (Postgres)

Tengo la siguiente tabla 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 

Quiero consultar una matriz de objects { value_three, value_four } agrupados por value_two . value_two debe estar presente solo en el resultado. El resultado debería verse así:

  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"}] 

No importa si usa json_agg() o array_agg() .

Sin embargo, lo mejor que puedo hacer es:

 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; 

Que devuelve:

  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"}] 

Con un valor extra, value_two keys en los objects, de las que me gustaría deshacerme. ¿Qué consulta de SQL (Postgres) debería usar?

json_build_object() en Postgres 9.4 o posterior

 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; 

El manual:

Construye un object JSON fuera de una list de arguments variados. Por convención, la list de arguments consiste en alternar keys y valores.

Para Postgres 9.3 o anterior

row_to_json() con una expresión ROW haría el truco:

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

Pero pierdes los nombres originales de las columnas. Un lanzamiento a un tipo de fila registrado lo evita. (El tipo de fila de una tabla temporal también sirve para consultas ad hoc).

 CREATE TYPE foo AS (value_three text, value_four text); -- once in the same session 
 SELECT value_two , json_agg(row_to_json( (value_three, value_four)::foo )) AS value_four FROM mytable GROUP BY value_two; 

O use una subselección en lugar de la expresión ROW . Más detallado, pero sin tipo de molde:

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

Más explicación en la respuesta relacionada de Craig:

  • PostgreSQL 9.2 row_to_json () con uniones anidadas

SQL Fiddle.