Search code examples
mysqlsqljsonstringalter-table

Add Column with Default value from existing JSON Column

I have a table with name GDN_AUDIT_TRAIL having JSON column with name

DETAILS.

I want to add new column SOLUTION_ID in this table, so that the default value of this column should be one of the field from DETAILS JSON Column. I have tried below query which is giving error -

ALTER TABLE GDN_AUDIT_TRAIL 
ADD COLUMN SOLUTION_ID VARCHAR(50) DEFAULT JSON_UNQUOTE(JSON_EXTRACT(DETAILS,'$.SolutionID')) AFTER ACTION_TYPE;

Please let me know if this is possible.

Solution

  • You would need to surround the expression given as a default with parentheses for MySQL to properly understand it:

    alter table gdn_audit_trail 
    add column solution_id varchar(50) 
    default (json_unquote(json_extract(details,'$.SolutionID'))) after action_type; 
         -- ^ --                                            -- ^ --

    Note that using expresions in the DEFAULT clause requires MySQL 8.0.13. In earlier versions, only literal constants were allowed (with current_timestamp being the only exceptions). This is well explained in the documentation:

    Handling of Explicit Defaults as of MySQL 8.0.13

    The default value specified in a DEFAULT clause can be a literal constant or an expression. With one exception, enclose expression default values within parentheses to distinguish them from literal constant default values.

    [...]

    Handling of Explicit Defaults Prior to MySQL 8.0.13

    With one exception, the default value specified in a DEFAULT clause must be a literal constant; it cannot be a function or an expression.