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

Answer 1

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