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?

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

2 75
postgresql json

2 ответа:

попробуйте выполнить этот запрос:

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 может дать вам именно тот результат, который вам нужен из коробки, единственная причина, по которой я могу не использовать его, - это соображения производительности. Не изобретайте заново колесо и все такое.

Edit:

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

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

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

143

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

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'}]
9