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
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!
Most helpful comment
You can use
to_json[b]to convert some text to a JSON string:Passing
JSON.stringify(…)as the parameter with your last query would also work. The rest of them don’t refer to the$2parameter at all.