jsonpostgresqltriggersaggregate-functionsplpgsql

building a trigger function to process json data with postgres


I'm struggling while building a pl/pgsql function to process a json array.

The data I receive from the client is a list of answers to questions by a user that I store in a json type field called 'data' in my table experiences.

[
{"completed":false},
{"completed":false},
{"completed":false},
{"completed":true,"score":1,"answerList":["1"],"QuestionId":75,"startTime":"2016-01-09T16:40:06.153Z","clickNb":1,"endTime":"2016-01-09T16:40:07.844Z"},
{"completed":true,"score":1,"answerList":["1"],"QuestionId":76,"startTime":"2016-01-09T16:40:08.487Z","clickNb":1,"endTime":"2016-01-09T16:40:12.482Z"},
{"completed":true,"score":1,"answerList":["1"],"QuestionId":77,"startTime":"2016-01-09T16:40:13.042Z","clickNb":1,"endTime":"2016-01-09T16:40:17.689Z"}
]

I want to build a trigger function to add up all the scores and save the result in a field called accuracy_score. This is what I have build so far:

CREATE OR REPLACE FUNCTION calculate_accuracy() RETURNS TRIGGER AS $$
DECLARE 
    dataarray json;

BEGIN
    dataarray := json_array_elements(New.data);
    UPDATE experiences SET accuracy_score = subquery.score 
    FROM (SELECT SUM(dataarray->>'score') from dataarray) AS subquery 
    WHERE challenge_id = New.challenge_id;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER calculate_accuracy
AFTER INSERT ON experiences
FOR EACH ROW EXECUTE PROCEDURE calculate_accuracy();

I get the following error message when I try to run it:

ERROR: query "SELECT json_array_elements(New.data)" returned more than one row CONTEXT: PL/pgSQL function calculate_accuracy() line 6 at assignment

I don't find how to fix this. Help more than welcome on this!


Solution

  • If you are going to change a value in an inserted row, use BEFORE TRIGGER and change the record NEW in a trigger function:

    CREATE OR REPLACE FUNCTION calculate_accuracy() RETURNS TRIGGER AS $$
    BEGIN
        NEW.accuracy_score = (
            SELECT sum((e->>'score')::int)
            FROM json_array_elements(NEW.data) e);
        RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;
    
    CREATE TRIGGER calculate_accuracy
    BEFORE INSERT ON experiences
    FOR EACH ROW EXECUTE PROCEDURE calculate_accuracy();
    

    Test:

    insert into experiences (challange_id, data)
    values
    (1, 
    '[
    {"completed":false},
    {"completed":false},
    {"completed":false},
    {"completed":true,"score":1,"answerList":["1"],"QuestionId":75,"startTime":"2016-01-09T16:40:06.153Z","clickNb":1,"endTime":"2016-01-09T16:40:07.844Z"},
    {"completed":true,"score":1,"answerList":["1"],"QuestionId":76,"startTime":"2016-01-09T16:40:08.487Z","clickNb":1,"endTime":"2016-01-09T16:40:12.482Z"},
    {"completed":true,"score":1,"answerList":["1"],"QuestionId":77,"startTime":"2016-01-09T16:40:13.042Z","clickNb":1,"endTime":"2016-01-09T16:40:17.689Z"}
    ]'::json)
    returning challange_id, accuracy_score;
    
     challange_id | accuracy_score 
    --------------+----------------
                1 |              3
    (1 row)