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;
END IF;

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?

Answers


klin February 2016

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

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

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

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

Post Status

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

Search




Leave an answer