1

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.

2
  • Specify precise MySQL version. Show exact GDN_AUDIT_TRAIL's CREATE TABLE script. Commented Oct 1, 2020 at 10:03
  • Please also include the error you received. Welcome to Stack Overflow! Commented Oct 3, 2020 at 12:24

1 Answer 1

2

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.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.