Принимать сложные параметры запроса в виде 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-объекта, хранящего еще и ключ, мы обошлись всего лишь массивом значений.