Trigger crashing due to invalid json path
Hi folks.
I did a trigger to keep a table updated with a JSON field that stores key-values fetched from a "master" table.
Doing my initial import (insert into t1 from select(... from t2)) it crashes after a while with the message
Invalid JSON path expression. The error is around character position 10.
I'm building my JSON path by concatenating a string and there are no strange characters in the variables names.
Any clues on what I can do to fix/debug this?
here's my trigger
CREATE DEFINER=`root`@`localhost` TRIGGER `datatable_insert` AFTER INSERT ON `datatable` FOR EACH ROW BEGIN
DECLARE mystr char;
IF exists (SELECT data,inst, eq FROM laravel.datastore WHERE `data`=NEW.`data` and inst=NEW.inst and eq=NEW.eq) THEN
SET @mystr = CONCAT("$.",NEW.var);
UPDATE laravel.datastore SET var=JSON_SET(var,@mystr,NEW.val) WHERE `data`=NEW.data and inst=NEW.inst and eq=NEW.eq;
ELSE
INSERT INTO laravel.datastore (data,inst,eq,var) VALUES (NEW.data,NEW.inst,NEW.eq,JSON_OBJECT(NEW.var,NEW.val));
END IF;
END
thanks in advance
Subject
Views
Written By
Posted
Trigger crashing due to invalid json path
1298
March 09, 2020 06:28AM
Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.