Когда я впервые опубликовал клиент 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 при вставке большого количества строк или вложенных структур данных.