Pass array to postgresql query - nodejs

Question

I seem to be having an issue passing an array to my query and using the IN condition, I can successfully get results back when I manually add each search term to the query

async function getFixtures() {
  let response;
  try {
    response = await pool.query("SELECT * FROM fixtures WHERE league_name IN ('Chinese Super League', 'Iran Pro League', 'English Premier League')");
  } catch (e) {
    console.error('Error Occurred', e);
  }
    return response.rows;
}

When I try passing an array though I get no results back

async function getFixtures(leaguesArray) {
  let response;
  try {
    response = await pool.query("SELECT * FROM fixtures WHERE league_name IN ($1)", [leaguesArray]);
  } catch (e) {
    console.error('Error Occurred', e);
  }
    return response.rows;
}

When I log out leaguesArray it will return

['Chinese Super League', 'Iran Pro League', 'English Premier League']

So when it is passed to the query I think it is

[['Chinese Super League', 'Iran Pro League', 'English Premier League']]

Do I need to convert that initial array to a string?

I am obviously missing something here but unsure as to what

Thanks

Are You sure that ("SELECT * FROM fixtures WHERE league_name IN ($1)", [leaguesArray]) works? I mean - are You sure that it replace $1 with that array in the query? — KarlR
– KarlR, Commented Aug 30, 2018 at 8:02
Take a look at following GitHub FAQ, I think that targets exactly your problem. — Ramiz Wachtler
– Ramiz Wachtler, Commented Aug 30, 2018 at 8:20

mtshaikh · Accepted Answer · 2018-08-30 08:28:25Z

22

As the docs mention. I believe you use two approaches

Approach #1

async function getFixtures(leaguesArray) {
let response;
  try {
    response = await pool.query("SELECT * FROM fixtures WHERE league_name = ANY ($1)", [leaguesArray]));
  } catch (e) {
    console.error('Error Occurred', e);
  }
return response.rows;
}

Approach #2

async function getFixtures(leaguesArray) {
let response;
const offset = 1;
const placeholders = leagueArray.map(function(name,i) { 
    return '$'+(i+offset); 
}).join(',');
  try {
    response = await pool.query("SELECT * FROM fixtures WHERE league_name IN (" + placeholders+")", leaguesArray));
  } catch (e) {
    console.error('Error Occurred', e);
  }
return response.rows;
}

answered Aug 30, 2018 at 8:28

mtshaikh

6884 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nikita Over a year ago

Approach #2 fails if placeholders length is 0. Getting WHERE league_name IN () throws syntax error at or near ")"

Collectives™ on Stack Overflow

Pass array to postgresql query - nodejs

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related