Seonixx February 2016

Postgres plpgsql JSON assignment

I'm working with JSONB in Postgres and trying to understand how to correctly perform an assignment to a JSON property in plpgsql.

This query snippet reports a syntax error because of the leading bracket on the assignment, however I was fairly sure this syntax was required to refer to JSON objects in plpgsq:

IF (NEW."data")->>'custom' IS NULL THEN
  (NEW."data")->>'custom' := 0;

This is in a postgresql trigger, so NEW is the provided variable relating to the new database record.

Can someone advise what the correct technique is for assigning a value to a JSON(B) property?


klin February 2016

In Postgres 9.5 it is quite simple with the function jsonb_set():

if>>'custom' is null then = jsonb_set(, '{custom}', '0'::jsonb);
end if;

There is no jsonb_set() in Postgres 9.4 so the issue is more complicated:

if>>'custom' is null then = (
        select json_object_agg(key, value)
        from (
            select key, value
            from jsonb_each(
            values ('custom', '0'::jsonb)
        ) s
end if;

Post Status

Asked in February 2016
Viewed 2,220 times
Voted 7
Answered 1 times


Leave an answer