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

Answer 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.

Answer 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

Answer 3

According to the laravel docs:

$table->json('movies')->default(new Expression('(JSON_ARRAY())'));

The default modifier accepts a value or an Illuminate\Database\Query\Expression instance. Using an Expression instance will prevent Laravel from wrapping the value in quotes and allow you to use database specific functions. One situation where this is particularly useful is when you need to assign default values to JSON columns

https://laravel.com/docs/8.x/migrations#default-expressions