If I were to perform the following script in newer versions of Postgres (13 and upward), then elements within the JSONB object can easily be added and amended using ||.
DO $$
declare j1 jsonb;
declare j2 jsonb;
declare j3 jsonb;
declare j4 jsonb;
begin
j1 := ('{"A":'||false||',"B":'||false||',"C":'||false||',"D":'||false||',"E":'||false||',"F":'||false||'}')::jsonb;
j2 := ('{"G":'||true||',"H":'||true||'}')::jsonb;
j4 := j1||j2;
raise notice '%',j4;
--Required output: {"A":false,"B":false,"C":false,"D":false,"E":false,"F":false,"G":true,"H":true}
j3 := ('{"A":'||false||',"B":'||true||',"C":'||false||',"D":'||false||',"E":'||true||',"F":'||false||'}')::jsonb;
j4 := j4||j3;
raise notice '%',j4;
--Required output: {"A":false,"B":true,"C":false,"D":false,"E":true,"F":false,"G":true,"H":true}
end $$;
I am trying to achieve the same on Postgres 9.2. Since 9.2 is only compatible with JSON objects, I have to use JSON instead of JSONB. (Client not able to upgrade yet). However, the same code for JSON gives errors.
DO $$
declare j1 json;
declare j2 json;
declare j3 json;
declare j4 json;
begin
j1 := ('{"A":'||false||',"B":'||false||',"C":'||false||',"D":'||false||',"E":'||false||',"F":'||false||'}')::json;
j2 := ('{"G":'||true||',"H":'||true||'}')::json;
j4 := j1||j2;
raise notice '%',j4;
--Required output: {"A":false,"B":false,"C":false,"D":false,"E":false,"F":false,"G":true,"H":true}
j3 := ('{"A":'||false||',"B":'||true||',"C":'||false||',"D":'||false||',"E":'||true||',"F":'||false||'}')::json;
j4 := j4||j3;
raise notice '%',j4;
--Required output: {"A":false,"B":true,"C":false,"D":false,"E":true,"F":false,"G":true,"H":true}
end $$;
Are there any functions that I can use to add/amend elements in a JSON object in Postgres 9.2? Ultimately achieving the same results in the second block of code as in the first block of code. Thanks.
declarestarts a block where variables are define. It's useless to start a new block for each variable.