Trigger crashing due to invalid json path
Posted by: Nuno Donato
Date: March 09, 2020 06:28AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Trigger crashing due to invalid json path
1192
March 09, 2020 06:28AM


Sorry, you can't reply to this topic. It has been closed.

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.