#!/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.
Wednesday, February 24, 2016
MySQL JSON Keys
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 | +----------------------------------------+ 4 rows in set (0.00 sec)The 1 means the record has a 'job' key and a 0 would indicate a lack. Not too interesting in this case as all the JSON docs have the same keys. But how do you gran one particular key/value, also know as a object? If we know the object(s)/ record(s) sought contain something to search on we pass that to JSON_CONTAINS.
SELECT * FROM stooge WHERE JSON_CONTAINS(info,'{"job": "Head Stooge"}');
My history of fat fingering brackets, braces, and single/double quotes is pretty long and is it not cheating to use JSON_OBJECT to help here.
mysql> SELECT * FROM stooge WHERE JSON_CONTAINS(info,JSON_OBJECT('job','Head Stooge'));
+------+----------------------------------------------+
| id | info |
+------+----------------------------------------------+
| 1 | {"job": "Head Stooge", "name": "Moe Howard"} |
+------+----------------------------------------------+
1 row in set (0.00 sec)
Now up to this point we have not extracted the JSON data into a generated column for SQL compatible indexes. If we look at the EXPLAIN for the last query it tells use we had a full table scan. And full table scans are not efficient.
mysql> EXPLAIN select JSON_SEARCH(info,'Comic','job') from stooge\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: stooge
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
mysql>
Monday, February 22, 2016
Grab JSON DATA from MySQL and Update It
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 Howard"}
2 - {"name": "Larry Fine"}
3 - {"name": "Shemp Howard"}
So lets add another record
#!/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 = "INSERT INTO stooge VALUES (4,JSON_OBJECT('name','Curly Howard'))";
print "$query1\n";
if(!$result = $mysqli->query($query1)) {
printf("Error: %s\n", $mysqli->error);
}
$mysqli->close();
?>
Please note that the example programs are very simple and from here on out they will skip some obvious error checking. I am trying to show the concepts of using the MySQL JSON data type and not the role of proper error checking which you should have ingrained in you PERIOD. I am going for brevity in the examples and not teaching proper programming practices.
mysql -u root -p test -e "SELECT * FROM stooge\g";
Enter password:
+------+---------------------------+
| id | info |
+------+---------------------------+
| 1 | {"name": "Moe Howard"} |
| 2 | {"name": "Shemp Howard"} |
| 3 | {"name": "Larry Fine"} |
| 3 | {"name": "Curly Howard"} |
+------+---------------------------+
So how do we get just the name. We can use JSON_EXTRACT in two ways to get the data. We can use JSON_EXTRACT directly as in the example code below or the short hand SELECT info->"$.name" AS Name FROM stooge; The arrow operator is shorthand for JSON_EXTRACT and there is no penalty for using one over the other.
#!/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 JSON_EXTRACT(info, '$.name') AS Name FROM stooge";
$result = $mysqli->query($query1);
while($row = $result->fetch_assoc()) {
printf("%s\n", $row["Name"]);
}
$mysqli->close();
?>
And we get the names as so:
"Moe Howard" "Larry Fine" "Shemp Howard" "Curly Howard"Don't like the double quotes? Wrap JSON_UNQUOTE around the core of the select as in 'SELECT JSON_UNQUOTE(info->"$.name") AS Name FROM stooge' to remove them.
A new Key/Value Pair
Lets add a new Key/Value pair to our data. Each of the JSON documents will get a new Key names 'job' and a value inserted. We have to tell the serve which document we are using (you can have more than one JSON column or document per table), the name of this Key, and a value. We use "UPDATE stooge SET info = JSON_SET(info,'$.job','Comic')" to tell the server we are adding a new key to the info document/column, naming it job and for this example all the records are given the same job value.
!/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 = "UPDATE stooge SET info = JSON_SET(info,'$.job','Comic')";
$result = $mysqli->query($query1);
$mysqli->close();
?>
The data:
mysql> select * from stooge;
+------+------------------------------------------+
| id | info |
+------+------------------------------------------+
| 1 | {"job": "Comic", "name": "Moe Howard"} |
| 2 | {"job": "Comic", "name": "Larry Fine"} |
| 3 | {"job": "Comic", "name": "Shemp Howard"} |
| 4 | {"job": "Comic", "name": "Curly Howard"} |
+------+------------------------------------------+
4 rows in set (0.00 sec)
Note that MySQL alphabetizes the keys.
Replacement Job
Let's change Moe Howard's job. We will use the LIMIT clause on our SQL to update only one entry (and Moe is the first entry in this data set).
#!/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 = "UPDATE stooge SET info = JSON_REPLACE(info,'$.job','Head Stooge') LIMIT 1";
print "$query1\n";
$result = $mysqli->query($query1) ;
$mysqli->close();
?>
And we update only the first record:
mysql> select * from stooge;
+------+----------------------------------------------+
| id | info |
+------+----------------------------------------------+
| 1 | {"job": "Head Stooge", "name": "Moe Howard"} |
| 2 | {"job": "Comic", "name": "Larry Fine"} |
| 3 | {"job": "Comic", "name": "Shemp Howard"} |
| 4 | {"job": "Comic", "name": "Curly Howard"} |
+------+----------------------------------------------+
4 rows in set (0.00 sec)
Next time we will dig deeper into the MySQL JSON Functions.
Monday, February 15, 2016
MySQL JSON Functions to Create Values
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()) |
+-------------------------------------------+
| ["Foo", 42, "2016-02-15 07:31:56.000000"] |
+-------------------------------------------+
JSON_QUOTE takes the string given as input and wraps it with double quotes.
mysql> SELECT JSON_QUOTE('{foo,1,{0,1,2},"FOOBAR"}');
+----------------------------------------+
| JSON_QUOTE('{foo,1,{0,1,2},"FOOBAR"}') |
+----------------------------------------+
| "{foo,1,{0,1,2},\"FOOBAR\"}" |
+----------------------------------------+
1 row in set (0.00 sec)
And finally JSON_OBJECT takes a list of key/value pairs and returns a JSON object containing those pairs. It will error if the number of arguments is odd or a key is named NULL.
SELECT JSON_OBJECT('a',1,'bb',345);
+-----------------------------+
| JSON_OBJECT('a',1,'bb',345) |
+-----------------------------+
| {"a": 1, "bb": 345} |
+-----------------------------+
1 row in set (0.00 sec)
So you would use JSON_OBJECT for creating JSON Objects (key/value pairs), JSON_ARRAY for creating arrays, some of which could be including in a bigger JSON Document, and JSON_QUOTE to escape embedded quotes.
Next time we will look at the MySQL JSON Functions to modify JSON data.
Subscribe to:
Posts (Atom)