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.


Name
Email address
Website
Comments
	Notify me of follow-up comments via email.

How to set default values for MySQL JSON columns

Category:

Adding a new column to an existing table:

Creating a new table:

tags cloud

popular searches

Comments (write a comment):

leave a comment

Similar Tutorials:

Tutorials in 'Database Management Systems > MySQL' (more):

Recent Tutorials: