MySQL and HeatWave

How to Populate JSON Columns

Mark Lewin
MySQL Curriculum Developer

While it was always technically possible to store JSON in MySQL database columns, it was never a particularly attractive option. JSON data is just text, so any string type of sufficient length will take it quite happily. However, getting your JSON into the database using this method is one thing, but getting it back out again in any useful format had to be the responsibility of your application.

All that changed in MySQL 5.7.8 with the introduction of a native JSON data type and a range of useful built-in functions that made JSON a first-class citizen of MySQL.

In this post, we'll have a look at some of the functions you can use to store JSON data. Later posts will cover retrieving and otherwise manipulating that data.

Imagine that we are an online retailer, selling a range of electronics equipment. We sell anything from top of the range TVs to toasters. Clearly all these items will have things in common: they will be associated with a manufacturer, for example, and have a sale price. However, not all attributes will be relevant to all product types. Toasters don't usually have an HDMI port and TVs aren't able to deal with frozen bread. We could always create a new table for each type of product, but what if a particular manufacturer starts producing a new range of toasters with a built-in digital radio? We would need to add a new column to our toasters table to differentiate those from the others, and this would quickly become painful. This is where having a flexible, schema-less data type like JSON could come in really handy.

Let's create a table called products that could store this sort of data:

mysql> CREATE TABLE `products` ( -> `id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT, -> `name` VARCHAR(100) NOT NULL, -> `manuf_id` INT UNSIGNED NOT NULL, -> `category_id` INT UNSIGNED NOT NULL, -> `price` DECIMAL(7,2) NOT NULL, -> `attributes` JSON NOT NULL, -> PRIMARY KEY(`id`) -> ); Query OK, 0 rows affected (#.## sec)

Now, let's add a product: the great new MP3 player from Wombat Technlogies, called the Wombat Pro Player. Here's what we know about the Wombat:

  • Manufacturer: Wombat Technologies (id 22 in the `manufacturers` table)
  • Category: MP3 players (id 331 in the `categories` table)
  • Price: $49.99
  • Attributes:
    • Storage: 8 GB
    • Size: 8 cm x 4 cm x 1 cm, weighs 32 g
    • Supported formats: MP3, WMA, FLAC, and AVI

To store the attributes, we can just pass in valid JSON as the field value in our INSERT statement:

mysql> INSERT INTO `products` (`name`, `manuf_id`, `category_id`, `price`, `attributes`) -> VALUES ( -> 'Wombat ProPlayer', -> 22, -> 331, -> 49.99, -> '{"storage_gb": 8, "size": {"height_cm": 8, "width_cm": 4, "depth_cm": 1, "weight_g": 32}, -> "formats": ["MP3", "WMA", "FLAC", "AVI"]}'); Query OK, 1 row affected (#.## sec)

We can then list the contents of the products table and see our JSON attributes.

mysql> SELECT * FROM products\G *************************** 1. row *************************** id: 1 name: Wombat ProPlayer manuf_id: 22 category_id: 331 price: 49.99 attributes: {"size": {"depth_cm": 1, "weight_g": 32, "width_cm": 4, "height_cm": 8}, "formats": ["MP3", "WMA", "FLAC", "AVI"], "storage_gb": 8} 1 row in set (#.## sec)

See how the order of JSON fields in the attributes column has changed from the order in which we provided them? MySQL sorts the keys and removes any extraneous whitespace between the key/value pairs. If you specified the same key twice, MySQL would only retain the first value you provided for that key. All these steps are part of MySQL's normalization process for JSON.

You must provide valid JSON, otherwise you'll get an error from MySQL. You can quickly validate any JSON document by using JSONLint. Or, you can pass some JSON to the JSON_VALID() function in MySQL, which will return 1 if the JSON validates correctly.

But providing JSON field values in this way can be very fiddly and error-prone, so you might want to use the MySQL JSON_OBJECT() function to lay out the JSON for you. The JSON_OBJECT() function accepts keys and their corresponding values as parameters:

mysql> INSERT INTO `products` (`name`, `manuf_id`, `category_id`, `price`, `attributes`) -> VALUES( -> 'Wombat ProPlayer', -> 22, -> 331, -> 49.99, -> JSON_OBJECT( -> "storage_gb", -> 8, -> "size", -> JSON_OBJECT( -> "height_cm", -> 8, -> "width_cm", -> 4, -> "depth_cm", -> 1, -> "weight_g", -> 32 -> ), -> "formats", -> JSON_ARRAY("MP3", "WMA", "FLAC", "AVI") -> ) -> ); Query OK, 1 row affected (#.## sec)

Note how we've used JSON_OBJECT() twice here: once for the main JSON attributes object, and once for the nested "size" object. We've also used another utility function, JSON_ARRAY(), to create and populate the formats field.

Another useful function is JSON_MERGE(). This takes two or more arrays and consolidates all their elements into a single array. Or, if you pass it objects instead of arrays, you get back a single object that includes the properties of all the objects you passed to it. Note that JSON_MERGE() behaves differently from JSON_OBJECT() with multiple keys of the same name. It will only use the key once in the output, but its value will be an array containing all the values specified by keys of that name:

mysql> SELECT JSON_MERGE( -> JSON_OBJECT("a", 1, "b", 2, "c", 3), -> JSON_OBJECT("c", 4, "d", 5) -> ); +------------------------------------------------------------------------------+ | JSON_MERGE(JSON_OBJECT("a", 1, "b", 2, "c", 3), JSON_OBJECT("c", 4, "d", 5)) | +------------------------------------------------------------------------------+ | {"a": 1, "b": 2, "c": [3, 4], "d": 5} | +------------------------------------------------------------------------------+ 1 row in set (#.## sec)

Hopefully this post has outlined some of the methods you can use to get your JSON data into MySQL. In later posts we'll look at other MySQL functions that can help you search and manipulate your JSON data.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.