Принимать сложные параметры запроса в виде JSON - полезно, хранить его в базе - удобно, но работа с ним в рамках SQL-запроса зачастую вызывает затруднения.
Сегодня столкнулся с очередным нетипичным вариантом использования - "перекладыванием" значений из JSON-строк в столбцы примерно такого вида:
SELECT
json_object_agg(js ->> 'language', coalesce(js ->> 'value', ''))
FILTER(WHERE js ->> 'language' = 'ru') ->> 'ru' ru
, json_object_agg(js ->> 'language', coalesce(js ->> 'value', ''))
FILTER(WHERE js ->> 'language' = 'en') ->> 'en' en
, json_object_agg(js ->> 'language', coalesce(js ->> 'value', ''))
FILTER(WHERE js ->> 'language' = 'de') ->> 'de' de
, json_object_agg(js ->> 'language', coalesce(js ->> 'value', ''))
FILTER(WHERE js ->> 'language' = 'fr') ->> 'fr' fr
FROM
(
VALUES
('{"language" : "ru", "value" : "Бухгалтерия"}'::json)
, ('{"language" : "en", "value" : "Accounting"}')
, ('{"language" : "de", "value" : "Buchhaltung"}')
, ('{"language" : "fr", "value" : "Comptabilité"}')
) T(js);
ru | en | de | fr
Бухгалтерия | Accounting | Buchhaltung | Comptabilité
Помимо очевидной многословности (код языка используется дважды в каждой строке) и избыточности (извлекаем заранее известный ключ JSON-объекта, хотя сами же его и собираем), этот код имеет одно неприятное свойство.
Если в исходной выборке окажутся два объекта для одного ключа, произойдет неприятность - значение может просто исчезнуть:
SELECT
json_object_agg(js ->> 'language', coalesce(js ->> 'value', ''))
FILTER(WHERE js ->> 'language' = 'ru') ->> 'ru' ru
FROM
(
VALUES
('{"language" : "ru", "value" : "Бухгалтерия"}'::json)
, ('{"language" : "ru"}')
) T(js);
ru
---
Понятно, что условие значения ключа можно добавить в FILTER
, но тогда дублирования кода становится еще больше:
json_object_agg(js ->> 'language', coalesce(js ->> 'value', ''))
FILTER(WHERE js ->> 'language' = 'ru' AND coalesce(js ->> 'value', '') <> '') ->> 'ru' ru
Чтобы не замусоривать наш код, оставим пока этот недостаток на совести автора оригинального запроса.
А еще в таком варианте на каждую строку входящей выборки будет совершено 2 (условия в фильтре) x 4 (столбца) = 8 обращений по ключу внутрь js
, плюс 4 обращения к "свернутому" json, плюс 2 обращения для каждой записи внутри агрегации...
Все это ни разу не дешево.
Давайте для начала избавимся от обращения к заранее известным ключам "сворачиваемого" JSON. Раз мы точно знаем, что хотим получить, то нам нужно всего лишь использовать first_value
при агрегации.
Увы, такой штатной функции для не-оконных агрегатов нет, поэтому заменим ее на (array_agg(...))[1]
:
SELECT
coalesce((array_agg(js ->> 'value') FILTER(WHERE js ->> 'language' = 'ru'))[1], '') ru
, coalesce((array_agg(js ->> 'value') FILTER(WHERE js ->> 'language' = 'en'))[1], '') en
, coalesce((array_agg(js ->> 'value') FILTER(WHERE js ->> 'language' = 'de'))[1], '') de
, coalesce((array_agg(js ->> 'value') FILTER(WHERE js ->> 'language' = 'fr'))[1], '') fr
FROM
(
VALUES
('{"language" : "ru", "value" : "Бухгалтерия"}'::json)
, ('{"language" : "en", "value" : "Accounting"}')
, ('{"language" : "de", "value" : "Buchhaltung"}')
, ('{"language" : "fr", "value" : "Comptabilité"}')
) T(js);
Как-то все равно кода слишком много, и слишком много обращений к ключам json.
Давайте все-таки перестанем на каждой записи четырежды извлекать и проверять значение ключа language, сделав это всего лишь раз с помощью json_to_record
:
SELECT
coalesce((array_agg(value) FILTER(WHERE language = 'ru'))[1], '') ru
, coalesce((array_agg(value) FILTER(WHERE language = 'en'))[1], '') en
, coalesce((array_agg(value) FILTER(WHERE language = 'de'))[1], '') de
, coalesce((array_agg(value) FILTER(WHERE language = 'fr'))[1], '') fr
FROM
(
VALUES
('{"language" : "ru", "value" : "Бухгалтерия"}'::json)
, ('{"language" : "en", "value" : "Accounting"}')
, ('{"language" : "de", "value" : "Buchhaltung"}')
, ('{"language" : "fr", "value" : "Comptabilité"}')
) T(js)
, json_to_record(js) X(language text, value text);
Нельзя сказать, что у нас получился верх совершенства, но теперь мы легко можем устранить проблему с "пустым" значением, добавив правильную сортировку внутрь агрегации:
SELECT
coalesce(
(
array_agg(value ORDER BY value DESC NULLS LAST) -- сначала непустые значения
FILTER(WHERE language = 'ru') -- фильтр по значению ключа
)[1] -- эмулируем first_value
, ''
) ru
FROM
(
VALUES
('{"language" : "ru", "value" : "Бухгалтерия"}'::json)
, ('{"language" : "ru"}')
) T(js)
, json_to_record(js) X(language text, value text);
Итого, обращение к ключам json у нас теперь лишь одно на каждую строку - внутри json_to_record
, а вместо json-объекта, хранящего еще и ключ, мы обошлись всего лишь массивом значений.
UPD: на основании комментария
Если уж мы уже можем использовать ORDER BY value
- то есть тип значения может ведь быть приведен и не к text
, но лишь бы он был линейно упорядочиваемым, а не каким-нибудь point
- то вместо array_agg[1]
мы можем использовать просто max
:
SELECT
coalesce(max(value) FILTER(WHERE language = 'ru'), '') ru
FROM
(
VALUES
('{"language" : "ru", "value" : "Бухгалтерия"}'::json)
, ('{"language" : "ru"}')
) T(js)
, json_to_record(js) X(language text, value text);