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 : …

We're excited to participate in upcoming Virtual Technology Summit hosted by Oracle Technology Network, with three MySQL sessions presented by engineers from Oracle's MySQL team:

• Improve Performance by 3x with MySQL 5.7 - presented by Geir Høydalsvik, Sr. Software Development Director, Oracle
• Analyze and Tune MySQL Queries for Better Performance - presented by Øystein Grøvlen, Senior Principal Software Engineer, Oracle
  
• Oracle Enterprise Manager for MySQL Database: Discover the New Features - presented by Carlos Proal Aguilar, Principal Software developer, Oracle

Complete agenda and abstracts can be viewed here.

Choose among the three live events, and register for the one that best suits your schedule:

• …

Continuing from the last entry on using MySQL's new JSON data type , let us take a look at the keys in our sample database. Use JSON_KEYS to get a lit of the Keys withing the document.

mysql> SELECT JSON_KEYS(info) FROM stooge;
+-----------------+
| json_keys(info) |
+-----------------+
| ["job", "name"] |
| ["job", "name"] |
| ["job", "name"] |
| ["job", "name"] |
+-----------------+
4 rows in set (0.00 sec)

So how many records have a 'job' key?

mysql> select  JSON_CONTAINS_PATH(info,'all','$.job') from stooge;
+----------------------------------------+
| JSON_CONTAINS_PATH(info,'all','$.job') |
+----------------------------------------+
|                                      1 |
|                                      1 |
|                                      1 |
|                                      1 | …

MySQL Enterprise Monitor 3.0.26 is now available for download on the My Oracle Support (MOS) web site. This is a maintenance release that includes a few enhancements and fixes a number of bugs. You can find more information on the contents of this release in the change log.

You will find binaries for the new release on My Oracle Support. Choose the "Patches & Updates" tab, and then choose the "Product or Family (Advanced Search)" side tab in the "Patch Search" portlet.

Important: MySQL Enterprise Monitor (MEM) 3.1 offers many significant improvements over MEM 3.0 and we highly recommend that you consider upgrading. More information on MEM 3.1 is available here:

• …

So lets get some data from a JSON column from a table in a MySQL database and update it. JSON data is is simply a column like a REAL, INTEGER, CHAR, or VARCAHR. So fetching the data from a JSON column is the same as any other column. The same database we have has two columns -- a integer for a identification number and a second for JSON data. Fetch Data

#!/usr/bin/php
<?php
$mysqli = new mysqli("localhost", "root", "hidave", "test");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query1 = "SELECT * FROM stooge";
$result = $mysqli->query($query1);
    while($row = $result->fetch_assoc()) {
        printf("%d - %s\n", $row["id"], $row["info"]);
}

$mysqli->close();
?>

When the program executes we see the following:

1 - {"name": "Moe …

We are happy to announce that MySQL team is for the first time part of the PyCon Italia show happening in Florence, Italy on April 15-17, 2016. PyCon Italia is the national conference where professionals, researches and enthusiasts of the python programming language gather together and now MySQL is part of this show!

Marco Carlessi, the MySQL Senior Sales Consultant will be talking about Python and MySQL: how python can help in manage MySQL. His talk is scheduled for Saturday, April 16, 2016, @9:00am, please watch the conference schedule for further changes.

More information & registration »

Want to learn how to unleash the power of MySQL 5.7? Oracle’s MySQL team is running a series of Tech Tours across EMEA, join us!

MySQL 5.7 is 3x faster than MySQL 5.6 and improves manageability. Our technical experts will help you understand how to leverage the latest innovations in InnoDB performance & scalability, replication, JSON support, instrumentation with the Performance Schema, GIS, optimizer improvements and more! You’ll also understand how the world’s most popular open source database can help you drive digital transformation initiatives, how to ensure the highest levels of MySQL security leveraging new features & best practices, and you’ll learn how to develop better MySQL applications.

We hope to see you in one of the following locations:

• March 1, Dubai: Register now!
…

It's our pleasure to inform you about a new MySQL 5.7 community tour this time organized in Asia. After the last year MySQL 5.7 release, the ACOUG (All China Oracle User Group) with the MySQL ACE Zhaoyang Wang are going to talk about New Awesome Features in MySQL 5.7 during three meetings in Shanghai, Tokyo and Taipei.

If you are interested in hearing about the hottest news in MySQL 5.7, do not miss the meetings with discussion as they are scheduled below:

• Name: ACOUG Asia Tour - MySQL Shanghai
• Date & Time: March 11, 2016 15:00-17:00 Asia/Shanghai
• Address of the Venue: Room 404, Haitong Securities Tower, 689 Guangdong Road, Shanghai, 200001, P.R.China
• Language: Chinese
• Type of the Event: Meeting
• Registration

MySQL 5.7's new JSON data type has three functions to help you make sure your data is a valid utf8mb4 character set JSON document. They are JSON_ARRAY, JSON_QUOTE, and JSON_OBJECT. (You can also obtain JSON values by casting values of other types to the JSON type using CAST(value AS JSON)) What is the big difference? And when would you use one over another?

JSON_ARRAY takes a string as input and returns a JSON array with the values from the string.

mysql> select JSON_ARRAY('')
+----------------+
| JSON_ARRAY('') |
+----------------+
| [""]           |
+----------------+
1 row in set (0.01 sec)

mysql>  SELECT JSON_ARRAY('Foo', 42, now());
+-------------------------------------------+
| JSON_ARRAY('Foo', 42, now())              | …

Lost post covered the use of the MySQL JSON functions to feed data into the database server and why the PHP json_encode did not provide what was needed. THis time we will look how to examine the data once it is in a JSON column. We started with a simple associative array and fed it into the database.

mysql> SELECT * FROM foobar;
+--------------------------+
| mydata                   |
+--------------------------+
| {"a": 1, "b": 2, "c": 3} |
+--------------------------+
1 row in set (0.00 sec)

MySQL 5.7 has a group of JSON functions for looking at attributes. See the MySQL Manual 12.16.5 Functions That Return JSON Value Attributes. And they do not always work in an intuitive fashion! JSON_DEPTH returns the maximum depth of a JSON document. It will return a NULL if passed a …