Postgres UPDATE table SET jsonb typed column with javascript var not work for remove an array item

I'm using this package to CRUD in a Postgres database https://github.com/porsager/postgres

This is my query - it works fine to remove a index 0 or n... on a JSON array inside a jsonb column:

UPDATE pedidos
SET objeto = objeto - 0
WHERE _id = '123abc';

If use javascript var in _id on WHERE, this work normally

const _id = '123abc'

POSTGRES_SQL`
  UPDATE pedidos
  SET objeto = objeto - 0
  WHERE _id = ${_id};
`

but, if I inject the Javascript var as index (with any value, even 0) inside an SQL SET, like this:

const _id = '123abc'
const INDEX_TO_REMOVE = 0 // (or '0' or 1 or '1' or n...)

POSTGRES_SQL`
  UPDATE pedidos
  SET objeto = objeto - ${INDEX_TO_REMOVE}
  WHERE _id = ${_id};
`

it does not work, entry is not as removed, but it does not show any error.

If I quote it like this:

const _id = '123abc'
const INDEX_TO_REMOVE = 0 // (or '0' or 1 or '1' or n...)

POSTGRES_SQL`
  UPDATE pedidos
  SET objeto = objeto - ${INDEX_TO_REMOVE}
  WHERE _id = '${_id}';
`

It throws an error

Could not determine data type of parameter $1

I tried

objeto = objeto - ${INDEX_TO_REMOVE}::text

and

objeto = (objeto::jsonb - ${INDEX_TO_REMOVE})::text

and

objeto = (objeto::jsonb - ${INDEX_TO_REMOVE}::text)::jsonb

but nothing worked. How to use Javascript var inside a set to perform delete an index inside an array?

Solution

Your attempts to cast to ::text were a good idea, however it's the inverse cast you should do: the porsager PotsgreSQL package emits the parameter as text, while PostgreSQL awaits it as an integer.
Try instead to force it as an ::int:

const _id = '123abc'
const INDEX_TO_REMOVE = 0 // (or '0' or 1 or '1' or n...)

POSTGRES_SQL`
  UPDATE pedidos
  SET
    objeto = objeto - ${INDEX_TO_REMOVE}::int
  WHERE _id = ${_id};

Exploring PostgreSQL

Fiddling in pure SQL will help you to quickly understand all possibilities:

-- PostgreSQL JSON array is refered by an integer index:
select '[1,2,3]'::jsonb - 0;
-- → [2, 3]
-- Removing a text index from an array silently makes nothing:
select '[1,2,3]'::jsonb - '0';
-- → [1, 2, 3]
-- Ensuring it is an integer will work again:
select '[1,2,3]'::jsonb - '0'::int;
-- → [2, 3]
-- On the contrary, JSON objects work with textual indices:
select '{"0":1,"1":2,"2":3}'::jsonb - '0';
-- → {"1": 2, "2": 3}
-- And won't obey to integer ones, instead it will even error:
select '{"0":1,"1":2,"2":3}'::jsonb - 0;
# ERROR:  cannot delete from object using integer index

See it in a running fiddle

Understanding the package's choice

The package's author could have chosen to pass the variable in PostgreSQL's type corresponding to your Javascript variable (here an int, as your JS was an int).

However:

It cannot determine if the top-level JSON structure stored in your column is a JSON array or a JSON object without introspecting it (and that's not its goal, and not desirable from a performance point of view)
Most people store objects at the top-level, not arrays; so statistically it's less risky to consider it always an object
Additionally, mistaking the expected value as an int for an object errors ("cannot delete from object using integer index"), while mistaking it as a text while targeting an array silently does nothing
even if objects can have integer-like indices, while arrays cannot have alphabetical ones…
OK the author could have decided that the package's users should be rigorous and use JS integers when they need PostgreSQL int, and JS strings for a PostgreSQL text; but I'd say that the statistical usage made it appear as a risk of more false positives bug reports "your package crashes my SQL", so he decided to adapt his package to the statistically most probable use.

Credits

Thanks to:

your idea to cast
@AdrianKlaver's idea about the unmatched index being silent

that gave me the idea that the PostgreSQL package you use maybe cast the parameters with the "wrong" type, at least unintended one.