How to pass an array of integers to sql IN() query

Question

Sadly, I somehow was unable to find an answer for this for the past two hours... I am trying to pass an array of integers into an SQL IN query like so:

"SELECT id, bio, image_url, userhandle FROM users IN ([4,6,7,8])"

Of course this is not gonna work so i tried this, but it only works with string arrays. formattedUserIds is the array of integers.

"SELECT id, bio, image_url, userhandle FROM users IN ('" + formattedUserIds.join(",") + "')"

to clarify, basically what i need is to convert [4,6,7,8] -> 4,6,7,8 so i can pass it into the in clause like IN(4,6,7,8) any help is appreciated.

You could pass-in a composite array parameter and INNER JOIN on it as an alternative to WHERE IN: stackoverflow.com/questions/47466801/… — Dai
– Dai, Commented Feb 15, 2021 at 3:03
I'm sorry, complete newbie at sql. How would i do this in my code? @Dai — frankied003
– frankied003, Commented Feb 15, 2021 at 3:28

Erwin Brandstetter · Accepted Answer · 2021-02-15 05:06:39Z

2

Use the ANY construct instead and pass the array as is:

SELECT id, bio, image_url, userhandle
FROM   users
WHERE  id = ANY($user_ids);

IN vs ANY operator in PostgreSQL

answered Feb 15, 2021 at 5:06

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

Comments

George Joseph · Accepted Answer · 2021-02-15 05:05:33Z

0

Working on the example provided by Dai, here is a way you can get this done. Use the IN Clause as follows

SELECT id, bio, image_url, userhandle 
  FROM users IN (select *
                   from unnest(ARRAY[4,6,7,8])
                )

demo
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=27ca1628fb40540f9dcf07b8c4625e6e

answered Feb 15, 2021 at 5:05

George Joseph

5,93213 silver badges25 bronze badges

Comments

Tyler2P · Accepted Answer · 2022-07-12 15:50:48Z

0

Define a function to parse array values to SQL return string

const parseArrayForSqlQuery = (stringArray: number[]) => {
  let returnString = '';
  stringArray.forEach((value, index) => {
    if (index > 0) {
      returnString = returnString + ',';
    }
    returnString = returnString + `${value}`; 
    // add an inner quotes if the values are string
  });
  return returnString;
}

Consider the below array should be pass to SQL statement

const arrayValues = [4,6,7,8];

Use the SQL statement like so

`SELECT id, bio, image_url, userhandle FROM users where users.id IN (${parseArrayForSqlQuery(arrayValues})`

Expected output

`SELECT id, bio, image_url, userhandle FROM users where users.id IN (4,6,7,8)`

edited Jul 12, 2022 at 15:50

Tyler2P

2,37030 gold badges26 silver badges34 bronze badges

answered Jul 12, 2022 at 8:57

Hanz Haneefa

213 bronze badges

Comments

Muhammad Tameem Rafay · Accepted Answer · 2022-10-24 11:10:15Z

0

You can use find_in_set that can work with string array. e.g. 1,2,3,4

SELECT id, bio, image_url, userhandle
FROM   users
WHERE  find_in_set(users.id, $user_ids);

answered Oct 24, 2022 at 11:10

Muhammad Tameem Rafay

4,6354 gold badges28 silver badges33 bronze badges

Comments

frankied003 · Accepted Answer · 2021-02-15 17:35:18Z

-1

I ended up just doing

  "SELECT id, bio, image_url, userhandle FROM users WHERE id IN (" + formattedUserIds.toString() + ")"

Thank you!

answered Feb 15, 2021 at 17:35

frankied003

51610 silver badges30 bronze badges

1 Comment

Dai Over a year ago

I downvoted this answer because of the risk of SQL injection (and because of the possibility that using variadic IN won't be able to take advantage of cached execution plans). What guarantees do you have that formattedUserIds.toString() will always return a syntactically correct substring? What if formattedUserIds is empty? If you're formatting strings using culture/locale-specific formatting then you might see commas as digit-grouping characters which will break your query. And so on and so on.

Collectives™ on Stack Overflow

How to pass an array of integers to sql IN() query

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related