Interpolating variables in json string

user_7147833 0
Viewed 161

I am using the code below to create a function with variables that updates a jsonb column with a json object or create it if it doesn't exist - With help from this post

However I am really having trouble interpolating the variables $2 and $3 in the json string. Any suggestions?

CREATE OR REPLACE FUNCTION public.updateoffset(site text, offsetnumber integer, toscrape integer)
 RETURNS void
 LANGUAGE sql
AS $function$


 update settings set "offset" = coalesce("offset", '{}') || '{"$2": {"toscrape":3$}}'
  where site = $1;
$function$
jsonb postgresql sql
2 Answers

Do not use string interpolation for building JSON values - use JSON functions and operators instead, particularly json_build_object:

update settings
set "offset" = coalesce("offset", '{}') || json_build_object($2, json_build_object('toscrape', $3))
where site = $1;

Also it might be simpler to use json_set:

update settings
set "offset" = json_set(coalesce("offset", '{}'), ARRAY[$2::text,'toscrape'], $3)
where site = $1;

(which, however, does keep other properties in the inner object, not replacing it completely with an object that has only toscrape as a key)

user_10485720

Use the function format().

...
  update settings
  set "offset" = 
    coalesce("offset", '{}') || format('{"%s": {"toscrape":%s}}', $2, $3)::jsonb
  where site = $1;
...
user_19957380
Related