Using PHP's JSON_ENCODE with the MySQL JSON Data Type

Since I have been presenting on MySQL's JSON data type, I have had questions on how to encode the JSON document into a database column. It has to be a VALID JSON document or the MySQL server will reject it. JSON is a way of storing data for interchange and it consists of two ways of storing data -- key/value pairs called objects and an ordered list of values called an array. To add to this every programming language has its own definitions of what is an object or what is an array, especially PHP. In my early experiments I had a hard time figuring out the various functions needed and am glad to report I was over complicating things.

So lets us start with the basics:

From JSON.ORG we are told this about objects:

An object is an unordered set of name/value pairs. An object begins with { (left brace) and ends with } (right brace). Each name is followed by : (colon) and the name/value pairs are separated by , (comma).

And this about arrays:

An array is an ordered collection of values. An array begins with [ (left bracket) and ends with ] (right bracket). Values are separated by , (comma).

And of course, a value is defined as:

A value can be a string in double quotes, or a number, or true or false or null, or an object or an array. These structures can be nested.

JSON's power is in the ability to store keys and values. Most of the example docs you will find are collections of objects with some arrays and other objects within them.

Objectifying Objects

Time to create an object. Lets take an associative array like so:

$my_array = array('a' => 1, 'b' => 2, 'c'=3);

We can send this through PHP's JSON_ENCODE function. And the output is:

{"a": 1, "b": 2, "c": 3}

The object begins and ends with braces, the name is separated from the value by a colon, ans pairs are separated by commas. So this is a valid JSON document. We can wrap that into single quotes and insert it into MySQL.

mysql> INSERT INTO foobar (mydata) VALUES ('{"a": 1, "b": 2, "c": 3}');
Query OK, 1 row affected (0.01 sec)

mysql> 

So PHP's JSON_ENCODE works with this simple object.

Array Array

This time we will use an array to build a JSON array.

$my_other_array  = array(1,2,'c');

And the output from PHP's JSON_ENCODE is:

[1,2,"c"]

And again wrapping that in single quotes lets us put it into our JSON column.

mysql> INSERT INTO foobar (mydata) VALUES ('[1,2,"c"]');
Query OK, 1 row affected (0.00 sec)

mysql> 

But what about a much more complex document? (Thanks to http://www.sitepoint.com/youtube-json-example/)

{"apiVersion":"2.0",
 "data":{
    "updated":"2010-01-07T19:58:42.949Z",
    "totalItems":800,
    "startIndex":1,
    "itemsPerPage":1,
    "items":[
        {"id":"hYB0mn5zh2c",
         "uploaded":"2007-06-05T22:07:03.000Z",
         "updated":"2010-01-07T13:26:50.000Z",
         "uploader":"GoogleDeveloperDay",
         "category":"News",
         "title":"Google Developers Day US - Maps API Introduction",
         "description":"Google Maps API Introduction ...",
         "tags":[
            "GDD07","GDD07US","Maps"
         ],
         "thumbnail":{
            "default":"http://i.ytimg.com/vi/hYB0mn5zh2c/default.jpg",
            "hqDefault":"http://i.ytimg.com/vi/hYB0mn5zh2c/hqdefault.jpg"
         },
         "player":{
            "default":"http://www.youtube.com/watch?vu003dhYB0mn5zh2c"
         },
         "content":{
            "1":"rtsp://v5.cache3.c.youtube.com/CiILENy.../0/0/0/video.3gp",
            "5":"http://www.youtube.com/v/hYB0mn5zh2c?f...",
            "6":"rtsp://v1.cache1.c.youtube.com/CiILENy.../0/0/0/video.3gp"
         },
         "duration":2840,
         "aspectRatio":"widescreen",
         "rating":4.63,
         "ratingCount":68,
         "viewCount":220101,
         "favoriteCount":201,
         "commentCount":22,
         "status":{
            "value":"restricted",
            "reason":"limitedSyndication"
         },
         "accessControl":{
            "syndicate":"allowed",
            "commentVote":"allowed",
            "rate":"allowed",
            "list":"allowed",
            "comment":"allowed",
            "embed":"allowed",
            "videoRespond":"moderated"
         }
        }
    ]
 }
}

Once again we wrap all in single quotes and MySQL accepts it as a valid JSON document. So the PHP built-in function JSON_ENCODE does the job very well.