How to set default values for MySQL JSON columns

Category:

Tutorials > Database Management Systems > MySQL > Administration

MySQL has been making huge progress on the JSON/NoSQL front as well ie. supporting many of the JSON access and manipulation features and operations. In this how-to tutorial, I am going to show you how to add a new JSON column with default value in an existing MySQL table using the ALTER TABLE statement. And, how to create a new table using CREATE TABLE with JSON column along with its default value.

I am using the following version of MySQL for this example:

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.20    |
+-----------+
1 row in set (0.00 sec)

Adding a new column to an existing table:

Let's assume you are adding a new column that uses JSON data type to an already existing table `user`. Here is how you’d go about doing it.

ALTER TABLE `user` 
ADD COLUMN `setting` JSON NOT NULL DEFAULT ( JSON_OBJECT() );

Creating a new table:

In case you are creating a new table, you can do so by running a CREATE statement like below:

CREATE TABLE `test` (
  `id` int NOT NULL,
  `t` int DEFAULT NULL,
  `name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `setting` json NOT NULL DEFAULT (json_object()),
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Lets confirm if everything worked as we planned or not:

Here is the table definition after the change.

mysql> SHOW CREATE TABLE test;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                    |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int NOT NULL,
  `t` int DEFAULT NULL,
  `name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `setting` json NOT NULL DEFAULT (json_object()),
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Table is empty, lets add a new record and confirm the default value for JSON column

mysql> SELECT * FROM test;
Empty set (0.00 sec)

mysql> INSERT INTO test (id, name) VALUES (1, 'geeks');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+----+------+-------+---------+
| id | t    | name  | setting |
+----+------+-------+---------+
|  1 | NULL | geeks | {}      |
+----+------+-------+---------+
1 row in set (0.00 sec)

Did this tutorial help a little? How about buy me a cup of coffee?

Please feel free to use the comments form below if you have any questions or need more explanation on anything. I do not guarantee a response.

IMPORTANT: You must thoroughy test any instructions on a production-like test environment first before trying anything on production systems. And, make sure it is tested for security, privacy, and safety. See our terms here.