Когда я впервые опубликовал клиент PostgreSQL Слоник для Node.js, я работал с большими наборами данных, вставляя десятки миллионов записей в час. Однако форма вставляемых данных обычно была довольно плоской, и поэтому их довольно легко вставлять с помощью INSERT INTO ... SELECT * FROM unnset() pattern
. В то время я рекомендовал шаблон unnest
как шаблон для вставки большого количества строк в пакетах (это была часть I).
Однако сегодня я нашел способ получше: jsonb_to_recordset.
jsonb_to_recordset
расширяет массив объектов JSON верхнего уровня до набора строк, имеющих составной тип, определенный предложениемAS
.
Проще говоря, jsonb_to_recordset
позволяет преобразовывать произвольный JSON в набор записей, который мы можем использовать для запроса и вставки записей, как и в случае с unnest. Однако, в отличие от unnest, поскольку мы просто передаем JSON в PostgreSQL, результирующий формат намного более выразительный и мощный.
SELECT * FROM json_to_recordset('[{"name":"John","tags":["foo","bar"]},{"name":"Jane","tags":["baz"]}]') AS t1(name text, tags text[]); name | tags ------+----------- John | {foo,bar} Jane | {baz} (2 rows)
Давайте продемонстрируем, как вы будете использовать его для вставки данных.
Вставка данных с помощью json_to_recordset
Допустим, вам нужно вставить список людей в базу данных, и у каждого человека есть массив связанных с ним атрибутов.
const persons = [ { name: 'John', tags: ['foo', 'bar'] }, { name: 'Jane', tags: ['baz'] } ];
У вас может возникнуть соблазн просто перебрать массив и запустить отдельный запрос INSERT
для каждой записи, например.
for (const person of persons) { await pool.query(sql` INSERT INTO person (name, tags) VALUES ( ${person.name}, ${sql.array(person.tags, 'text[]')} ) `); }
При работе с несколькими записями это даже предпочтительнее, потому что это легко читать и понимать. Однако, если вы похожи на меня и обнаруживаете, что отлаживаете INSERT
запрос, который вызывается более 2 миллионов раз в день, то, скорее всего, пакетные вставки предпочтительнее.
Что подтолкнуло к поиску лучшей альтернативы.
Вы также можете попытаться вставить с помощью шаблона unnest
, и это будет выглядеть примерно так:
await pool.query(sql` INSERT INTO public.person (name, tags) SELECT t1.name, t1.tags::text[] FROM unnest( ${sql.array(['John', 'Jane'], 'text')}, ${sql.array(['{foo,bar}', '{baz}'], 'text')} ) AS t1.(name, tags); `);
Проблема здесь в том, что вы фактически отвечаете за преобразование массивов в строковое представление массива PostgreSQL и передачу их в виде текстовых параметров, что некрасиво. Вам также нужно выполнить итерацию массива, чтобы построить срезы, представляющие значения для каждого столбца, что также довольно уродливо.
Однако благодаря jsonb_to_recordset
мы можем добиться того же результата с помощью:
await pool.query(sql` INSERT INTO person (name, tags) SELECT * FROM jsonb_to_recordset(${sql.jsonb(persons)}) AS t(name text, tags text[]) `);
В отличие от подхода unnest
, используя jsonb_to_recordset
, мы можем легко вставлять сложные вложенные структуры данных и передавать исходный документ JSON в запрос без необходимости манипулировать им.
С точки зрения производительности они также абсолютно одинаковы. Таким образом, моя текущая рекомендация — предпочитать jsonb_to_recordset
при вставке большого количества строк или вложенных структур данных.