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-ключа в каждое отдельное одноименное поле
«ручное» преобразование текстового представления массива в json в текстовое представление 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: «где-то я тебя уже видел…».
