How to Use JSON Data Fields in MySQL Databases

In my article SQL vs NoSQL: The Differences I mentioned the line between SQL and NoSQL databases has become increasingly blurred with each camp adopting features from the other. MySQL 5.7 InnoDB databases and PostgreSQL 9.4 both directly support JSON document types in a single field. In this article we'll examine MySQL's JSON implementation in more detail.

(PostgreSQL supported JSON before version 9.4 and any database will accept JSON documents as a single string blob. However, both now directly support validated JSON data in real key/value pairs rather than a basic string.)

Just Because You Can Store JSON…

…it doesn't follow you should.

Normalisation is a technique used to optimize the database structure. The First Normal Form (1NF) rule governs that every column should hold a single value -- which is clearly broken by storing multi-value JSON documents.

If you have clear relational data requirements, use appropriate single-value fields. JSON should be used sparingly as a last resort. JSON value fields cannot be indexed so avoid using it on columns which are updated or searched regularly. In addition, fewer client applications support JSON and the technology is newer and possibly less stable than other types.

That said, there are good JSON use-cases for sparsely-populated data or custom attributes.

Create a Table With a JSON Field

Consider a shop selling books. All books will have an ID, ISBN number, title, publisher, number of pages and other clear relational data. Presume we want to add any number of category tags to each book. We could achieve this in SQL using:

1. a tag table which stored each tag name against a unique ID, and
2. a tagmap table with many-to-many records mapping book IDs to tag IDs

It'll work but it's cumbersome and considerable effort for a minor feature. Therefore, we'll define a tags JSON field in our MySQL database's book table:

[code language=sql]
CREATE TABLE `book` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(200) NOT NULL,
`tags` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
[/code]

Continue reading %How to Use JSON Data Fields in MySQL Databases%

by Craig Buckler via SitePoint