0

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.

3
  • Unrelated, but: declare starts a block where variables are define. It's useless to start a new block for each variable. Commented Oct 18, 2022 at 6:54
  • My strategy for things like that: make this solution so expensive in terms of development, that it's cheaper to upgrade to a supported Postgres version. Does your client have some kind of security officer? Maybe alert them of the many security problems that are fixed in a current version. I have found that those responsible for security can very often put higher pressure on those that think upgrading has no benefits. Commented Oct 18, 2022 at 6:57
  • 1
    And please do not crosspost Commented Oct 18, 2022 at 6:58

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.