Node-postgres: Difficulties using jsonb_set with parameterized query

Created on 14 Mar 2018  Â·  2Comments  Â·  Source: brianc/node-postgres

If I have a table with id, and prefs, and prefs is of type jsonb, it's not clear how to update a JSON value with a parameterized query. For instance this works as expected:
UPDATE app_preferences SET prefs=jsonb_set(prefs, '{"foo"}', '"bar"') WHERE id=1;

This appropriately sets {"foo":"bar"} in prefs. However, none of these seem to work where $2 is bar (note the variations of quotes).
UPDATE app_preferences SET prefs=jsonb_set(prefs, '{"foo"}', '"$2"') WHERE id=$1
UPDATE app_preferences SET prefs=jsonb_set(prefs, '{"foo"}', "'$2'") WHERE id=$1
UPDATE app_preferences SET prefs=jsonb_set(prefs, '{"foo"}', '$2') WHERE id=$1
UPDATE app_preferences SET prefs=jsonb_set(prefs, '{"foo"}', "$2") WHERE id=$1
UPDATE app_preferences SET prefs=jsonb_set(prefs, '{"foo"}', $2) WHERE id=$1

What am I missing?

I'm using PostgreSQL 9.6.8, and I've tried this in node-postgres 6.1.4 and 7.4.1

question

Most helpful comment

You can use to_json[b] to convert some text to a JSON string:

UPDATE app_preferences SET prefs=jsonb_set(prefs, '{foo}', to_jsonb($2::text)) WHERE id=$1

Passing JSON.stringify(…) as the parameter with your last query would also work. The rest of them don’t refer to the $2 parameter at all.

All 2 comments

You can use to_json[b] to convert some text to a JSON string:

UPDATE app_preferences SET prefs=jsonb_set(prefs, '{foo}', to_jsonb($2::text)) WHERE id=$1

Passing JSON.stringify(…) as the parameter with your last query would also work. The rest of them don’t refer to the $2 parameter at all.

Thank you @charmander!

Was this page helpful?
0 / 5 - 0 ratings