Devuelve múltiples columnas de la misma fila como matriz JSON de objetos

Resuelto ehmicky asked hace 9 años • 3 respuestas

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 serie de objetos { value_three, value_four }agrupados por value_two. value_twodebe estar presente por sí 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 una value_twoclave extra en los objetos de los que me gustaría deshacerme. ¿Qué consulta SQL (Postgres) debo utilizar?

ehmicky avatar Oct 21 '14 19:10 ehmicky
Aceptado

json_build_object()en Postgres 9.4 o más reciente

O jsonb_build_object()para volver jsonb.

SELECT val2, json_agg(json_build_object('val3', val3
                                      , 'val4', val4)) AS val4
FROM   tbl 
GROUP  BY val2;

El manual:

Crea un objeto JSON a partir de una lista de argumentos variada. Por convención, la lista de argumentos consta de claves y valores alternos.

Para cualquier versión (incluido Postgres 9.3)

to_json()(o to_jsonb) con una ROWexpresión funciona. (O row_to_json()con saltos de línea opcionales):

SELECT val2, json_agg(to_json((val3, val4))) AS val4
FROM   tbl
GROUP  BY val2;

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

CREATE TYPE foo AS (val3 text, val4 text);  -- once in the same session

SELECT val2, json_agg(to_json((val3, val4)::foo)) AS val4
FROM   tbl
GROUP  BY val2;

O utilice una subselección en lugar de la ROWexpresión. Más detallado, pero sin conversión de tipos:

SELECT val2, json_agg(to_json((SELECT t FROM (SELECT val3, val4) t))) AS val4
FROM   tbl
GROUP  BY val2;

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

  • Usando row_to_json() con uniones anidadas

violín
viejo sqlfiddle

Erwin Brandstetter avatar Oct 21 '2014 12:10 Erwin Brandstetter