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

У меня есть таблица 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, которое в любом случае точно такое же, как имя столбца.

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


postgresql json sql jsonb postgresql-json
person schizofrenic_bit    schedule 02.04.2018    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Вы можете использовать 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 здесь.

person Michel Milezzi    schedule 02.04.2018
comment
Но это не включает порядок в столбце c. Простое добавление ORDER BY c в CTE tmp или в подзапрос неверно, так как вам нужно упорядочить во внешнем запросе. Поэтому вам нужно добавить c к проекции в CTE или подзапросе. Но тогда вы возвращаетесь к исходному вопросу ... - person schizofrenic_bit; 02.04.2018
comment
Зачем нужен заказ по внешнему запросу? - person Michel Milezzi; 02.04.2018
comment
См. Здесь: stackoverflow.com/questions/16248813/ - person schizofrenic_bit; 02.04.2018
comment
В этом случае аномалия связана с предложением LIMIT. - person Michel Milezzi; 02.04.2018
comment
Я не эксперт по SQL, но я всегда понимал, что упорядочение подзапроса / CTE не гарантируется для сохранения во внешнем запросе по спецификации SQL. - person schizofrenic_bit; 02.04.2018
comment
Действительно. Но поскольку вы не фильтруете ни одну из групп во внешнем запросе, порядок запроса, вероятно, останется таким же, как и из внутреннего. - person Michel Milezzi; 02.04.2018
comment
Вероятно? Это опасно. Я думаю, что с введением параллельного выполнения запросов в последних версиях PostgreSQL это может быстро укусить вас. - person schizofrenic_bit; 02.04.2018
comment
Ваше обновление - хороший подход! Я буду использовать это. Спасибо за ваши идеи, очень признательны. - person schizofrenic_bit; 03.04.2018
comment
Рад помочь вам @schizofrenic_bit - person Michel Milezzi; 03.04.2018

arrow_upward
0
arrow_downward

выполните заказ в подзапросе или 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
person Haleemur Ali    schedule 02.04.2018
comment
Я не думаю, что это правильно. Насколько мне известно, ORDER BY должен выполняться во внешнем запросе. - person schizofrenic_bit; 02.04.2018
comment
не могли бы вы привести мне встречный пример, в котором этот запрос возвращает результат, отличный от вашего. Я попробовал использовать следующие тестовые данные и получил точно такой же результат: sqlfiddle.com/#!17/ 17d54c / 5 - person Haleemur Ali; 02.04.2018
comment
Я не эксперт по SQL, но я всегда понимал, что упорядочение подзапроса / CTE не гарантируется сохранением во внешнем запросе по спецификации SQL. Так что выводить поведение из специальной скрипки опасно / сложно. - person schizofrenic_bit; 02.04.2018
comment
см. обновленный ответ для альтернативного решения, которое не зависит от порядка подзапросов - person Haleemur Ali; 02.04.2018
comment
Это творческая конструкция! Но я не думаю, что это желаемое решение с точки зрения производительности. - person schizofrenic_bit; 02.04.2018
comment
@schizofrenic_bit: вы правильно сделали заказ. Цитата из руководства В качестве альтернативы, предоставив ввод значения из отсортированного подзапроса обычно работают .... Помните, что этот подход может потерпеть неудачу, если внешний уровень запроса содержит дополнительную обработку, поэтому единственный гарантированный способ получить определенный порядок сортировки - это использовать на order by внутри агрегатной функции - person a_horse_with_no_name; 02.04.2018
comment
@schizofrenic_bit, вы заявили, что были wondering if there is a more elegant way to do this. Просто любопытно, как мое второе решение работает по сравнению с SELECT COALESCE(JSON_AGG(JSON_BUILD_OBJECT('a', a, 'b', b) ORDER BY c), '[]'::json) FROM t - person Haleemur Ali; 03.04.2018
comment
@HaleemurAli Создание JSONB для вывода JSON кажется мне слишком накладными расходами. Нет смысла создавать JSONB, если мы его не используем. - person schizofrenic_bit; 03.04.2018