PostgreSQL - Json data - Inserting into Target Table through Trigger

This is continuation to similar post.

I have a students_source table with a json column like this.

CREATE TABLE students_source (doc_id INT, doc_data JSON);

A row is inserted with doc_id = 201. The insert statement is:

INSERT INTO xereports.students_data(doc_id, doc_data) 
VALUES (201, '{
  "document_type": "students_report",
  "document_name": "students_report_202406.pdf",
  "data": {
    "grades": [
      {
        "grade_name": "5",
        "sections": {
          "5A_students": [
            {
              "student_name": "Arun",
              "avg_marks": 85,
              "rank": "AA+"
            },
            {
              "student_name": "Bala",
              "avg_marks": 70,
              "rank": "A+"
            }
          ],
          "5B_students": [
            {
              "student_name": "Chitra",
              "avg_marks": 86,
              "rank": "AA+"
            },
            {
              "student_name": "David",
              "avg_marks": 72,
              "rank": "A+"
            }
          ],
          "5C_students": [
            {
              "student_name": "Elango",
              "avg_marks": 88,
              "rank": "AA+"
            },
            {
              "student_name": "Fathima",
              "avg_marks": 74,
              "rank": "A+"
            }
          ]
        }
      },
      {
        "grade_name": "6",
        "sections": {
          "6A_students": [
            {
              "student_name": "Arun2",
              "avg_marks": 85,
              "rank": "AA+"
            },
            {
              "student_name": "Bala2",
              "avg_marks": 70,
              "rank": "A+"
            }
          ],
          "6B_students": [
            {
              "student_name": "Chitra2",
              "avg_marks": 86,
              "rank": "AA+"
            },
            {
              "student_name": "David2",
              "avg_marks": 72,
              "rank": "A+"
            }
          ],
          "6C_students": [
            {
              "student_name": "Elango2",
              "avg_marks": 88,
              "rank": "AA+"
            },
            {
              "student_name": "Fathima2",
              "avg_marks": 74,
              "rank": "A+"
            }
          ]
        }
      }
    ]
  }
}');

The requirement is that whenever such record is inserted, a subset of the data should be inserted into a target table. i.e., all the AA+ student details to be captured. Like this...

The structure of the target table is:

CREATE TABLE students_target (
  doc_id INT,
  document_type VARCHAR(500),
  document_name VARCHAR(500),
  grade_name VARCHAR(500),
  student_name VARCHAR(500),
  avg_marks int,
  rank_cd varchar(100) 
);

I am able to query the data from the source table.

select
    sd.doc_id,
    sd.doc_data ->> 'document_type' as document_type,
    sd.doc_data ->> 'document_name' as document_name,
    grade ->> 'grade_name' as grade_name,
    student->>'student_name' as student_name,
    student->>'avg_marks' as avg_marks,
    student->>'rank' as rank
from
    students_source sd,
    json_array_elements(doc_data->'data'->'grades') as grade,
    json_each(grade->'sections') as section(key, value),
    json_array_elements(section.value) as student
where
    student->>'rank' = 'AA+'
    and sd.doc_id = 201;

But while creating Trigger Function, I get error.

CREATE OR REPLACE FUNCTION student_data_triger_function() RETURNS TRIGGER AS
$BODY$
BEGIN
INSERT INTO students_target 
    (doc_id, document_type, document_name, grade_name, student_name, avg_marks, rank_cd)
VALUES(
    NEW.doc_id,
    NEW.doc_data ->> 'document_type',
    NEW.doc_data ->> 'document_name',
    grade ->> 'grade_name',
    student->>'student_name',
    student->>'avg_marks',
    student->>'rank'
from
    NEW sd,
    json_array_elements(NEW.doc_data->'data'->'grades') as grade,
    json_each(grade->'sections') as section(key, value),
    json_array_elements(section.value) as student
where
    student->>'rank' = 'AA+'
    )
       RETURN new;
END;
$BODY$
language plpgsql;

I get this error.

How to fix this?

Solution

The correct syntax is INSERT … SELECT …;, see https://www.postgresql.org/docs/current/sql-insert.html

No VALUES, no parenthesis.

INSERT INTO xereports.students_data_extract 
    (doc_id, document_type, document_name, grade_name, student_name, avg_marks, rank_cd)
SELECT
    NEW.doc_id,
    NEW.doc_data ->> 'document_type',
    NEW.doc_data ->> 'document_name',
    grade ->> 'grade_name',
    student->>'student_name',
    student->>'avg_marks',
    student->>'rank'
FROM
    json_array_elements(NEW.doc_data->'data'->'grades') as grade,
    json_each(grade->'sections') as section(key, value),
    json_array_elements(section.value) as student
WHERE
    student->>'rank' = 'AA+';