JavaScript variable not working when removing JSONB array element in PostgreSQL UPDATE query

[FlyingEagle]

I have a PostgreSQL database with a JSONB column that contains arrays. I’m trying to remove array items using JavaScript variables but running into issues.

This works fine when hardcoding the index:

UPDATE orders
SET data = data - 1
WHERE id = 'xyz789';

This also works with variables in WHERE clause:

const orderId = 'xyz789'

POSTGRES_SQL`
  UPDATE orders
  SET data = data - 1
  WHERE id = ${orderId};
`

But this doesn’t work when using variable for the array index:

const orderId = 'xyz789'
const itemIndex = 1

POSTGRES_SQL`
  UPDATE orders
  SET data = data - ${itemIndex}
  WHERE id = ${orderId};
`

The query runs without errors but nothing gets removed from the array. I’ve tried different approaches like casting to text or jsonb but none of them work. Has anyone faced this issue before? How can I properly use a JavaScript variable to specify which array index to remove from a JSONB column?

[miat]

PostgreSQL treats ${itemIndex} as a variable instead of the actual value you need for array modifications. That’s why parameterized queries don’t work here - you’ll need to build the query dynamically. Just ensure your index is a valid integer first to prevent SQL injection. It’s best to validate itemIndex beforehand and then drop it directly into the query string using template literals. Alternatively, consider using jsonb_set to rebuild the array without removing elements directly.

[Stella_Dreamer]

postgresql needs an actual integer for jsonb array index removal - you can’t use a parameter. you’ll have to build the query string dynamically instead. so update orders set data = data - ${itemIndex} where id = $1 won’t work, but update orders set data = data - 1 where id = $1 does. pretty annoying limitation.

[alexlee]

I encountered the same issue recently. The PostgreSQL JSONB operator requires a fixed integer literal for removing an array element, which means you can’t directly use a parameter like in your query. I found success by dynamically constructing the SQL statement. Here’s an example:

const orderId = 'xyz789';
const itemIndex = 1;
const query = `UPDATE orders SET data = data - ${itemIndex} WHERE id = '${orderId}';`;
// Execute query

This way, the itemIndex is correctly interpreted as a literal during query execution.