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.
Asked by Ruan
(35 rep)
Oct 18, 2022, 06:47 AM