multiple - postgresql jsonb example

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

Question

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 objetos { 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 claves en los objetos, de las que me gustaría deshacerme. ¿Qué consulta de SQL (Postgres) debería usar?

Answer 1

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 objeto JSON fuera de una lista de argumentos variados. Por convención, la lista de argumentos consiste en alternar claves 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.