Mysql set default value to a json type column

Question

I heard that mysql version prior to 8.0.13 accept default value for json type column, so I using the cmd:

ALTER TABLE templates CHANGE COLUMN values JSON NOT NULL DEFAULT '{}' ;

but receive error:

Error Code: 1101. BLOB, TEXT, GEOMETRY or JSON column 'values' can't have a default value

So how do I fix it?

I'm using mysql version 8.0.19 and client tool Workbench

Solution 1

From version 8.0.13 onwards, the documentation says (emphasis is mine):

The BLOB, TEXT, GEOMETRY, and JSON data types can be assigned a default value only if the value is written as an expression, even if the expression value is a literal.

You can make your default an expression by surrounding the literal value with parentheses:

ALTER TABLE templates CHANGE COLUMN values JSON NOT NULL DEFAULT ('{}') ;

Or:

ALTER TABLE templates CHANGE COLUMN values JSON NOT NULL DEFAULT (JSON_OBJECT()) ;

---

Prior to version 8.0.13 of MySQL, it was not possible to set a default value on a JSON column, as the 8.0 documentation points out a few paragraphs later :

The BLOB, TEXT, GEOMETRY, and JSON data types cannot be assigned a default value.

Solution 2

MySql syntax is a bit different than Oracle/Postgres, hence to make say JSON_Array as default, the query would be -

ALTER TABLE table_name ALTER column_name SET DEFAULT (JSON_ARRAY());

Further reference here

Comments

[sgruetter]

Note that this only works as of version 8.0.13 - before, e.g. in version 5.7, defaults for JSON are not possible. ("The BLOB, TEXT, GEOMETRY, and JSON data types cannot be assigned a default value.")

[WEBjuju]

should have read the comment. if you try this in Aurora MySQL 5.7 the result is error message: BLOB, TEXT, GEOMETRY or JSON column 'your_col_name' can't have a default value. (Error #1101)

[Narxx]

Actually, I have the same error on mysql 8.0.28 as well (can't have a default value #1011)