#!/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();
}
$foo = array();
$query0 =
"SELECT* FROM restaurant WHERE json_contains(data, '{\"grade\": \"A\"}', '$.grades')";
echo "$query0\n";
if ($result = $mysqli->query($query0)) {
$row = $result->fetch_row();
printf("JSON is %s!\n\n", $row[0]);
$foo = json_decode($row[0]);
var_dump($foo);
} else {
printf("Errormessage: %s\n", $mysqli->error);
}
$mysqli->close();
In this case we get the data into an array and the the processing is limited to a var_dump().
Wednesday, March 23, 2016
Digging Down into JSON data with the MySQL Functions -- A Question from Peter Zaitsev -- Follow Up
Last time this blog covered digging into a JSON document in a MySQL 5.7 table. The goal was to pull certain records matching a particular criteria. Both Peter Zaitsev and Morgan Tocker get my thanks for their kind comments. My example was a little contrived in that an application would be used to fine tune seeking for a particular key value pair. I was trying to pull single records which is kind of silly when it is much easier to use PHP to parse the data. What follows below is a sample PHP script to grab out the matching records and then feed the results, the JSON document, into an array.
Thursday, March 17, 2016
Digging Down into JSON data with the MySQL Functions -- A Question from Peter Zaitsev
How do you dig down into the JSON data, say like in comments on a blog post? This is a badly paraphrased question from Peter Zaitsev of Percona at the end of a recent presentation of mine at the Great Wide Open conference. I have to admit I had not done like that with JSON data but it had to be able to be done, right? Surely it can be done. Can't it? Time to dig.
I could not find an example of JSON data on a blog with comments. But the good folks at Mongo had an example of a restaurant with grades.
{
"address": {
"building": "1007",
"coord": [ -73.856077, 40.848447 ],
"street": "Morris Park Ave",
"zipcode": "10462"
},
"borough": "Bronx",
"cuisine": "Bakery",
"grades": [
{ "date": { "$date": 1393804800000 }, "grade": "A", "score": 2 },
{ "date": { "$date": 1378857600000 }, "grade": "A", "score": 6 },
{ "date": { "$date": 1358985600000 }, "grade": "A", "score": 10 },
{ "date": { "$date": 1322006400000 }, "grade": "A", "score": 9 },
{ "date": { "$date": 1299715200000 }, "grade": "B", "score": 14 }
],
"name": "Morris Park Bake Shop",
"restaurant_id": "30075445"
}
So how do we get the grade information and just the grades (A,A,A,A, and B) from grades?I created a quick little table named restaurant with a single column named data for this test and then added the above document to the table. Yeah, real creative on the table and columns names there. Let's look at the fields, or keys.
mysql> select json_keys(data) from restaurant; +----------------------------------------------------------------------+ | json_keys(data) | +----------------------------------------------------------------------+ | ["name", "grades", "address", "borough", "cuisine", "restaurant_id"] | +----------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql>So can we see the path to the grades key? Yes, but not the date, grade, or score keys from under the grades tag. Note that in the case the 'one' or 'all' second argument returns the same result.
mysql> select json_contains_path(data,'one','$.grades') from restaurant; +-------------------------------------------+ | json_contains_path(data,'one','$.grades') | +-------------------------------------------+ | 1 | +-------------------------------------------+ 1 row in set (0.00 sec)Grab the grades.
mysql> select json_extract(data,'$.grades') from restaurant\G
*************************** 1. row ***************************
json_extract(data,'$.grades'): [{"date": {"$date": 1393804800000}, "grade": "A",
"score": 2}, {"date": {"$date": 1378857600000}, "grade": "A", "score": 6}, {"da
te": {"$date": 1358985600000}, "grade": "A", "score": 10}, {"date": {"$date": 13
22006400000}, "grade": "A", "score": 9}, {"date": {"$date": 1299715200000}, "gra
de": "B", "score": 14}]
1 row in set (0.00 sec)
This is all the info from the grades tag. Now to whittle this down to just the individual grade entries.
We can get the individual grades as $.grades.[N] such as:
mysql> select json_extract(data, '$.grades[1]') from restaurant\G
*************************** 1. row ***************************
json_extract(data, '$.grades[1]'): {"date": {"$date": 1378857600000}, "grade": "
A", "score": 6}
1 row in set (0.00 sec)
mysql> select json_extract(data, '$.grades[4]') from restaurant\G
*************************** 1. row ***************************
json_extract(data, '$.grades[4]'): {"date": {"$date": 1299715200000}, "grade": "
B", "score": 14}
1 row in set (0.00 sec)
Much closer!
And then the individual grade from the 4th entry:
mysql> select json_extract(json_extract(data, '$.grades[4][0]'),'$.grade') from restaurant; +--------------------------------------------------------------+ | json_extract(json_extract(data, '$.grades[4][0]'),'$.grade') | +--------------------------------------------------------------+ | "B" | +--------------------------------------------------------------+ 1 row in set (0.00 sec)I need to go read up on JSON pathing and experiment so I can grab all the grades at once but right now I fell like I am halfway done with the 'homework' Peter assigned.
Tuesday, March 15, 2016
Modifying JSON Values in MySQL 5.7
The JSON Functions for using MySQL 5.7 are overwhelming at first glance. A peek at documentation such as the JSON Modification Functions may make you shy at proceeding further. In previous posts the functions to create JSON documents for use in a MySQL column and the functions to get the meta information about JSON documents have been covered. And how to use the built-in JSON functions from PHP have been covered. But now it is time to cover modifying JSON data.
Appending
Lets start at the beginning and ask the server for a JSON array with the values of 1, 2, 3, 4, and 5.mysql> select JSON_ARRAY(1,2,3,4,5); +-----------------------+ | JSON_ARRAY(1,2,3,4,5) | +-----------------------+ | [1, 2, 3, 4, 5] | +-----------------------+ 1 row in set (0.00 sec) mysql>Using the $ operator to represent our array, we can append an array with the numbers 6, 7, and 8 in it to our array.
mysql> SELECT JSON_ARRAY_APPEND(JSON_ARRAY(1,2,3,4,5),'$',JSON_ARRAY(6,7,8)); +----------------------------------------------------------------+ | JSON_ARRAY_APPEND(JSON_ARRAY(1,2,3,4,5),'$',JSON_ARRAY(6,7,8)) | +----------------------------------------------------------------+ | [1, 2, 3, 4, 5, [6, 7, 8]] | +----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>Or change values.
mysql> select JSON_ARRAY_APPEND(JSON_ARRAY(1,2,3,4,5),'$[2]',99); +----------------------------------------------------+ | JSON_ARRAY_APPEND(JSON_ARRAY(1,2,3,4,5),'$[2]',99) | +----------------------------------------------------+ | [1, 2, [3, 99], 4, 5] | +----------------------------------------------------+ 1 row in set (0.00 sec) mysql>
Inserting an Array
But what if we need an array inside an array? We can modify arrays by inserting new arrays in them where we want. In the first example we add 9 & 10 at the beginning of an array, $[0], and the second the new array in the third position, $[2]. Remember array counting starts at zero!mysql> SELECT JSON_ARRAY_INSERT(JSON_ARRAY(1,2,3,4,5),'$[0]',JSON_ARRAY(9,10)); +------------------------------------------------------------------+ | JSON_ARRAY_INSERT(JSON_ARRAY(1,2,3,4,5),'$[0]',JSON_ARRAY(9,10)) | +------------------------------------------------------------------+ | [[9, 10], 1, 2, 3, 4, 5] | +------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_ARRAY_INSERT(JSON_ARRAY(1,2,3,4,5),'$[2]',JSON_ARRAY(9,10)); +------------------------------------------------------------------+ | JSON_ARRAY_INSERT(JSON_ARRAY(1,2,3,4,5),'$[2]',JSON_ARRAY(9,10)) | +------------------------------------------------------------------+ | [1, 2, [9, 10], 3, 4, 5] | +------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
Merging Arrays
We can also put two arrays together with JSON_MERGE.mysql> SELECT JSON_MERGE(JSON_ARRAY(1,2,3,4,5),JSON_ARRAY(6,7,8,9)); +-------------------------------------------------------+ | JSON_MERGE(JSON_ARRAY(1,2,3,4,5),JSON_ARRAY(6,7,8,9)) | +-------------------------------------------------------+ | [1, 2, 3, 4, 5, 6, 7, 8, 9] | +-------------------------------------------------------+ 1 row in set (0.00 sec) mysql>Or Combine arrays with objects.
mysql> SELECT JSON_MERGE(JSON_ARRAY(1,2,3,4,5),JSON_OBJECT('name','Dave'));
+--------------------------------------------------------------+
| JSON_MERGE(JSON_ARRAY(1,2,3,4,5),JSON_OBJECT('name','Dave')) |
+--------------------------------------------------------------+
| [1, 2, 3, 4, 5, {"name": "Dave"}] |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
Removing/Replacing JSON
Sometimes you need to remove or replace items as well as add them. Here we remove the first item, $[0].mysql> SELECT JSON_REMOVE(JSON_ARRAY(1,2,3,4,5),'$[0]'); +-------------------------------------------+ | JSON_REMOVE(JSON_ARRAY(1,2,3,4,5),'$[0]') | +-------------------------------------------+ | [2, 3, 4, 5] | +-------------------------------------------+ 1 row in set (0.00 sec) mysql>The JSON_REPLACE function has an argument for the new value. Here we change the first item in the array from 1 to 999.
mysql> SELECT JSON_REPLACE(JSON_ARRAY(1,2,3,4,5),'$[0]',999); +------------------------------------------------+ | JSON_REPLACE(JSON_ARRAY(1,2,3,4,5),'$[0]',999) | +------------------------------------------------+ | [999, 2, 3, 4, 5] | +------------------------------------------------+ 1 row in set (0.00 sec) mysql>
Game, SET , and Match
The JSON_SET function can insert OR update values. Previously exiting items are updated, so we could use JSON_SET like JSON_REPLACE.mysql> SELECT JSON_SET(JSON_ARRAY(1,2,3,4,5),'$[0]',999)\g +--------------------------------------------+ | JSON_SET(JSON_ARRAY(1,2,3,4,5),'$[0]',999) | +--------------------------------------------+ | [999, 2, 3, 4, 5] | +--------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_SET(JSON_ARRAY(1,2,3,4,5),'$[2]',999)\g +--------------------------------------------+ | JSON_SET(JSON_ARRAY(1,2,3,4,5),'$[2]',999) | +--------------------------------------------+ | [1, 2, 999, 4, 5] | +--------------------------------------------+ 1 row in set (0.00 sec)Or use it like JSON_ARRAY_APPEND. Here using the not existing index of $[7] we append the value 999 to the array.
mysql> SELECT JSON_SET(JSON_ARRAY(1,2,3,4,5),'$[7]',999)\g +--------------------------------------------+ | JSON_SET(JSON_ARRAY(1,2,3,4,5),'$[7]',999) | +--------------------------------------------+ | [1, 2, 3, 4, 5, 999] | +--------------------------------------------+ 1 row in set (0.00 sec) mysql>JSON_SET, JSON_INSERT, and JSON_REPLACE are very closely related. And all allow you to do lots of work with one call. So lets change the values for the first and third values in the array and add a new value at the end.
mysql> SELECT JSON_SET(JSON_ARRAY(1,2,3,4,5),'$[7]',999,'$[0]',888,'$[2]',777)\g +------------------------------------------------------------------+ | JSON_SET(JSON_ARRAY(1,2,3,4,5),'$[7]',999,'$[0]',888,'$[2]',777) | +------------------------------------------------------------------+ | [888, 2, 777, 4, 5, 999] | +------------------------------------------------------------------+ 1 row in set (0.00 sec)So read this entry and then go to the JSON Mofication Documentation and be less shy.
Next time -- Back to JSON coding with PHP
Wednesday, March 2, 2016
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.
Subscribe to:
Posts (Atom)