json_build_object - ¿El resultado de retorno de PostgreSQL se configura como matriz JSON?
jsonb (2)
Me gustaría que PostgreSQL devuelva el resultado de una consulta como una matriz JSON. Dado
create table t (a int primary key, b text);
insert into t values (1, ''value1'');
insert into t values (2, ''value2'');
insert into t values (3, ''value3'');
Me gustaría algo similar a
[{"a":1,"b":"value1"},{"a":2,"b":"value2"},{"a":3,"b":"value3"}]
o
{"a":[1,2,3], "b":["value1","value2","value3"]}
(en realidad, sería más útil conocer ambos). He intentado algunas cosas como
select row_to_json(row) from (select * from t) row;
select array_agg(row) from (select * from t) row;
select array_to_string(array_agg(row), '''') from (select * from t) row;
Y siento que estoy cerca, pero no realmente. ¿Debería mirar otra documentación excepto 9.15. Funciones y operadores JSON ?
Por cierto, no estoy seguro de mi idea. ¿Es esta una decisión de diseño habitual? Mi idea es que, por supuesto, podría tomar el resultado (por ejemplo) de la primera de las 3 consultas anteriores y manipularlo ligeramente en la aplicación antes de servirlo al cliente, pero si PostgreSQL puede crear el objeto JSON final directamente, sería más simple, porque todavía no he incluido ninguna dependencia en ninguna biblioteca JSON en mi aplicación.
Prueba esta consulta:
SELECT array_to_json(array_agg(t)) FROM t
El resultado es el siguiente JSON:
[{"a":1,"b":"value1"},{"a":2,"b":"value2"},{"a":3,"b":"value3"}]
Aquí hay un SQLFiddle: http://sqlfiddle.com/#!15/5860d/11/0 . Los resultados de SQLFiddle tienen algo extraño de "Value" / "Type" en un objeto JSON y escapa de la cadena de resultados (que está asignada a "Value" ), pero eso no parece suceder cuando se ejecuta en PostgreSQL simple . Parece ser una peculiaridad de SQLFiddle.
En cuanto a si es un buen diseño o no realmente depende de su aplicación específica. En general, la evaluación comparativa sería la mejor manera de saber si esto funciona para usted en términos de rendimiento. En términos de mantenibilidad, no veo ningún problema en particular. Todo lo contrario. Simplifica el código de su aplicación y significa que hay menos para mantener, al menos en mi opinión. Si PG puede darle exactamente el resultado que necesita de fábrica, la única razón por la que se me ocurre no usarlo sería por consideraciones de rendimiento. No reinventes la rueda y todo.
Editar:
No me di cuenta de que estabas buscando consultas para ambos resultados.
Primero, para su segundo resultado, puede usar:
SELECT row_to_json(r)
FROM (SELECT array_agg(t.a) AS a
, array_agg(t.b) AS b
FROM t
) r
La subconsulta le permite controlar los nombres de las teclas en el objeto JSON resultante. Esto da
{"a":[1,2,3],"b":["value1","value2","value3"]}
SQLFiddle: http://sqlfiddle.com/#!15/5860d/42/0
En segundo lugar, en mi búsqueda, descubrí un par de otras funciones introducidas en 9.3 que debes considerar:
1) json_agg : hace lo que quiere para su primer resultado de la caja.
SELECT json_agg(t) FROM t
SQLFiddle: http://sqlfiddle.com/#!15/5860d/38/0
2) to_json : se puede usar en lugar de array_to_json o row_to_json y da los mismos resultados.
SELECT to_json(array_agg(t)) FROM t
SQLFiddle: http://sqlfiddle.com/#!15/5860d/10/0
También si desea seleccionar el campo de la tabla y agregarlo como una matriz.
SELECT json_agg(json_build_object(''data_a'',a,
''data_b'',b,
)) from t;
El resultado vendrá.
[{''data_a'':1,''data_b'':''value1''}
{''data_a'':2,''data_b'':''value2''}]