CODEWITHSUNDEEP
jsonpostgresqlplpgsqlpostgresql-9.4postgresql-9.5
Seonixx
Seonixx

Reputation: 468

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?

Upvotes: 1

Views: 2513

Answers (1)

klin
klin

Reputation: 121784

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;

Upvotes: 2

Related Questions