PostgreSQL возвращает результат в виде массива JSON?

Я бы хотел, чтобы PostgreSQL возвращал результат запроса как один массив JSON. Учитывая,

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');

Мне хотелось бы что-то похожее на

[{"a":1,"b":"value1"},{"a":2,"b":"value2"},{"a":3,"b":"value3"}]

или

{"a":[1,2,3], "b":["value1","value2","value3"]}

(на самом деле было бы более полезно знать оба). Я пробовал некоторые вещи вроде

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;

И я чувствую, что я рядом, но на самом деле не так. Должен ли я искать другую документацию, кроме 9.15. Функции и операторы JSON?

Кстати, я не уверен в своей идее. Это обычное дизайнерское решение? Мое мышление состоит в том, что я мог бы, конечно, взять результат (например) из первого из указанных выше трех запросов и немного манипулировать им в приложении перед его обслуживанием клиенту, но если PostgreSQL может напрямую создать конечный объект JSON, это было бы проще, потому что я все еще не включал никакой зависимости от какой-либо библиотеки JSON в своем приложении.

Ответ 1

Попробуйте этот запрос:

SELECT array_to_json(array_agg(t)) FROM t

В результате получается следующий JSON:

[{"a":1,"b":"value1"},{"a":2,"b":"value2"},{"a":3,"b":"value3"}]

Здесь SQLFiddle: http://sqlfiddle.com/#!15/5860d/11/0. Результаты SQLFiddle имеют некоторую странную вещь "Value"/"Type", которая происходит в объекте JSON, и она ускользает от строки результата (которая отображается на "Value"), но, похоже, это не происходит при запуске на простой PostgreSQL, Похоже, это какая-то причуда SQLFiddle.

Что касается хорошего дизайна или не зависит от вашего конкретного приложения. В целом, бенчмаркинг был бы лучшим способом сказать, работает ли это для вас с точки зрения производительности. Что касается ремонтопригодности, я не вижу особых проблем. Наоборот. Это упрощает ваш код приложения и означает, что он меньше, по крайней мере, на мой взгляд. Если PG может дать вам именно тот результат, который вам нужен из коробки, единственная причина, по которой я могу думать, что не использовать его, - это соображения производительности. Не изобретайте велосипед и все.

Изменить:

Я не понимал, что вы ищете запросы для обоих результатов.

Во-первых, для вашего второго результата вы можете использовать:

SELECT row_to_json(r)
FROM (SELECT array_agg(t.a) AS a
           , array_agg(t.b) AS b
      FROM t
     ) r

Подзапрос позволяет вам управлять именами ключей в результирующем объекте JSON. Это дает

{"a":[1,2,3],"b":["value1","value2","value3"]}

SQLFiddle: http://sqlfiddle.com/#!15/5860d/42/0

Во-вторых, в моем копании я обнаружил пару других функций, введенных в 9.3, которые вы должны рассмотреть:

1) json_agg: это делает то, что вы хотите для своего первого результата из коробки.

SELECT json_agg(t) FROM t

SQLFiddle: http://sqlfiddle.com/#!15/5860d/38/0

2) to_json: Это можно использовать вместо array_to_json или row_to_json и дает те же результаты.

SELECT to_json(array_agg(t)) FROM t

SQLFiddle: http://sqlfiddle.com/#!15/5860d/10/0

Ответ 2

Также, если вы хотите, чтобы выбранное поле было из таблицы и агрегировано, а затем как массив.

SELECT json_agg(json_build_object('data_a',a,
                                  'data_b',b,
))  from t;

Результат придет.

 [{'data_a':1,'data_b':'value1'}
  {'data_a':2,'data_b':'value2'}]