PostgreSQL function ON INSERT, insert JSON values to another table

Question

I'm attempting to write a function in PostgreSQL that receives a record when a Cloudant document is inserted, via listening to Cloudant (I would like to make it a lambda, but right now it is a node process).

I'm taking the Cloudant id and the complete document and inserting it to case_raw table with columns: char(32) id and json type raw_json.

CREATE TABLE report_db.case_raw
(
    doc_id character(32) NOT NULL PRIMARY KEY,
    raw_json jsonb NOT NULL
)

The effort to go from Cloudant to Postgres is so I can do complex joins and such on data that comes from edge devices in JSON form.

Here is a sample of the Cloudant doc, and I'm using the same id in the id column, where this would be inserted into raw_json.

{
    "name": {
        "id": "4e1e3425ef49b3b028683a5cb06ca585",
        "doc": {
            "_id": "4e1e3425ef49b3b028683a5cb06ca585",
            "_rev": "1-feae33bee0b773c7d130b1dd6fb42869",
            "doc_type": "standard",
            "location": {
                "carrier": "AT&T",
                "mobileCountryCode": 123,
                "mobileNetworkCode": 456
            }
        }
    }
}

Now, I'd like to make a trigger that calls a function for every row ON INSERT.

But, I'm having a huge hang up immediately. I've tried everything I can think of including: WITH, nested SELECT, to_json, casting ::json, and I bet it is simple, but why can't I (seemingly) not use JSON operators?

I can directly query records and get to the data I want from this record, for example (in two different ways getting the same thing):

SELECT * FROM case_raw WHERE raw_json->'name'->'doc'->'location'->>'carrier' = 'AT&T';

SELECT * FROM case_raw WHERE raw_json @> '{"name":{"doc": {"location": { "carrier": "AT&T"}}}}'

But, for the life of me I cannot step into the JSON. I've read atleast 20 posts, PG docs, PG tutorials, but not finding anything to move me forward. Any insight is greatly appreciated. Here I've greatly truncated attempts at using with, etc. PGAdmin reports an error on ->'name'.

CREATE OR REPLACE FUNCTION parse_raw ()
RETURNS trigger AS '
BEGIN
  -- We have a lot more values to insert, but PG does not seem to like the syntax of the following:
  INSERT INTO report_db.location_master VALUES (NEW.raw_json->'name'->'doc'->'location'->>'carrier');
  RETURN NEW;
END' LANGUAGE 'plpgsql';

-- setup on before insert trigger to parse into tables
CREATE TRIGGER parse_raw_trigger
BEFORE INSERT ON report_db.case_raw
FOR EACH ROW
EXECUTE PROCEDURE parse_raw();

@a_horse_with_no_name I've seen your name on so many of the posts around Postgres and I was actually really hoping you might see this one! I've added a sample with the matching property structure todo with location. Thank you in advance for any insight! — Neil Gaetano Lindberg
– Neil Gaetano Lindberg, Commented Nov 17, 2020 at 22:29

user330315 · Accepted Answer · 2020-11-18 06:58:38Z

1

You are embedding a string inside a string. So for the embedded 'name' the single quotes need to be escaped. So you essentially have ' ... 'name' ... which isn't a valid string constant. It should be ' ... ''name'' ...'

To make that easier, the usual approach is to wrap the whole function body (string) with dollar quotes

It's also good coding practice to always qualify the target columns in an INSERT statement.

CREATE OR REPLACE FUNCTION parse_raw ()
RETURNS trigger AS 
$body$ --<< replaces the '
BEGIN
 -- We have a lot more values to insert, but PG does not seem to like the syntax of the following:
 INSERT INTO report_db.location_master (carrier) VALUES (NEW.raw_json->'name'->'doc'->'location'->>'carrier');
 RETURN NEW;
END;
$body$ --<< replaces the '
LANGUAGE plpgsql;

answered Nov 18, 2020 at 6:58

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Neil Gaetano Lindberg Over a year ago

Brilliant! Following advice on qualifying target columns makes everything easier to read too. bows

Collectives™ on Stack Overflow

PostgreSQL function ON INSERT, insert JSON values to another table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related