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