Mysql, How do I add a computed column to compute the sum of a json column?

Column a contains a json array formatted like [1,4,3,6]

I want column b to be a sum of column a.

ALTER TABLE `T` ADD `b` int AS 
        (SELECT SUM(t.c) FROM JSON_TABLE(a, '$[*]' COLUMNS (c INT PATH '$')) AS t) NULL;

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT SUM(t.c) FROM JSON_TABLE(a, '$[*]' COLUMNS (c INT PATH '$')) AS t) NULL' at line 1

Solution

This section of the MySQL docs specifies the rules for generated columns. The two which seem most relevant here are:

Literals, deterministic built-in functions, and operators are permitted.

Subqueries are not permitted.

You could achieve "similar" functionality with a pair of triggers:

DELIMITER $$

CREATE TRIGGER `test_t_b_insert` BEFORE INSERT ON `t`
  FOR EACH ROW BEGIN
    SET NEW.b = (SELECT SUM(t.c) FROM JSON_TABLE(NEW.a, '$[*]' COLUMNS (c INT PATH '$')) AS t);
  END$$

CREATE TRIGGER `test_t_b_update` BEFORE UPDATE ON `t`
  FOR EACH ROW BEGIN
    IF NOT OLD.a <=> NEW.a THEN
        SET NEW.b = (SELECT SUM(t.c) FROM JSON_TABLE(NEW.a, '$[*]' COLUMNS (c INT PATH '$')) AS t);
    END IF;
  END$$

DELIMITER ;