MySQL 5.7 Introduces a JSON Data Type

There's a new JSON data type available in MySQL 5.7 that I've been playing with. I wanted to share some examples of when it's useful to have JSON data in your MySQL database and how to work with the new data types (not least so I can refer back to them later!)

MySQL isn't the first database to offer JSON storage; the document databases (such as MongoDB, CouchDB) work on a JSON or JSON-ish basis by design, and other platforms including PostgreSQL, Oracle and SQL Server also have varying degress of JSON support. With such wide adoption as MySQL has, the JSON features are now reaching a new tribe of developers. Continue reading

Pretty-Printing JSON with Python's JSON Tool

Today's quick tip is something that was widely retweeted after my "Debugging HTTP" talk at the ever-fabulous WhiskyWeb conference last weekend. When working with JSON on the commandline, here's a quick tip for showing the JSON in a nicer format:

curl | python -mjson.tool

You need python installed, but the JSON extension is probably included, and that's all you need for this tool. The result is something like:


You can also use this approach to present JSON data that has been captured to another file, for example, it's a handy trick that I use often when developing something with JSON as a data format. API: Bundles and Short URLs

I am a huge fan of and use their tools for a wide variety of different things. They recently did a big relaunch with some lovely new features, which are for the most part pretty good, but which are inaccessible in places. In particular, it seems that there aren't any short URLs for the bundles - which is annoying for me as I use that feature a lot!

To get around this, I used their API to make a page which lists my bundles, and creates shortlinks for each of them (once you've created a shortlink for a given URL once, just re-uses the same ones the next time you ask to shorten the same URL, so this is less silly than it sounds).

In case the code is helpful, I thought I'd share. Continue reading

Building a RESTful PHP Server: Output Handlers

This is the third installment in my series about writing a RESTful web service in PHP (the previous entries are about understanding the request and routing it. It is probably the last one but there are a few other things I'd like to cover such as error handling, so I might keep adding to it, especially if I get any particular requests or interesting questions in the comments. So far we've covered parsing requests to determine exactly what the user is asking for, and also looked at routing to a controller to obtain the data or perform the action required. This post gives examples of how to return the data to the client in a good way. Continue reading


disclaimer: I am not a client-side developer, and I don't write javascript. However I am committed to supplying useful APIs of all kinds, and JSONP falls into this category

Early in the development of the new Joind.In API, someone else started consuming the service to populate the javascript widgets they were making*. Since these scripts are intended to be used on many external pages, and they retrieve data from the API, cross-domain issues were a problem. Continue reading

PHP Returning Numeric Values in JSON

When I wrote about launching a prototype of a new API, quite a few people started to try it out. My friend David Soria Parra emailed me to point out that many of the numbers in the API were being returned as strings. He said:

It's just a standard problem of PHP REST services. When I try to access it with java I have to convert it over and over again to ints.

I did have a quick look at the PHP manual page for json_encode but I didn't see anything mentioning this. A few weeks later (my inbox is a black hole and it takes a while to process these things) I fell over a throwaway comment to an undocumented constant JSON_NUMERIC_CHECK, and I added the constant name to my todo list. In the time it took for me to actually get around to googling for this, some wonderful person updated the PHP manual page (this is why I love PHP) to include it as a documented option, and someone else had added a user contributed note about using it.

It turns out, this constant does exactly what I need. Here's a simple use case:

echo json_encode(array('event_id' => '603'));
echo json_encode(array('event_id' => '603'), JSON_NUMERIC_CHECK);

and the output:


There are probably some situations in which you don't want all your looks-like-a-number data to be returned as a number, but for now it seems to be a good fit for


This is a quick outline on working with JSON from PHP, which is actually pretty simple to do. This post has some examples on how to do it and what the results should look like. JSON stands for JavaScript Object Notation, and is widely used in many languages (not just JavaScript) for serialisation. It is particularly popular for use in web services.

Writing JSON From PHP

Imagine we have a multidimensional array in PHP that looks something like this:

$menu['starter'] = array( "prawn cocktail",
                          "soup of the day");
$menu['main course'] = array( "roast chicken",
                              "fish 'n' chips",
                              "macaroni cheese");
$menu['pudding'] = array( "cheesecake",
                          "treacle sponge");

echo json_encode($menu);

The output of this script looks like this:

{"starter":["prawn cocktail","soup of the day"],"main course":["roast chicken","fish 'n' chips","macaroni cheese"],"pudding":["cheesecake","treacle sponge"]}

This is pretty typical of a JSON output string - you can see the curly brackets to enclose the whole thing, then some square brackets to show the nesting levels within the key/value formats. JSON is an ideal format for many applications because it is easy to understand and debug, its quite concise, and many languages have built-in support just like PHP.

Reading JSON Data From PHP

Once we've serialised the string, we might want to unserialise it again - and the PHP code for that is every bit as simple as the previous example, except that we use the function json_decode() instead of json_encode(). I've set the output of the previous script as the input to this one:

$json = '{"starter":["prawn cocktail","soup of the day"],"main course":["roast chicken","fish \'n\' chips","macaroni cheese"],"pudding":["cheesecake","treacle sponge"]}';


This decodes the string and then dumps it using print_r() - the output of my script looked like this:

stdClass Object
[starter] => Array
[0] => prawn cocktail
[1] => soup of the day

[main course] => Array
[0] => roast chicken
[1] => fish 'n' chips
[2] => macaroni cheese

[pudding] => Array
[0] => cheesecake
[1] => treacle sponge


Note that the data isn't identical to how it looked when it went in - JSON can't distinguish between arrays and objects, and doesn't retain information about data types. So its perfect for a web service where we just want to convey the information, but may be too loose for other applications.

The examples here were taken from a talk I give about consuming web services - you can see all the slides on slideshare. If you have any additions or alternatives, leave a comment!