Home Ask Login Register

Developers Planet

Your answer is one click away!

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 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)
            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

Quote of the day: live life