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

Question

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

Might want to look at [triggers](https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html) here, alter table does things to the columns once and not when an event occurs (such as insert, update etc.) — Can O' Spam, Apr 12 '23 at 14:47

user1191247 · Accepted Answer · 2023-04-12T17:20:18.297

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 ;

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

1 Answers1