Node -postgres: как выполнить запрос WHERE col IN (<динамический список значений>) "?

Ответ 1

Мы уже видели этот вопрос в списке проблем github. Правильный способ - динамически генерировать список параметров на основе массива. Что-то вроде этого:

var arr = [1, 2, "hello"];
var params = [];
for(var i = 1; i <= arr.length; i++) {
  params.push('$' + i);
}
var queryText = 'SELECT id FROM my_table WHERE something IN (' + params.join(',') + ')';
client.query(queryText, arr, function(err, cb) {
 ...
});

Таким образом вы получаете параметризированное экранирование postgres.

Ответ 2

Похоже, что вы, возможно, были близки по вашему комментарию к @ebohlman answer. Вы можете использовать WHERE id = ANY($1::int[]). PostgreSQL будет преобразовать массив в тип, на который выполняется параметр, в $1::int[]. Итак, здесь надуманный пример, который работает для меня:

var ids = [1,3,4]; 

var q = client.query('SELECT Id FROM MyTable WHERE Id = ANY($1::int[])',[ids]);

q.on('row', function(row) {
  console.log(row);
})

// outputs: { id: 1 }
//          { id: 3 }
//          { id: 4 }

Ответ 3

Используя pg-prom, это хорошо работает через CSV-фильтр (значения, разделенные запятыми):

const values = [1, 2, 3, 4];

db.any('SELECT * FROM table WHERE id IN ($1:csv)', [values])
    .then(data => {
        console.log(data);
    })
    .catch(error => {
        console.log(error);
    });

И для решения проблемы различных типов данных :csv modifier сериализует массив в csv, преобразовывая все значения в их собственный формат PostgreSQL в соответствии со своим типом JavaScript, даже поддерживая форматирование пользовательского типа.

И если у вас есть значения типа смешанного типа: const values = [1, 'two', null, true], вы все равно получите правильно экранированный SQL:

SELECT * FROM table WHERE id IN (1, 'two', null, true)

ОБНОВИТЬ

Из v7.5.1 pg- prom начал поддерживать :list как сменный псевдоним для фильтра :csv:

db.any('SELECT * FROM table WHERE id IN ($1:list)', [values])

Ответ 4

Лучшее решение, которое я нашел, заключалось в использовании функции ANY с принуждением массива Postgres. Это позволяет сопоставить столбец с произвольным массивом значений, как если бы вы записали col IN (v1, v2, v3). Это подход в pero answer, но здесь я показываю, что производительность ANY совпадает с IN.

Query

Ваш запрос должен выглядеть так:

SELECT * FROM table WHERE id = ANY($1::int[])

Этот бит в конце, который говорит, что $1::int[] можно изменить, чтобы соответствовать типу столбца "id". Например, если тип ваших идентификаторов uuid, вы должны написать $1::uuid[], чтобы принудить аргумент к массиву UUID. См. здесь список типов Postgres.

Это проще, чем писать код для построения строки запроса и безопасно для инъекций SQL.

Пример

С node -postgres, полный пример JavaScript выглядит так:

var pg = require('pg');

var client = new pg.Client('postgres://username:[email protected]/database');
client.connect(function(err) {
  if (err) {
    throw err;
  }

  var ids = [23, 65, 73, 99, 102];
  client.query(
    'SELECT * FROM table WHERE id = ANY($1::int[])',
    [ids],  // array of query arguments
    function(err, result) {
      console.log(result.rows);
    }
  );
});

Производительность

Один из лучших способов понять производительность SQL-запроса - это посмотреть, как обрабатывается база данных. В таблице примеров содержится около 400 строк и первичный ключ с именем "id" типа text.

EXPLAIN SELECT * FROM tests WHERE id = ANY('{"test-a", "test-b"}');
EXPLAIN SELECT * FROM tests WHERE id IN ('test-a', 'test-b');

В обоих случаях Postgres сообщили тот же план запроса:

Bitmap Heap Scan on tests  (cost=8.56..14.03 rows=2 width=79)
  Recheck Cond: (id = ANY ('{test-a,test-b}'::text[]))
  ->  Bitmap Index Scan on tests_pkey  (cost=0.00..8.56 rows=2 width=0)
        Index Cond: (id = ANY ('{test-a,test-b}'::text[]))

Вы можете увидеть другой план запроса в зависимости от размера вашей таблицы, где есть индекс и ваш запрос. Но для запросов, подобных выше, ANY и IN обрабатываются одинаково.

Ответ 5

Другим возможным решением является использование функции UNNEST следующим образом:

 var ids = [23, 65, 73, 99, 102];
 var strs = ['bar', 'tar', 'far']
 client.query(
   'SELECT * FROM table WHERE id IN(SELECT(UNNEST($1))',
    [ids],  // array of query arguments
    function(err, result) {
       console.log(result.rows);
    }
);
client.query(
   'SELECT * FROM table WHERE id IN(SELECT(UNNEST($1))',
    [strs],  // array of query arguments
    function(err, result) {
       console.log(result.rows);
    }
);

Я использовал это в хранимой процедуре, и он отлично работает. Поверьте, он должен работать и от node -pg кода.

Вы можете прочитать информацию о функции UNNEST здесь.

Ответ 6

Другим возможным решением является, например, REST API в NODE JS:

var name = req.body;//Body is a objetc that has properties for example provinces
var databaseRB = "DATABASENAME"
var conStringRB = "postgres://"+username+":"+password+"@"+host+"/"+databaseRB; 

var filter_query = "SELECT row_to_json(fc) FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features FROM (SELECT 'Feature' As type, ST_AsGeoJSON(lg.geom)::json As geometry, row_to_json((parameters) As properties FROM radiobases As lg WHERE lg.parameter= ANY($1) )As f) As fc";

var client = new pg.Client(conStringRB);
client.connect();
var query = client.query(new Query(filter_query,[name.provinces]));
query.on("row", function (row, result) {
  result.addRow(row);
});
query.on("end", function (result) {
 var data = result.rows[0].row_to_json
   res.json({
     title: "Express API",
     jsonData: data
     });
});

Имейте в виду, что можно использовать любой тип массива

Ответ 7

Идея в целом:

var invals = [1,2,3,4], cols = [...fields];
var setvs = vs => vs.map(v=> '$'+ (values.push(v))  ).join();

var values = [];
var text = 'SELECT '+ setvs(cols) +' FROM table WHERE id IN (' + setvs(invals) +')';