02.11.2023 06:45
Kilor 10 9000 Источник

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

...
, (((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: «где-то я тебя уже видел...»".

Комментарии (10)


  1. Algrinn
    02.11.2023 14:15
    #26118776

    Есть теория, что проектированием БД занимались MongoDB-шники. Узнаю неповторимый стиль. :-) Жуткая денормализация. Это делать опасно. Такое можно мудрить только в крайне редких случаях, когда нужно поднять скорость выполнения запросов и ничего другое уже не помогает.


    1. Kilor Автор
      02.11.2023 14:15
      #26118788

      Не-не, что вы! ))

      jsd тут - это CTE, которую собрали сначала из нормальных реляционных записей в базе.


  1. RichardBlanck
    02.11.2023 14:15
    #26119698
    -1

    После SELECT * можно не читать.


  1. mvv-rus
    02.11.2023 14:15
    #26119984

    Интересно. Но я вот только не пойму, зачем реляционную CEБД PostgreSQL использовать таким образом - хранить сырые объекты в JSON? Можно ведь использовать объектную NoSQL СУБД - она специально сконструирована под такое использование. Или - с помощью какой-нибудь ОРМ либо вручную отображать объекты на обычные поля обычных таблиц (хотя бы частично, для тех полей, по которым поиск и фильтрация часто производятся)?

    Или хранение JSON и манипуляции с ним - это очень маленькая часть большого проекта, которому в остальной части требуется именно реляционная СУБД?


    1. santjagocorkez
      02.11.2023 14:15
      #26120056

      До появления schemaless json(b) в постгрессе интернет-магазин реализовывали в основном так: справочник типов параметров, справочник номенклатурных типов, таблица-схема с объявлением типов параметров, участвующих в определении номенклатурного типа; затем каждая номенклатурная позиция в своей таблице, и плюс в другой таблице заполнялись значения параметров для соответствующего номенклатурного типа. Уже только на этом этапе "достать" из базы описание товара с типом, например, "телевизор" требовало множественных джоинов, причем, в процессе участвовали таблицы-связки с отношениями many-to-many.

      С появлением json(b) документ-описание типа номенклатуры стало можно хранить сразу в той же строке, в которой объявляется сам номенклатурный тип:

      • для тетради там будут объявлены параметры "type_of_pages: {"oneOf": ["diagonal", "squares", "horizontal"]}"

      • а для телевизора там может быть "refresh_frequency", "mass", "packed_mass"...

      При этом, если добывается позиция из базы, достаточно весьма простого джоина к справочнику номенклатурных типов, который, раз уж уже попал в выборку, уже содержит и все параметры, дополнительных джоинов со сложными отношениями к этому добавлять не надо

      И при этом сохраняется возможность прицепить другие, обычно нормализованные данные: пользователь-автор позиции в базе, компания, которой принадлежит номенклатурный тип (чтобы не показывать другим клиентам чужие типы, например).

      Если же делать на монге, то:

      • либо придётся выносить и нормализованные данные туда же, по пути получив много веселья с aggregation pipelines и с индексами

      • либо делать запросы в базы в несколько проходов: сначала достать номенклатуру, узнать ее тип, сходить в монгу, достать не нормализуемые данные оттуда, сходить опять в базу за оставшимися данными

      При этом JSON-инфраструктура в постгресе сделана весьма грамотно. Один только json_agg, поддерживающий все плюшки агрегатных функций, чего стоит. Можно просто бросить в постгрес готовый JSON, заставить его развернуть его в табличное представление, промэтчить его, обернуть конкретные поля "колясками", NULLIF-ами, окошками (и прочими всеми имеющимися средствами обычного SQL), промаркировать ввод, вставить позиции, соответствующие критерию, выплюнуть обратно другой JSON, в котором каждая входная запись имеет маркировку, например, вставилось ли, если нет, то по какой причине.


    1. Kilor Автор
      02.11.2023 14:15
      #26120204
      +1

      зачем реляционную CEБД PostgreSQL использовать таким образом - хранить сырые объекты в JSON?

      Возможно, я недостаточно акцентировал внимание на том моменте, что этот кейс не про хранение JSON, а про его использование в качестве промежуточной структуры - либо как словаря с доступом по ключу вместо множественных CTE Scan, либо как параметра-выборки, передаваемого на вход запроса в качестве $1-значения.


    1. gsl23
      02.11.2023 14:15
      #26120438

       зачем реляционную CEБД PostgreSQL использовать таким образом - хранить сырые объекты в JSON? 

      Обычно ответ - ACID, если ACID требования нет, транзакционность не нужна, тогда да хранить и обрабатывать json в pg - дорого и неэффективно.


    1. breninsul
      02.11.2023 14:15
      #26121878

      очень просто.

      Для начала посгря быстрее в большинстве ситуаций. Учитывая ACID , триггеры , процедуры и прочие плюшки, вопрос скорее в чем смысл использовать MongoDB.

      Во вторых jsonb поле можно использовать только там, где и нужно хранить денормализованные данные. А не плодить 30 таблиц. Например, raw json запроса. Metadata NFT токенов. Настройки. Да бесчисленное множество таких ситуаций.


  1. breninsul
    02.11.2023 14:15
    #26121852

    Кажется главная проблема исходного запроса в том, что вообще не ясно что происходит (вообще сложно понять какая это часть запроса, возможно имея весь запрос было бы более понятно)

    И боюсь показаться некомпетентным, но зачем ключевое слово TABLE?

    (TABLE jsd)


    1. Kilor Автор
      02.11.2023 14:15
      #26121888

      И боюсь показаться некомпетентным, но зачем ключевое слово TABLE?

      Это "синтаксический сахар", который позволяет писать короче обращение к CTE (или таблицам) вида 1 строка/1 столбец - в отдельной статье чуть больше информации.

      возможно имея весь запрос было бы более понятно

      Вряд ли. Алгоритмически - там по определенным правилам из нескольких таблиц собирается "большая" CTE, из которой потом по относительно небольшому набору ключей надо "выдернуть" некоторые записи.

      Классический JOIN пары CTE дает Nested Loop с множественными проходами по "большой" CTE, из которой N-1 запись каждый раз бессмысленно отфильтровывается - об этом как раз и написано в статье про "ословаривание".