Solución:
TL; DR
SELECT json_agg
para una matriz JSON de objetos, y
SELECT
json_build_object(
'a', json_agg(t.a),
'b', json_agg(t.b)
)
FROM t
para un objeto JSON de matrices.
Lista de objetos
Esta sección describe cómo generar una matriz JSON de objetos, y cada fila se convierte en un solo objeto. El resultado se ve así:
[{"a":1,"b":"value1"},{"a":2,"b":"value2"},{"a":3,"b":"value3"}]
9.3 y más
los json_agg La función produce este resultado fuera de la caja. Automáticamente descubre cómo convertir su entrada en JSON y la agrega en una matriz.
SELECT json_agg
No hay jsonb (introducido en 9.4) versión de json_agg. Puede agregar las filas en una matriz y luego convertirlas:
SELECT to_jsonb(array_agg
o combinar json_agg con un yeso:
SELECT json_agg
Mis pruebas sugieren que agregarlos en una matriz primero es un poco más rápido. Sospecho que esto se debe a que el elenco tiene que analizar todo el resultado JSON.
9.2
9.2 no tiene el json_agg o to_json funciones, por lo que debe utilizar la antigua array_to_json:
SELECT array_to_json(array_agg
Opcionalmente, puede incluir un row_to_json llamar en la consulta:
SELECT array_to_json(array_agg(row_to_json
Esto convierte cada fila en un objeto JSON, agrega los objetos JSON como una matriz y luego convierte la matriz en una matriz JSON.
No pude discernir ninguna diferencia de rendimiento significativa entre los dos.
Objeto de listas
Esta sección describe cómo generar un objeto JSON, donde cada clave es una columna en la tabla y cada valor es una matriz de los valores de la columna. Es el resultado que se ve así:
{"a":[1,2,3], "b":["value1","value2","value3"]}
9.5 y más
Podemos aprovechar el json_build_object función:
SELECT
json_build_object(
'a', json_agg(t.a),
'b', json_agg(t.b)
)
FROM t
También puede agregar las columnas, crear una sola fila y luego convertirla en un objeto:
SELECT to_json(r)
FROM (
SELECT
json_agg(t.a) AS a,
json_agg(t.b) AS b
FROM t
) r
Tenga en cuenta que es absolutamente necesario asignar un alias a las matrices para garantizar que el objeto tenga los nombres deseados.
Cuál es más claro es cuestión de opinión. Si usa el json_build_object función, recomiendo encarecidamente poner un par clave / valor en una línea para mejorar la legibilidad.
También podrías usar array_agg en lugar de json_agg, pero mis pruebas indican que json_agg es un poco más rápido.
No hay jsonb versión de la json_build_object función. Puede agregar en una sola fila y convertir:
SELECT to_jsonb(r)
FROM (
SELECT
array_agg(t.a) AS a,
array_agg(t.b) AS b
FROM t
) r
A diferencia de otras consultas para este tipo de resultado, array_agg parece ser un poco más rápido cuando se usa to_jsonb. Sospecho que esto se debe al análisis general y la validación del resultado JSON de json_agg.
O puede usar un elenco explícito:
SELECT
json_build_object(
'a', json_agg(t.a),
'b', json_agg(t.b)
)::jsonb
FROM t
los to_jsonb la versión te permite evitar el yeso y es más rápida, según mis pruebas; de nuevo, sospecho que esto se debe a la sobrecarga de analizar y validar el resultado.
9.4 y 9.3
los json_build_object La función era nueva en 9.5, por lo que debe agregar y convertir a un objeto en versiones anteriores:
SELECT to_json(r)
FROM (
SELECT
json_agg(t.a) AS a,
json_agg(t.b) AS b
FROM t
) r
o
SELECT to_jsonb(r)
FROM (
SELECT
array_agg(t.a) AS a,
array_agg(t.b) AS b
FROM t
) r
dependiendo de si quieres json o jsonb.
(9.3 no tiene jsonb.)
9.2
En 9.2, ni siquiera to_json existe. Debes usar row_to_json:
SELECT row_to_json(r)
FROM (
SELECT
array_agg(t.a) AS a,
array_agg(t.b) AS b
FROM t
) r
Documentación
Busque la documentación de las funciones JSON en funciones JSON.
json_agg está en la página de funciones agregadas.
Diseño
Si el rendimiento es importante, asegúrese de comparar sus consultas con su propio esquema y datos, en lugar de confiar en mis pruebas.
Si es un buen diseño o no, realmente depende de su aplicación específica. En términos de mantenibilidad, no veo ningún problema en particular. Simplifica el código de su aplicación y significa que hay menos que mantener en esa parte de la aplicación. Si PG puede brindarle exactamente el resultado que necesita, la única razón por la que puedo pensar para no usarlo sería por consideraciones de rendimiento. No reinventes la rueda y todo.
Nulos
Las funciones agregadas suelen devolver NULL cuando operan sobre cero filas. Si esta es una posibilidad, es posible que desee utilizar COALESCE para evitarlos. Un par de ejemplos:
SELECT COALESCE(json_agg
O
SELECT to_jsonb(COALESCE(array_agg
Gracias a Hannes Landeholm por señalar esto
Además, si desea un campo seleccionado de la tabla y agregado, entonces como matriz.
SELECT json_agg(json_build_object('data_a',a,
'data_b',b,
)) from t;
El resultado llegará.
[{'data_a':1,'data_b':'value1'}
{'data_a':2,'data_b':'value2'}]