Getting Started With MySQL & JSON on Windows

MySQL is getting native support for JSON.  This blog post will show you how to quickly get the MySQL server with these new features running on your Windows rig and how to write a small C# program in Visual Studio 2015 that stores a JSON document using the new native JSON data type.

Schema or Schemaless

The upcoming 5.7 version of MySQL introduces a ton of new features, some of which I am quite excited about—in particular the native JSON support and Generated Columns. I think these can be very powerful for rapid development where the database is constantly changing as I add features and refactor existing ones. With the introduction of the new JSON data type I can combine schema and schemaless data.

Schema bound or schemaless with with ACID features, combined with transparent and easy data model changes? Yes, thank you, both please.


Installing Docker on Windows

The first step is to get the JSON labs release version of the MySQL server running on our computer. The easiest way is to have Docker do this for us. If you already have Docker installed (or Boot2Docker) then you can skip this step.


Installing the MySQL JSON Labs Release

Now that we have Docker installed, lets start it up. Simply double click the new desktop icon. This will then take us into a shell.



Now install the MySQL JSON Labs release using some Docker magic:

You can then use this command to verify that the server is up and running:

Which should read something like this:


Connecting to the Server Using the MySQL Client

Let’s then start a bash process inside the same container that the new MySQL server is running in. This will give us access to the mysql command-line client which we can then use to interact with the MySQL server.

At the Docker prompt enter docker exec -it ml bash, this give us a bash shell inside the container. From here enter mysql -p to connect to the MySQL server. The password is ‘my-secret-pw‘ or whatever you chose at install.


Trying out the New Native JSON Type

All righty then, finally we can play around with the new JSON type. It has a lot more to offer than simply storing JSON data, heck one could do that in a TEXT/BLOB or VARCHAR/CHAR type. The JSON data type enforces validation and fast look ups of sub-elements inside of the JSON document.



Exposing the Server Port to the OS

We now have a MySQL server running on a virtual Linux server inside VirtualBox. The problem is that this MySQL server is living inside a docker container completely cut off from any outside access, but we want to interact with it from the our Windows host. So, what we need to do is to tell Docker to expose the server port to VirtualBox, and then tell VirtualBox to expose that port to Windows.

We already did the first step by using the “-p 3306:3306” parameter when we installed the mysql/mysql-labs:5.7.7-json container.  Read more about docker run -p if you are interested in knowing all of the gory details.

The next step requires starting VirtualBox.  We have exposed the server port in Docker to VirtualBox, now we need to tell VirtualBox to pass that port along to Windows. You should have a start icon on the desktop after installing Boot2Docker.

Go to Settings -> Network -> Port forwarding and add the following rule:

This is a good time to open a command shell and check if you see port 3306 being listened on.


Create a Project in Visual Studio 2015

So in case you haven’t been paying attention—Microsoft has released a free and fully featured version of their IDE which I recommend you get if you’re even remotely interested in developing on the Windows platform (or even for mobile and Web development).

Head over to and grab it.  I use the community edition.

Once you have it installed, then create a new project called JsonTest (for example), and to keep it simple choose a project of type ‘Console Application’.


Add MySQL Connector/Net to the Project

Now we want to communicate with the MySQL server that is running in our Docker container from our C# program, so what we need is MySQL Connector/Net, this is a pure C# library for exactly this purpose.

  1. Connector/Net ReferenceIn Solution Explorer, right click on ‘References’ to bring up the menu
  2. Choose “Manage nuget packages” from the menu.
  3. Type in ‘mysql’ in the search bar
  4. Choose the ‘MySql.Data ADO.Net driver for MySQL’.
  5. Press the install button


C# Code for Using the New JSON Data Type

This is a simple example program of how to create a table with a JSON type column, INSERT a couple of JSON Documents, and then finally to read them back from the database.

Notice line 36, here I do a small trick to avoid getting MySqlException ‘Unknown data type’, because this is a Labs release that contains functionality which is slightly ahead of the current generation of connectors. So to work around the fact that the connector is clueless about the new JSON type, I CAST the type to a CHAR(40) column in the SELECT statement.  

Copy & paste the program below into your own project and hit CTRL-F5 to run it:

You should then get the following result:

As you can see, we’ve successfully created a table in MySQL 5.7 which has a JSON column, we’ve then INSERTED two JSON Documents into that table, and finally we’ve queried the table to get JSON Documents back.

That’s it for now. I hope that this brief introduction was helpful! Please stay tuned as we add additional helpful information regarding working with JSON in MySQL 5.7. As always, THANK YOU for using MySQL!

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.