/* Code-Comments */

JSON Operators And Functions In Postgres

November 25, 2019

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: 1 | operator | Right Operand Type | Return Type | Description | Example | Example Result| | --- | ----------------- | --------------- | ------------ |------------ |------------ | | -> | int | json or jsonb | Get JSON array element (indexed from zero, negative integers count from the end) | ’[{“a”:“foo”},{“b”:“bar”},{“c”:“baz”}]‘::json->2 | { “c”:“baz”}| | -> | text | json or jsonb | Get JSON object field by key |’{“a”: {“b”:“foo”}}‘::json->‘a’ | {“b”:“foo”} | | ->> | int | text | Get JSON array element as text |’[1,2,3]‘::json->>2 |3 | | ->> | text | text | Get JSON object field as text |’{“a”:1,“b”:2}‘::json->>‘b’| 2| | #> | text[] | json or jsonb | Get JSON object at the specified path | ’{“a”: {“b”:{“c”: “foo”}}}‘::json#>‘{a,b}’| {“c”: “foo”} | | #>> | text[] | text | Get JSON object at the specified path as text |’{“a”:[1,2,3],“b”:[4,5,6]}‘::json#>>‘{a,2}’| 3|

Footnotes

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

Stephen Weiss

Thanks for reading! My name's Stephen Weiss. I live in Chicago with my wife, Kate, and dog, Finn.
Click here to see the archives of my weeks in review and sign up yourself!