node.js node-postgres：如何执行“WHERE col IN (<dynamic value list>)”查询？

node-postgres: how to execute "WHERE col IN (<dynamic value list>)" query?

提问by lanzz

I'm trying to execute a query like this:

我正在尝试执行这样的查询：

SELECT * FROM table WHERE id IN (1,2,3,4)

The problem is that the list of ids I want to filter against is not constant and needs to be different at every execution. I would also need to escape the ids, because they might come from untrusted sources, though I would actually escape anything that goes in a query regardless of the trustworthiness of the source.

问题是我要过滤的 id 列表不是恒定的，每次执行时都需要不同。我还需要转义 id，因为它们可能来自不受信任的来源，尽管我实际上会转义查询中的任何内容，而不管来源的可信度如何。

node-postgres appears to work exclusively with bound parameters: client.query('SELECT * FROM table WHERE id = $1', [ id ]); this will work if I had a known number of values (client.query('SELECT * FROM table WHERE id IN ($1, $2, $3)', [ id1, id2, id3 ])), but will not work with an array directly: client.query('SELECT * FROM table WHERE id IN ($1)', [ arrayOfIds ]), as there does not seem to be any special handling of array parameters.

节点的Postgres似乎与绑定参数专门工作：client.query('SELECT * FROM table WHERE id = $1', [ id ]); 如果我有已知数量的值 ( client.query('SELECT * FROM table WHERE id IN ($1, $2, $3)', [ id1, id2, id3 ]))，这将起作用，但不能直接使用数组: client.query('SELECT * FROM table WHERE id IN ($1)', [ arrayOfIds ])，因为似乎没有对数组参数进行任何特殊处理。

Building the query template dynamically according to the number of items in the array and expanding the ids array into the query parameters array (which in my actual case also contains other parameters besides the list of ids) seems unreasonably burdensome. Hard-coding the list of ids in the query template seems not viable either, as node-postgres does not provide any value escaping methods.

根据数组中的项数动态构建查询模板并将 ids 数组扩展到查询参数数组（在我的实际情况中，除了 id 列表之外还包含其他参数）似乎不合理地繁重。对查询模板中的 id 列表进行硬编码似乎也不可行，因为 node-postgres 不提供任何值转义方法。

This seems like a very common use-case, so my guess is that I'm actually overlooking something, and not that it is not possible to use the common IN (values)SQL operator with node-postgres.

这似乎是一个非常常见的用例，所以我的猜测是我实际上忽略了一些东西，而不是不可能在IN (values)node-postgres 中使用常见的SQL 运算符。

If anybody has solved this problem in a more elegant manner than those I listed above, or if I'm really missing something about node-postgres, please help.

如果有人以比我上面列出的方式更优雅的方式解决了这个问题，或者如果我真的错过了关于 node-postgres 的一些东西，请帮忙。

采纳答案by brianc

We've seen this question before on the github issues list. The correct way is to dynamically generate your list of parameters based on the array. Something like this:

我们之前在 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) {
 ...
});

That way you get the postgres parameterized escaping.

这样你就可以得到 postgres 参数化的转义。

回答by Pero P.

It looks like you may have been close based on your comment to @ebohlman's answer. You can use WHERE id = ANY($1::int[]). PostgreSQL will convertthe array to the type the parameter is cast to in $1::int[]. So here's a contrived example that works for me:

根据您对@ebohlman 的回答的评论，您似乎已经接近了。您可以使用WHERE id = ANY($1::int[]). PostgreSQL 会将数组转换为参数在 in 中强制转换的类型$1::int[]。所以这是一个对我有用的人为例子：

var ids = [1,3,4]; 

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

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

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

回答by ide

The best solution I've found has been to use the ANYfunction with Postgres' array coercion. This lets you match a column with an arbitrary array of values as if you had written out col IN (v1, v2, v3). This is the approach in pero's answerbut here I show that the performance of ANYis the same as IN.

我发现的最佳解决方案是将该ANY函数与 Postgres 的数组强制一起使用。这使您可以将一列与任意值数组进行匹配，就像已写出col IN (v1, v2, v3). 这是pero 的答案中的方法，但在这里我表明 的性能ANY与IN.

Query

询问

Your query should look like:

您的查询应如下所示：

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

That bit at the end that says $1::int[]can be changed to match the type of your "id" column. For example, if the type of your IDs is uuid, you'd write $1::uuid[]to coerce the argument to an array of UUIDs. See here for the list of Postgres datatypes.

最后的那一点$1::int[]可以更改以匹配您的“id”列的类型。例如，如果您的 ID 的类型是uuid，您将写入$1::uuid[]以将参数强制转换为 UUID 数组。请参阅此处获取 Postgres 数据类型列表。

This is simpler than writing code to construct a query string and is safe against SQL injections.

这比编写代码来构造查询字符串更简单，并且可以安全地防止 SQL 注入。

Example

例子

With node-postgres, a complete JavaScript example looks like:

使用 node-postgres，一个完整的 JavaScript 示例如下所示：

var pg = require('pg');

var client = new pg.Client('postgres://username:password@localhost/database');
client.connect(function(err) {
  if (err) {
    throw err;
  }

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

Performance

表现

One of the best ways to understand the performance of a SQL query is to look at how the database processes it. The sample table has about 400 rows and a primary key called "id" of type text.

了解 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');

In both cases, Postgres reported the same query plan:

在这两种情况下，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[]))

You might see a different query plan depending on the size of your table, where there's an index, and your query. But for queries like the ones above, ANYand INare processed the same way.

您可能会看到不同的查询计划，具体取决于表的大小、索引所在的位置以及您的查询。但是对于像上面那样的查询，ANY并IN以相同的方式处理。

回答by vitaly-t

Using pg-promise, this works well via the CSV Filter(comma-separated values):

使用pg-promise，这可以通过CSV 过滤器（逗号分隔值）很好地工作：

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

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

And to address the concern about various data types, :csvmodifier serializes the array into csv, while converting all values into their proper PostgreSQL format, according to their JavaScript type, even supporting the Custom Type Formatting.

并且为了解决对各种数据类型的担忧，:csv修饰符将数组序列化为 csv，同时根据它们的 JavaScript 类型将所有值转换为其正确的 PostgreSQL 格式，甚至支持自定义类型格式。

And if you have mixed-type values like this: const values = [1, 'two', null, true], you still will get the correctly escaped SQL:

如果您有这样的混合类型值：const values = [1, 'two', null, true]，您仍然会得到正确转义的 SQL：

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

UPDATE

更新

From v7.5.1, pg-promisestarted supporting :listas an interchangeable alias for the :csvfilter:

从 v7.5.1开始，pg-promise开始支持:list作为:csv过滤器的可互换别名：

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

回答by C. Marca

Another posible solution is for example for REST API in NODE JS:

另一个可能的解决方案是例如 NODE JS 中的 REST API：

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() )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
     });
});

Keep in mind that any type of array can be used

请记住，可以使用任何类型的数组

回答by Yaki Klein

Another possible solution is to use the UNNESTfunction like this:

另一种可能的解决方案是使用这样的UNNEST函数：

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

I've used this in a stored procedure and it works fine. Believe it should work also from node-pg code.

我在存储过程中使用了它，它工作正常。相信它也应该从 node-pg 代码中工作。

You can read about the UNNEST function here.

您可以在此处阅读有关 UNNEST 函数的信息。

回答by ALMEK

The idea generally:

思路大致如下：

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) +')';