Возвращать как массив объектов JSON в SQL (Postgres)

У меня есть следующая таблица 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

Я хочу запросить массив объектов { value_three, value_four }, сгруппированный по value_two. value_two должен присутствовать сам по себе в результате. Результат должен выглядеть следующим образом:

 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"}]

Не имеет значения, использует ли он json_agg() или array_agg().

Однако самое лучшее, что я могу сделать, это:

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;

Что возвращает:

 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"}]

С дополнительным ключом value_two в объектах, от которых я бы хотел избавиться. Какой SQL-запрос (Postgres) следует использовать?

Ответы

Ответ 1

json_build_object() в Postgres 9.4 или новее

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;

Руководство:

Создает объект JSON из списка вариационных аргументов. Условно, список аргументов состоит из переменных ключей и значений.

Для Postgres 9.3 и старше

row_to_json() с ROW будет делать трюк:

SELECT value_two
     , json_agg(row_to_json((value_three, value_four))) AS value_four
FROM   mytable
GROUP  BY value_two;

Но вы теряете оригинальные имена столбцов. Приведение к зарегистрированному типу строк позволяет избежать этого. (Тип строки временной таблицы также используется для специальных запросов.)

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;

Или используйте подсегмент вместо выражения ROW. Более подробный, но без ввода типов:

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;

Больше объяснений в ответе Крейга:

SQL Fiddle.