json operators and functions in postgres

I wrote in the past about Array Intersections in Postgres, which alluded to operators and functions available for arrays within Postgres.

Today, I came across a Postgres function dealing with JSON and found a whole suite of new syntax to learn.

It’s actually pretty cool!

Here’s an example function.

create or replace function generate_log(
    target_op text,
    newj jsonb,
    oldj jsonb
) returns audit_log as $body$
declare
    audit_row audit_log;
    dkey text;

begin
    audit_row = row (
      # …
        );

    if (target_op = ‘DELETE’) then
        audit_row.root_resource_name = oldj ->> root_resource_name;
        audit_row.root_resource_id = oldj ->> root_resource_id;
        audit_row.resource_id = oldj ->> resource_id;
        audit_row.from_data = jsonb_strip_nulls(oldj);
    else
      # ...
    end if;
    # ...
    return audit_row;

end;
$body$ language plpgsql;

The gist of the function is that it takes in an operation type (e.g., DELETE) and two json objects and returns the delta between them.

Before we get there, however, we define a few of the return values based on the old JSON’s keys. That is, our new row will have the root_resource_name value that was the root_resource_name of the oldj JSON. That’s what the ->> operator does.

For space considerations, we also remove any null keys using the jsonb_strip_nulls method of Postgres.

There are several standard operators for json and jsonb. From the Postgres Documentation: ¹

Footnotes

• ¹ There are additional operators specifically for jsonb operators that are worth knowing as well.