PostgreSQL Antipatterns: ходим по JSON-граблям

Недавно попался на глаза примерно следующий кусок запроса:

...
, (((TABLE jsd) -> src.id::text)::jsonb ->> 'Номер')::text "Номер"
, (((TABLE jsd) -> src.id::text)::jsonb ->> 'Дата')::date "Дата"
, (((TABLE jsd) -> src.id::text)::jsonb ->> 'Сумма')::numeric "Сумма"
, replace(
    replace(
      ((TABLE jsd) -> src.id::text)::jsonb ->> 'Флаги')
    , '['
    , '{'
    )
  , ']'
  , '}'
  )::boolean[] "Флаги"
...

Тут прекрасно примерно все:

• множество чтений из CTE (хоть и единственной записи, но все же)

• извлечение по каждому ключу текста с раскастовкой в jsonb

• извлечение каждого отдельного json-ключа в каждое отдельное одноименное поле

• "ручное" преобразование текстового представления массива в jsonb в текстовое представление PostgreSQL

Хорошо, если json[b]-объект у вас относительно невелик, но если его объем к мегабайту, а извлекаемых полей несколько десятков, то одним подобным запросом вы можете увести процесс PostgreSQL в 100% cpu.

Истоки этой проблемы кроются в модели извлечения данных, которую я приводил в статье "PostgreSQL Antipatterns: ударим словарем по тяжелому JOIN" - чтобы не получить множество циклов CTE Scan по каждой из связываемых записей, сложим все содержимое CTE в единый json[b]-объект, откуда будем "мгновенно" извлекать необходимое по ключу, не тратя ресурсы на перебор всех записей.

Как правило, на практике возникает две задачи относительно подобного словаря:

• извлечь только конкретные записи по набору ключей

• извлечь все записи по всем ключам (например, при передаче JSON в качестве параметра запроса)

По набору ключей

Чтобы не дублировать названия ключей и полей, а заодно сразу описать желаемые типы, воспользуемся функцией jsonb_to_record:

WITH jsd AS (
  SELECT $${
    "1" : {"Номер" : 101, "Дата" : "2023-11-01", "Сумма" : 123.45, "Флаги" : [true,false,null]}
  , "2" : {"Номер" : 202, "Дата" : "2023-11-02", "Сумма" : 321.54, "Флаги" : [false,null,true]}
  , "3" : {"Номер" : 303, "Дата" : "2023-11-03", "Сумма" : 100.00, "Флаги" : [null,true,false]}
  }$$::jsonb
)
SELECT
  *
FROM
  unnest(ARRAY[1, 2]) id -- входящий набор ключей
, jsonb_to_record( -- функцию можно звать без LATERAL
    (TABLE jsd) -> id::text
  ) T( -- определяем имена и типы извлекаемых ключей
    "Номер"
      integer
  , "Дата"
      date
  , "Сумма"
      numeric(32,2)
  , "Флаги"
      boolean[]
  );

Заметьте, что нам больше не требуется ни множественно кастовать один и тот же извлекаемый text к jsonb, ни заниматься replace-магией - все это за нас сделает сам PostgreSQL!

По всем ключам объекта

Конечно, можно было бы сначала извлечь все ключи с помощью jsonb_object_keys, а затем "задача сведена к предыдущей". Но есть более изящное решение - перебор сразу всех пар ключ-значение функцией json_each - тогда извлекать "по ключу" отдельно каждое "значение" не потребуется:

WITH jsd AS (
  SELECT $${
    "1" : {"Номер" : 101, "Дата" : "2023-11-01", "Сумма" : 123.45, "Флаги" : [true,false,null]}
  , "2" : {"Номер" : 202, "Дата" : "2023-11-02", "Сумма" : 321.54, "Флаги" : [false,null,true]}
  , "3" : {"Номер" : 303, "Дата" : "2023-11-03", "Сумма" : 100.00, "Флаги" : [null,true,false]}
  }$$::jsonb
)
SELECT
  jskey::integer
, T.*
FROM
  jsonb_each((TABLE jsd)) js(jskey, jsval) -- все пары ключ-значение
, jsonb_to_record(jsval) T(
    "Номер"
      integer
  , "Дата"
      date
  , "Сумма"
      numeric(32,2)
  , "Флаги"
      boolean[]
  );

По всему массиву

Если же мы управляем источником данных и можем изменить формат передаваемого json с единого объекта на массив объектов-записей, то задача вообще решается "в одно действие", и действие это - jsonb_to_recordset:

WITH jsd AS (
  SELECT $$[
    {"id" : 1, "Номер" : 101, "Дата" : "2023-11-01", "Сумма" : 123.45, "Флаги" : [true,false,null]}
  , {"id" : 2, "Номер" : 202, "Дата" : "2023-11-02", "Сумма" : 321.54, "Флаги" : [false,null,true]}
  , {"id" : 3, "Номер" : 303, "Дата" : "2023-11-03", "Сумма" : 100.00, "Флаги" : [null,true,false]}
  ]$$::jsonb
)
SELECT
  *
FROM
  jsonb_to_recordset((TABLE jsd)) T(
    id
      integer
  , "Номер"
      integer
  , "Дата"
      date
  , "Сумма"
      numeric(32,2)
  , "Флаги"
      boolean[]
  );

Итого: RTFM!

Если в вашем запросе к базе или его плане начинает "отрастать копипаста" - скорее всего, вы делаете что-то не так, и стоит почитать еще и статью "PostgreSQL Antipatterns: «где-то я тебя уже видел...»".