I’m working with a PostgreSQL database and trying to update a JSONB column using a JavaScript library. My table has a column called cliente_info which stores JSON data, and I need to update a nested object inside it.
DB_SQL`UPDATE orders
SET cliente_info = jsonb_set(
cliente_info,
'{address}',
'${DB_SQL({
zipcode,
district,
city,
details,
state,
street,
area,
house_number,
notes,
})}'
)
WHERE order_id = ${order_id} RETURNING order_id;`
This gives me “Invalid input syntax for type json” error. When I try hardcoded empty values it works but doesn’t insert the actual data:
Using string interpolation gives “Could not determine data type of parameter” error. My INSERT query works fine with the same syntax. How can I properly update nested JSONB data with dynamic values?
This happens because PostgreSQL can’t handle parameter substitution properly inside jsonb_set when you wrap the object with DB_SQL(). I ran into the same thing last year. The cleanest fix is passing the entire JSON object as a separate parameter: const addressData = JSON.stringify({ zipcode, district, city, details, state, street, area, house_number, notes }); DB_SQLUPDATE orders SET cliente_info = jsonb_set( cliente_info, '{address}', ${addressData}::jsonb ) WHERE order_id = ${order_id} RETURNING order_id; Use JSON.stringify() to convert your object to a JSON string, then cast it to jsonb with ::jsonb. PostgreSQL knows exactly what data type it’s working with and handles the parameter substitution correctly.
Yeah, ran into this same issue a few months back. You’re mixing templating systems - putting DB_SQL inside DB_SQL breaks the parser. Switch to $1, $2 placeholders: DB_SQL'UPDATE orders SET cliente_info = jsonb_set(cliente_info, $1, $2) WHERE order_id = $3', ['{address}', JSON.stringify(addressObj), order_id] Much cleaner than nested templates.
This happens because PostgreSQL’s parameter binding gets confused when you nest DB_SQL() calls inside jsonb_set. The database driver can’t figure out parameter types and boundaries when you embed one DB_SQL query inside another. I’ve hit this exact issue before - the fix is building your JSON outside the query first. Prepare the address object separately, then pass it as a clean parameter. Like this: const newAddress = { zipcode, district, city, details, state, street, area, house_number, notes }; then DB_SQL'UPDATE orders SET cliente_info = jsonb_set(cliente_info, '{address}', ${JSON.stringify(newAddress)}::jsonb) WHERE order_id = ${order_id};' No more nested DB_SQL calls means PostgreSQL can properly handle the data types.