Как использовать подмножество столбцов строки при преобразовании в JSON?

Question

У меня есть стол t с некоторыми столбцами a, b а также c, Я использую следующий запрос для преобразования строк в массив объектов JSON:

SELECT COALESCE(JSON_AGG(t ORDER BY c), '[]'::json)
FROM   t

Это возвращает как ожидалось:

[
  {
    "a": ...,
    "b": ...,
    "c": ...
  },
  {
    "a": ...,
    "b": ...,
    "c": ...
  }
]

Теперь я хочу тот же результат, но только с колонками a а также b на выходе. Я все еще буду использовать колонку c для заказа. Лучшее, что я придумал:

SELECT COALESCE(JSON_AGG(JSON_BUILD_OBJECT('a', a, 'b', b) ORDER BY c), '[]'::json)
FROM   t

[
  {
    "a": ...,
    "b": ...
  },
  {
    "a": ...,
    "b": ...
  }
]

Хотя это прекрасно работает, мне интересно, есть ли более элегантный способ сделать это. Это расстраивает меня, что я должен вручную определить свойства JSON. Конечно, я понимаю, что я должен перечислить столбцы a а также b, но странно, что мне нужно скопировать / вставить соответствующее имя свойства JSON, которое в любом случае точно совпадает с именем столбца.

Есть ли другой способ сделать это?

Answer 1

Вы можете использовать row_to_json вместо создания объекта вручную:

CREATE TABLE foobar (a text, b text, c text);

INSERT INTO foobar VALUES 
    ('1', 'LOREM', 'A'),
    ('2', 'LOREM', 'B'),
    ('3', 'LOREM', 'C');

--Using CTE
WITH tmp AS (
    SELECT a, b FROM foobar ORDER BY c
)
SELECT json_agg(row_to_json(t)) FROM tmp t

--Using subquery
SELECT 
    json_agg(row_to_json(t)) 
FROM 
    (SELECT a, b FROM foobar ORDER BY c) t;

РЕДАКТИРОВАТЬ: Как вы заявили, порядок следования является строгим требованием. В этом случае вы можете использовать конструктор строки для создания объекта json:

--Using a type to build json with desired keys
CREATE TYPE mytype AS (a text, b text);

SELECT 
    json_agg(
        to_json(
            CAST(
                ROW(a, b) AS mytype
            ) 
        )
    ORDER BY c) 
FROM 
    foobar;

--Ignoring column names...    
SELECT 
    json_agg(
        to_json(
            ROW(a, b) 
        )
    ORDER BY c) 
FROM 
    foobar;

SQL Fiddle здесь.

Answer 2

Выполнить заказ в подзапросе или cte, а затем применить json_agg

SELECT COALESCE(JSON_AGG(t2), '[]'::json)
FROM (SELECT a, b FROM t ORDER BY c) t2

В качестве альтернативы используйте JSONB. Тип jsonb позволяет удалять элементы, указав их ключ

SELECT coalesce(jsonb_agg(row_to_json(t)::jsonb - 'c' 
                          order by c), '[]'::jsonb)
FROM t