Question 89179 in PostgreSQL Question posted in
The official documentation can be found here.

Failed parsing jsonb and automatic inserting into table – trigger function – Postgresql

Postgresql trigger function throws exception (failed jsonb parsing)

How can i fix it?

first table

CREATE TABLE document_templates (
                           id SERIAL PRIMARY KEY,
                           name VARCHAR(255) NOT NULL,
                           created_at TIMESTAMP NOT NULL,
                           version INT NOT NULL,
                           fields JSONB NOT NULL
);

related table

CREATE TABLE document_fields (
                                 id SERIAL PRIMARY KEY,
                                 document_template_id INTEGER NOT NULL REFERENCES document_templates(id),
                                 field_name VARCHAR(255) NOT NULL,
                                 field_type VARCHAR(255) NOT NULL,
                                 placeholder TEXT,
                                 default_value TEXT
);

trigger function:

CREATE OR REPLACE FUNCTION fill_document_fields() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO document_fields (document_template_id, field_name, field_type, placeholder, default_value)
    SELECT NEW.id, (f ->> 'name')::VARCHAR(255), (f ->> 'type')::VARCHAR(255), (f ->> 'placeholder')::TEXT, (f ->> 'default')::TEXT
    FROM jsonb_array_elements(NEW.fields) AS f;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER fill_document_fields_trigger
    AFTER INSERT ON document_templates
    FOR EACH ROW
EXECUTE PROCEDURE fill_document_fields();

ERROR!

[2023-03-29 17:19:40] [22023] ERROR: cannot extract elements from an object
[2023-03-29 17:19:40] Where: SQL statement "INSERT INTO document_fields (document_template_id, field_name, field_type, placeholder, default_value)
[2023-03-29 17:19:40] SELECT NEW.id, (f ->> 'name')::VARCHAR(255), (f ->> 'type')::VARCHAR(255), (f ->> 'placeholder')::TEXT, (f ->> 'default')::TEXT
[2023-03-29 17:19:40] FROM jsonb_array_elements(NEW.fields) AS f"
[2023-03-29 17:19:40] PL/pgSQL function fill_document_fields() line 3 at SQL statement

I inserted this data:

INSERT INTO document_templates (name, created_at, version, fields)
VALUES ('Example Document', NOW(), 1, '{"fields": [{"name": "Field 1", "type": "text", "placeholder": "Enter text here", "default": ""}, {"name": "Field 2", "type": "number", "placeholder": "Enter number here", "default": 0}]}'::JSONB);

and expected:

parsing jsonb column value and automatic insert parsed data into document_fields table

Tags:

2

Answers


  1. 0

    In your function fill_document_fields you have to get fields from json
    Replace NEW.fields to (NEW.fields->>'fields')::jsonb

    CREATE OR REPLACE FUNCTION fill_document_fields() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO document_fields (document_template_id, field_name, field_type, placeholder, default_value)
    SELECT NEW.id, (f ->> 'name')::VARCHAR(255), (f ->> 'type')::VARCHAR(255), (f ->> 'placeholder')::TEXT, (f ->> 'default')::TEXT
    FROM jsonb_array_elements((NEW.fields->>'fields')::jsonb) AS f;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
    Login or Signup to reply.
  2. 0

    You should insert only '[{"name":…}, {"name": "…}]'::JSONB into the fields column.

    You were using '{"fields": [{"name":…}, {"name": "…}]}'::JSONB. The trigger rightfully complains that one cannot get jsonb_array_elements from that {"fields": …} object.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search