5

I am trying to write a Postgres trigger to unnest an array field before inserting or updating a new row. E.g.

SELECT
unnest(something)
FROM NEW

However, this seems to lead to an error:

relation 'new' does not exist

How can I use the NEW row inside of the trigger function, in such a way that allows me to unnest an array field for further processing?

CREATE TABLE statements

Below are examples of the table structures:

CREATE TABLE parent_table (
    id uuid NOT NULL,
    jsonb_array_field jsonb[] NOT NULL DEFAULT '{}'::jsonb[],
    CONSTRAINT "parent_table_pkey" PRIMARY KEY (id),
);

CREATE TABLE many_to_one_table (
  id serial primary key,
  parent_table_id uuid references parent_table(id),
  subfield_a text,
  subfield_a text
);

TRIGGER function

Here is the essence of the TRIGGER function:

CREATE OR REPLACE FUNCTION unnest_and_normalize_json() RETURNS TRIGGER AS 
$body$
begin
    if NEW.jsonb_array_field is null then
        raise exception 'jsonb_array_field is null';
    else
        insert into 
            many_to_one_table(subfield_a, subfield_b)
            select
                parent_table_id, -- this is to be the ForeignKey
                json_data->>'subfieldA' as subfield_a,
                json_data->>'subfieldB' as subfield_b
            from (
                select 
                    id, -- need ID for ForeignKey relationship
                    unnest(jsonb_array_field) as json_data
                    from new
            ) as unnested_json;
    end if;
    RETURN new;
end;
$body$ 
LANGUAGE plpgsql;

TRIGGER statement

The trigger statement can be run either before or after INSERT and UPDATE, so long as the data are mirrored in the 'migration' table.

CREATE TRIGGER unnest_and_normalize_json_on_insert AFTER INSERT ON parent_table
FOR EACH ROW EXECUTE PROCEDURE unnest_and_normalize_json();

CREATE TRIGGER unnest_and_normalize_json_on_update AFTER UPDATE ON parent_table
FOR EACH ROW EXECUTE PROCEDURE unnest_and_normalize_json();

ForeignKey clarification We are trying to transition this part of our data model to use a ForeignKey relationship rather than JSON field. The trigger is intended as a temporary step to ensure the normalized_table has data going forward, while we backfill/migrate data from older records.

Improve this question
9
  • 4
    Please provide the CREATE TABLE statement and the whole trigger code. Commented Jan 22, 2019 at 19:36
  • What is producing the array to begin with? This sounds like a half-baked optimization because you don't want to run multiple insert statements? Commented Jan 22, 2019 at 21:48
  • The array is coming from backend code. We have a lot of JSONB / JSONB[] columns in our database, which I would like to move to a more relational model. Commented Jan 23, 2019 at 9:16
  • jsonb[] almost never makes sense. It's much better to use jsonb and store a proper JSON array inside the json value Commented Jan 23, 2019 at 9:34
  • Duly noted. I can't change the past decision here, but am trying to migrate the data to a new table moving forward. Commented Jan 23, 2019 at 9:53

1 Answer 1

Reset to default
8

NEW and OLDin trigger functions are records - each only defined where applicable. (So no OLD in an INSERT trigger, for instance.) You can perform ROW operations on them. Reference nested columns with dot notation (like table-qualified column names) like they were tables included in the FROM list. NEW and OLD are visible almost everywhere in SQL DML statements in a trigger function. (Except dynamic SQL with EXECUTE.)

To treat NEW as actual table you'd have to transform it first - which is typically not necessary. Like: (SELECT NEW.*). For a meaningful use case see chapter 3. in this related answer:

Your basic trigger function burns down to:

CREATE OR REPLACE FUNCTION unnest_and_normalize_json()
  RETURNS trigger AS 
$func$
BEGIN
   INSERT INTO many_to_one_table (parent_table_id, subfield_a, subfield_b)
   SELECT NEW.id
        , ja->>'subfieldA' AS subfield_a  -- column alias only for documentation
        , ja->>'subfieldB'
   FROM   unnest(NEW.jsonb_array_field) ja;  -- produces a derived table

   RETURN NEW; -- optional for AFTER trigger
END
$func$  LANGUAGE plpgsql;

This was just noise:

if NEW.jsonb_array_field is null then ...

The column jsonb_array_field is defined as:

jsonb_array_field jsonb[] NOT NULL DEFAULT '{}'::jsonb[],

It's an AFTER trigger, the NOT NULL constraint would already have raised an exception for NULL in jsonb_array_field at this point.

You can merge your two triggers into one:

CREATE TRIGGER unnest_and_normalize_json_on_insert
AFTER INSERT OR UPDATE ON parent_table  -- !
FOR EACH ROW EXECUTE PROCEDURE unnest_and_normalize_json();

That said, you'd have to do a lot more to cover UPDATE and DELETE properly. Similar to this one (chapter The dark side):

Improve this answer
6
  • As requested, I have added the table definition, trigger, and trigger function to the original question. The goal here is a rolling migration, so we can deprecate this jsonb[] field with minimal downtime or data inconsistency. Commented Jan 23, 2019 at 10:05
  • I will also need to get the id field from the NEW row while unnesting the JSON data, so that I can create a ForeignKey relationship. Commented Jan 23, 2019 at 10:33
  • I updated accordingly. Details about ID are still fuzzy. Commented Jan 23, 2019 at 17:55
  • Thanks for your help and good humor. I have added a CREATE TABLE statement example in the question for the many_to_one_table. Commented Jan 23, 2019 at 19:44
  • We don't currently DELETE rows from the parent_table to my knowledge. For the UPDATE case, I was thinking to DELETE matching rows (by ForeignKey on UUID field) and re-creating them from the jsonb_array_field, to prevent duplicates. Commented Jan 23, 2019 at 19:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.