MySQL: JSON [Data Type]

JSON data type is introduced in MySQL 5.7.8. Native JSON data type is supported as per the definition of RFC 7159.

In the older version of MySQL, we used to encode JSON data to a string and save that string in a TEXT or BLOB type column. But in that case, the column capability becomes very limited.

• The JSON column allows us to store unstructured data in a relational database.
• JSON type column provides automatic validation of JSON data when saved in the column.
• Data is stored in an efficient way when saved in a JSON column instead of encoding and saving in a TEXT column. As the JSON data is saved in binary format.
• Efficient reading and writing of data, as no explicit parsing(encoding/decoding) is required for the data.
• Searching and/or fetching data(especially in the nested deep part of JSON) is efficient, as MySQL will be able to look into the JSON key/value (saved in binary format internally) directly and perform operations on that.

Define JSON Field

Let’s create a table for “customer” and set column “address” of type JSON, so that there is no strict rule for the address and any format/structure of address can be saved in that.

CREATE TABLE `customer` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `company` VARCHAR(100) DEFAULT NULL,
  `address` JSON DEFAULT NULL,  /* This address field is of type JSON */
  PRIMARY KEY (`id`)
) ENGINE=INNODB

Let’s insert data in the table-

INSERT INTO `customer` (`name`, `email`, `phone`, `company`, `address`) 
VALUES('CA','ca@a.com','123456','ACOM','{"city": "Tallahassee", "state": "Florida", "street": "3198 Morgan Street", "zipcode": 32301, "state_code": "FL"}');

To check the validity of JSON data we can use JSON_VALID function like below-

SELECT JSON_VALID('{"city": "Tallahassee", "state": "Florida", "street": "3198 Morgan Street", "zipcode": 32301, "state_code": "FL"}') AS is_address_valid_json;

Output of the above validation check code will be as below-

is_address_valid_json
1

JSON_VALID returns “1” (one) for a valid JSON, and returns “0” (zero) otherwise.

We can construct a JSON object for MySQL using the JSON_OBJECT function-

SELECT JSON_OBJECT("city", "Tallahassee", "state", "Florida", "zipcode", 32301);

This will construct a JSON object and following output will be generated-

JSON_OBJECT(“city”, “Tallahassee”, “state”, “Florida”, “zipcode”, 32301)
{“city”: “Tallahassee”, “state”: “Florida”, “zipcode”: 32301}

We can use JSON_ARRAY method to construct an array, like below-

SELECT JSON_ARRAY("item 1", "item 2", "item 3", "last item");

JSON_ARRAY(“item 1”, “item 2”, “item 3”, “last item”)
[“item 1”, “item 2”, “item 3”, “last item”]

We can combine these functions to construct our desired JSON data-

INSERT INTO `customer` (`name`, `email`, `phone`, `company`, `address`) 
VALUES('CA','ca@a.com','123456','ACOM', JSON_OBJECT("city", "Tallahassee", "state", "Florida", "zipcode", 32301, "customitem", JSON_ARRAY("item 1", "item 2", "item 3", "last item")));

The generated JSON data for the address column will be-

{"city": "Tallahassee", "state": "Florida", "zipcode": 32301, "customitem": ["item 1", "item 2", "item 3", "last item"]}

Insert some more rows-

INSERT INTO `customer` (`name`, `email`, `phone`, `company`, `address`) VALUES('CB','db@b.com','125874','BCOM','{\"city\": \"Miami\", \"state\": \"Florida\", \"street\": \"2365 Poplar Lane\", \"zipcode\": 33128, \"state_code\": \"FL\"}');
INSERT INTO `customer` (`name`, `email`, `phone`, `company`, `address`) VALUES('CUC','cuc@c.com','998787','CCOM','{\"city\": \"Birmingham\", \"state\": \"Alabama\", \"street\": \"1501 Petunia Way\", \"zipcode\": 35215, \"state_code\": \"AL\"}');

Default Value

MySQL version older than 8.0.13 can have only a NULL default value for a JSON-type column.

Storage Requirement

JSON column size is limited by the system variable “max_allowed_packet”. When MySQL processes the data in memory, the limit does not apply. This limit applies when the data is saved in the server storage.

JSON column storage size requirement is similar to the LONGTEXT or LONGBLOB type column.

For the storage of additional metadata, binary encoding info, and dictionary(for JSON key/value lookup), a JSON type column would need an additional 4 bytes to 10 bytes of storage space.

We can check the size used or to be used by JSON column using MySQL function “JSON_STORAGE_SIZE”. To demonstrate this, let’s consider the following address-

{
    "street": "3198 Morgan Street",
    "city": "Tallahassee",
    "state_code": "FL",
    "state": "Florida",
    "zipcode": 32301
}

To check the size that will be used to store this data we can use the following-

SELECT JSON_STORAGE_SIZE('{"street":"3198 Morgan Street","city":"Tallahassee","state_code":"FL","state":"Florida","zipcode":32301}');

Result of this query will be-

JSON_STORAGE_SIZE(‘{“street”:”3198 Morgan Street”,”city”:”Tallahassee”,”state_code”:”FL”,”state”:”Florida”,”zipcode”:32301}’)
114

This means 114 bytes will be used to store this data in a JSON column.

This method can be used to check existing data in database JSON column, like below-

select json_storage_size(existing_json_type_column_name) from table_name_that_has_json_type_column);

Indexing JSON Column

We can not add an index to the JSON column, so the full JSON column can not be indexed.

But the elements inside a JSON column can be indexed. We need to define a generated column for a specific element inside the JSON, then we can define an index for that generated column.

Say, we wan to add an index for the zipcode data for the “address” column (which is a JSON field), we can do it like below-

CREATE TABLE `customer` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `company` VARCHAR(100) DEFAULT NULL,
  `address` JSON DEFAULT NULL,  /* This address field is of type JSON */
  `address_zipcode` INT GENERATED ALWAYS AS (address->"$.zipcode"), /* Generated column from JSON type of field */
  PRIMARY KEY (`id`),
  INDEX `address_zip_index` (`address_zipcode`)
);

Here are the methods we can use to create/modify/search JSON type columns in MySQL-