I would like to point out some 'small' things you may not have noticed in the MySQL 8.0.17 Release Notes. They are small changes compared to things like MVIs, InnoDB cloning, and the like but these are the types of changes that are subtle that may catch you unaware.
1. Host names have grown from 60 to 255 characters. However your SSL/TLS package may not be able to handle the longer names.
2. If you are an old C/C++ programmer, 'C-style &&, ||, and ! operators that are synonyms for the standard SQL AND, OR, and NOT operators, respectively, are deprecated and support for them will be removed in a future MySQL version'.
3. The ZERO fill attribute is being deprecated as is unsigned FLOAT, DOUBLE, and DECIMAL. You can no longer AUTO_INCREMENT FLOAT and DOUBLE columns.
4. The optimizer will rewrite your WHERE foo to WHERE foo != 0 to ensure complete predicates are being used to plan your query.
Tuesday, July 23, 2019
Monday, July 22, 2019
JSON Schema Validation with MySQL 8.0.17
JSON has become the standard document interchange format over the last several years. MySQL 5.7 added a native JSON data type and it has been greatly enhanced with version 8.0. But many in the relational world have complained the the NoSQL approach does not allow you to have rigor on your data. That is to make sure an integer value is really an integer and within specified ranges or string of the proper length. And there was no way to make sure that email addresses are not listed under a combination of E-mail, e-mail, eMail, and eMAIL. JSON is great for many things but traditional, normalized data was better for making certain that your data matched what was specified.
If only there was a way to enforce come rigor on JSON data! Or a way to annotate (pronounced 'document') your JSON data. Well there is. MySQL 8.0.17 has added the ability to validate JSON documents against a schema following the guidelines of the JSON-Schema.org's fourth draft standard. You can find both the manual page 12.17.7 JSON Schema Validation Functions and the JSON Schema information online.
As you are probably already aware, MySQL will reject an invalid JSON document when using the JSON data type. But there is a difference between syntactically valid and validation against a schema. With schema validation you can define how the data should be formatted. This will help with automated testing and help ensure the quality of your data.
Lets create a simple document schema that looks at a key named 'myage' and set up rules that the minimum value is 28 and the maximum value is 99.
set @s='{"type": "object",
"properties": {
"myage": {
"type" : "number",
"minimum": 28,
"maximum": 99
}
}
}';
And here is our test document where we use a value for 'myage' what is between the minimum and the maximum.
set @d='{ "myage": 33}';
Now we use JSON_SCHEMA_VALID() to test if the test document passes the validation test, with 1 or true as a pass and 0 or false as a fail.
select JSON_SCHEMA_VALID(@s,@d);
+--------------------------+
| JSON_SCHEMA_VALID(@s,@d) |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.00 sec)
Now try with a non-numeric value.
set @d='{ "myage": "foo"}';
Query OK, 0 rows affected (0.00 sec)
mysql> select JSON_SCHEMA_VALID(@s,@d);
+--------------------------+
| JSON_SCHEMA_VALID(@s,@d) |
+--------------------------+
| 0 |
+--------------------------+
And a value below the minimum.
mysql> set @d='{ "myage": 16}';
Query OK, 0 rows affected (0.00 sec)
mysql> select JSON_SCHEMA_VALID(@s,@d);
+--------------------------+
| JSON_SCHEMA_VALID(@s,@d) |
+--------------------------+
| 0 |
+--------------------------+
1 row in set (0.00 sec)
Validity Report
We can use JSON_SCHEMA_VALIDATION_REPORT() to get more information on why a document is failing with JSON_SCHEMA_VALID().
mysql> select JSON_SCHEMA_VALIDATION_REPORT(@s,@d)\G
*************************** 1. row ***************************
JSON_SCHEMA_VALIDATION_REPORT(@s,@d): {"valid": false, "reason": "The JSON document location '#/myage' failed requirement 'minimum' at JSON Schema location '#/properties/myage'", "schema-location": "#/properties/myage", "document-location": "#/myage", "schema-failed-keyword": "minimum"}
1 row in set (0.00 sec)
And, you should note, the response is in JSON format. And you can neaten the output up with JSON_PRETTY() wrapped around the above query.
If you want to make sure certain keys are included in a document, you can use a the required option in your schema definition. So if you are working with GIS information, you can specify requiring longitude and latitude.
""required": ["latitude", "longitude"]
So we can no have required fields and specify their value ranges. And we can verify BEFORE committing the JSON document to the MySQL server that the data conforms to our schema.
CREATE TABLE `testx` (
`col` JSON,
CONSTRAINT `myage_inRange`
CHECK (JSON_SCHEMA_VALID('{"type": "object",
"properties": {
"myage": {
"type" : "number",
"minimum": 28,
"maximum": 99
}
},"required": ["myage"]
}', `col`) = 1)
);
And the proof that it works.
mysql> insert into testx values('{"myage":27}');
ERROR 3819 (HY000): Check constraint 'myage_inRange' is violated.
mysql> insert into testx values('{"myage":97}');
Query OK, 1 row affected (0.02 sec)
So two of the big criticisms on using JSON in a relational database are now gone. We can add rigor and value checks. While not as easy to do as with normalized relational data, this is a huge win for those using JSON.
I highly recommend going through the basics of JSON Schema as they is a lot of material that can not be covered in a simple blog.
If only there was a way to enforce come rigor on JSON data! Or a way to annotate (pronounced 'document') your JSON data. Well there is. MySQL 8.0.17 has added the ability to validate JSON documents against a schema following the guidelines of the JSON-Schema.org's fourth draft standard. You can find both the manual page 12.17.7 JSON Schema Validation Functions and the JSON Schema information online.
Valid JSON and Really Valid JSON
As you are probably already aware, MySQL will reject an invalid JSON document when using the JSON data type. But there is a difference between syntactically valid and validation against a schema. With schema validation you can define how the data should be formatted. This will help with automated testing and help ensure the quality of your data.
Overly Simple Example
Lets create a simple document schema that looks at a key named 'myage' and set up rules that the minimum value is 28 and the maximum value is 99.
set @s='{"type": "object",
"properties": {
"myage": {
"type" : "number",
"minimum": 28,
"maximum": 99
}
}
}';
And here is our test document where we use a value for 'myage' what is between the minimum and the maximum.
set @d='{ "myage": 33}';
Now we use JSON_SCHEMA_VALID() to test if the test document passes the validation test, with 1 or true as a pass and 0 or false as a fail.
select JSON_SCHEMA_VALID(@s,@d);
+--------------------------+
| JSON_SCHEMA_VALID(@s,@d) |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.00 sec)
Now try with a non-numeric value.
set @d='{ "myage": "foo"}';
Query OK, 0 rows affected (0.00 sec)
mysql> select JSON_SCHEMA_VALID(@s,@d);
+--------------------------+
| JSON_SCHEMA_VALID(@s,@d) |
+--------------------------+
| 0 |
+--------------------------+
And a value below the minimum.
mysql> set @d='{ "myage": 16}';
Query OK, 0 rows affected (0.00 sec)
mysql> select JSON_SCHEMA_VALID(@s,@d);
+--------------------------+
| JSON_SCHEMA_VALID(@s,@d) |
+--------------------------+
| 0 |
+--------------------------+
1 row in set (0.00 sec)
We can use JSON_SCHEMA_VALIDATION_REPORT() to get more information on why a document is failing with JSON_SCHEMA_VALID().
mysql> select JSON_SCHEMA_VALIDATION_REPORT(@s,@d)\G
*************************** 1. row ***************************
JSON_SCHEMA_VALIDATION_REPORT(@s,@d): {"valid": false, "reason": "The JSON document location '#/myage' failed requirement 'minimum' at JSON Schema location '#/properties/myage'", "schema-location": "#/properties/myage", "document-location": "#/myage", "schema-failed-keyword": "minimum"}
1 row in set (0.00 sec)
And, you should note, the response is in JSON format. And you can neaten the output up with JSON_PRETTY() wrapped around the above query.
select JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@s,@d))\G
*************************** 1. row ***************************
JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@s,@d)): {
"valid": false,
"reason": "The JSON document location '#/myage' failed requirement 'minimum' at JSON Schema location '#/properties/myage'",
"schema-location": "#/properties/myage",
"document-location": "#/myage",
"schema-failed-keyword": "minimum"
}
*************************** 1. row ***************************
JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@s,@d)): {
"valid": false,
"reason": "The JSON document location '#/myage' failed requirement 'minimum' at JSON Schema location '#/properties/myage'",
"schema-location": "#/properties/myage",
"document-location": "#/myage",
"schema-failed-keyword": "minimum"
}
Required Keys
If you want to make sure certain keys are included in a document, you can use a the required option in your schema definition. So if you are working with GIS information, you can specify requiring longitude and latitude.
""required": ["latitude", "longitude"]
So we can no have required fields and specify their value ranges. And we can verify BEFORE committing the JSON document to the MySQL server that the data conforms to our schema.
Using JSON SCHEMA Validation with Check Constraint
SO the next logical step is to use the CONSTRAINT CHECK option on table creation to assure that we are not only getting a valid JSON document but a verified JSON document.CREATE TABLE `testx` (
`col` JSON,
CONSTRAINT `myage_inRange`
CHECK (JSON_SCHEMA_VALID('{"type": "object",
"properties": {
"myage": {
"type" : "number",
"minimum": 28,
"maximum": 99
}
},"required": ["myage"]
}', `col`) = 1)
);
And the proof that it works.
mysql> insert into testx values('{"myage":27}');
ERROR 3819 (HY000): Check constraint 'myage_inRange' is violated.
mysql> insert into testx values('{"myage":97}');
Query OK, 1 row affected (0.02 sec)
So two of the big criticisms on using JSON in a relational database are now gone. We can add rigor and value checks. While not as easy to do as with normalized relational data, this is a huge win for those using JSON.
More on JSON Schema
I highly recommend going through the basics of JSON Schema as they is a lot of material that can not be covered in a simple blog.
Three New JSON Functions in MySQL 8.0.17
MySQL 8.0.17 adds three new functions to the JSON repertoire. All three can take advantage of the new Multi-Value Index feature or can be used on JSON arrays.
This function indicates with a 1 or 0 if a candidate document is contained in the target document. The optional path argument lets you seek information in embedded documents. And please note the 'haystack' is before the 'needle' for this function.
mysql> SELECT JSON_CONTAINS('{"Moe": 1, "Larry": 2}','{"Moe": 1}');
+------------------------------------------------------+
| JSON_CONTAINS('{"Moe": 1, "Larry": 2}','{"Moe": 1}') |
+------------------------------------------------------+
| 1 |
+------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS('{"Moe": 1, "Larry": 2}','{"Shemp": 1}');
+--------------------------------------------------------+
| JSON_CONTAINS('{"Moe": 1, "Larry": 2}','{"Shemp": 1}') |
+--------------------------------------------------------+
| 0 |
+--------------------------------------------------------+
1 row in set (0.00 sec)
Objects as must match both key and value. Be careful as an array is considered to be contained in a target array only if every element in the candidate is contained in some element of the target. So JSON_CONTAINS("[1,2,3]","[2,3]") will return a '1' while JSON_CONTAINS("[1,2,3]","[3,4]") will return a '0'.
You can always use JSON_CONTAINS_PATH() to test if any matches exist on the entire path and JSON_CONTAINS() for a simple match.
This functions compares two JSON documents and returns 1 if it has any key/value pairs or array elements in common.
mysql> SELECT JSON_OVERLAPS("[1,3,5,7]","[2,3,4,5]");
+----------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]","[2,3,4,5]") |
+----------------------------------------+
| 1 |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_OVERLAPS("[1,3,5,7]","[2,4,6]");
+--------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]","[2,4,6]") |
+--------------------------------------+
| 0 |
+--------------------------------------+
1 row in set (0.00 sec)
So what is the difference between these two new functions? JSON_CONTAINS() requires ALL elements of the array searched for to be present while JSON_OVERLAPS() looks for any matches. So think JSON_CONTAINS() as the AND operation on KEYS while JSON_OVERLAP is the OR operator.
mysql> SELECT JSON_OVERLAPS("[1,3,5,7]","[1,3,5,9]");
+----------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]","[1,3,5,9]") |
+----------------------------------------+
| 1 |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS("[1,3,5,7]","[1,3,5,9]");
+----------------------------------------+
| JSON_CONTAINS("[1,3,5,7]","[1,3,5,9]") |
+----------------------------------------+
| 0 |
+----------------------------------------+
1 row in set (0.00 sec)
This function returns a 1 if the value is an element of the json_array.
mysql> SELECT 3 MEMBER OF('[1, 3, 5, 7, "Moe"]');
+------------------------------------+
| 3 MEMBER OF('[1, 3, 5, 7, "Moe"]') |
+------------------------------------+
| 1 |
+------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT 2 MEMBER OF('[1, 3, 5, 7, "Moe"]');
+------------------------------------+
| 2 MEMBER OF('[1, 3, 5, 7, "Moe"]') |
+------------------------------------+
| 0 |
+------------------------------------+
1 row in set (0.00 sec)
This function does not convert to and from strings for you so do not try something like this.
mysql> SELECT "3" MEMBER OF('[1, 3, 5, 7, "Moe"]');
+--------------------------------------+
| "3" MEMBER OF('[1, 3, 5, 7, "Moe"]') |
+--------------------------------------+
| 0 |
+--------------------------------------+
So "3" is not equal to 3. And you may have to explicitly cast the value as an array or use JSON_ARRAY().
mysql> SELECT CAST('[3,4]' AS JSON) MEMBER OF ('[[1,2],[3,4]]');
+---------------------------------------------------+
| CAST('[3,4]' AS JSON) MEMBER OF ('[[1,2],[3,4]]') |
+---------------------------------------------------+
| 1 |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_ARRAY(3,4) MEMBER OF ('[[1,2],[3,4]]');
+---------------------------------------------+
| JSON_ARRAY(3,4) MEMBER OF ('[[1,2],[3,4]]') |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
1 row in set (0.00 sec)
JSON_CONTAINS(target, candiate[, path])
This function indicates with a 1 or 0 if a candidate document is contained in the target document. The optional path argument lets you seek information in embedded documents. And please note the 'haystack' is before the 'needle' for this function.
mysql> SELECT JSON_CONTAINS('{"Moe": 1, "Larry": 2}','{"Moe": 1}');
+------------------------------------------------------+
| JSON_CONTAINS('{"Moe": 1, "Larry": 2}','{"Moe": 1}') |
+------------------------------------------------------+
| 1 |
+------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS('{"Moe": 1, "Larry": 2}','{"Shemp": 1}');
+--------------------------------------------------------+
| JSON_CONTAINS('{"Moe": 1, "Larry": 2}','{"Shemp": 1}') |
+--------------------------------------------------------+
| 0 |
+--------------------------------------------------------+
1 row in set (0.00 sec)
Objects as must match both key and value. Be careful as an array is considered to be contained in a target array only if every element in the candidate is contained in some element of the target. So JSON_CONTAINS("[1,2,3]","[2,3]") will return a '1' while JSON_CONTAINS("[1,2,3]","[3,4]") will return a '0'.
You can always use JSON_CONTAINS_PATH() to test if any matches exist on the entire path and JSON_CONTAINS() for a simple match.
JSON_OVERLAPS(document1, document2)
This functions compares two JSON documents and returns 1 if it has any key/value pairs or array elements in common.
mysql> SELECT JSON_OVERLAPS("[1,3,5,7]","[2,3,4,5]");
+----------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]","[2,3,4,5]") |
+----------------------------------------+
| 1 |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_OVERLAPS("[1,3,5,7]","[2,4,6]");
+--------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]","[2,4,6]") |
+--------------------------------------+
| 0 |
+--------------------------------------+
1 row in set (0.00 sec)
So what is the difference between these two new functions? JSON_CONTAINS() requires ALL elements of the array searched for to be present while JSON_OVERLAPS() looks for any matches. So think JSON_CONTAINS() as the AND operation on KEYS while JSON_OVERLAP is the OR operator.
mysql> SELECT JSON_OVERLAPS("[1,3,5,7]","[1,3,5,9]");
+----------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]","[1,3,5,9]") |
+----------------------------------------+
| 1 |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS("[1,3,5,7]","[1,3,5,9]");
+----------------------------------------+
| JSON_CONTAINS("[1,3,5,7]","[1,3,5,9]") |
+----------------------------------------+
| 0 |
+----------------------------------------+
1 row in set (0.00 sec)
value MEMBER OF(json_array)
This function returns a 1 if the value is an element of the json_array.
mysql> SELECT 3 MEMBER OF('[1, 3, 5, 7, "Moe"]');
+------------------------------------+
| 3 MEMBER OF('[1, 3, 5, 7, "Moe"]') |
+------------------------------------+
| 1 |
+------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT 2 MEMBER OF('[1, 3, 5, 7, "Moe"]');
+------------------------------------+
| 2 MEMBER OF('[1, 3, 5, 7, "Moe"]') |
+------------------------------------+
| 0 |
+------------------------------------+
1 row in set (0.00 sec)
This function does not convert to and from strings for you so do not try something like this.
mysql> SELECT "3" MEMBER OF('[1, 3, 5, 7, "Moe"]');
+--------------------------------------+
| "3" MEMBER OF('[1, 3, 5, 7, "Moe"]') |
+--------------------------------------+
| 0 |
+--------------------------------------+
So "3" is not equal to 3. And you may have to explicitly cast the value as an array or use JSON_ARRAY().
mysql> SELECT CAST('[3,4]' AS JSON) MEMBER OF ('[[1,2],[3,4]]');
+---------------------------------------------------+
| CAST('[3,4]' AS JSON) MEMBER OF ('[[1,2],[3,4]]') |
+---------------------------------------------------+
| 1 |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_ARRAY(3,4) MEMBER OF ('[[1,2],[3,4]]');
+---------------------------------------------+
| JSON_ARRAY(3,4) MEMBER OF ('[[1,2],[3,4]]') |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
1 row in set (0.00 sec)
Use with Multi-Value Indexes
Queries using JSON_CONTAINS(), JSON_OVERLAPS(), or MEMBER OF() on JSON columns of an InnoDB table can be optimized to use Multi-Valued Indexes. More on MVIs in another blog post!
Monday, July 8, 2019
MySQL Workbench and OpenStreetMap Data Visualization
I have a presentation next month on MySQL and GIS. MySQL 8.0 has benefited greatly from the three dimensional libraries from Boost.Geometry. There are many facets to the Geographic Data world that it is damn near impossible not to lurch down one rabbit hole into another in an unending spiral of acronyms, standards, projections, and functions. But thankfully I have MySQL Workbench to aid me.
I wanted some test data to use for some GIS exercises and was very happy to find many useful sets curated by the OpenStreetMaps folks. Shapefiles are used to hold the various data points of an item of interest. I had assumed that the data would have some sort of longitude/latitude pairs but was wondering what I would need to do to work with that data and what ever came bundled with it. I download the Texas data and then loaded it into the database.
You will need a copy of the ogr2org utility This wonderful program reads the raw shapefile and converts it into SQL. You may want to get the source but hopefully a version may be available for you Linux distribution. There is a copy of the ogr2ogr program that comes with MySQL Workbench but I have not been able to get it to convert the data and load that data into a MySQL instance, with either Windows, Fedora, or Ubuntu.
$ogr2ogr -overwrite -progress -f "MySQL" mysql:texas,user=dave,password=****** gis_osm_natural_a_free_1.shp
0...10...20...30...40...50...60...70...80...90...100 - done.
$ ogr2ogr -overwrite -progress -f "MySQL" mysql:texas,user=dave,password=***** gis_osm_natural_free_1.shp
0...10...20...30...40...50...60...70...80...90...100 - done.
The ogr2org utility reads the shape files and puts all the attributes into a a table.
mysql> DESC gis_osm_natural_free_1;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| OGR_FID | int(11) | NO | PRI | NULL | auto_increment |
| SHAPE | geometry | NO | MUL | NULL | |
| osm_id | varchar(10) | YES | | NULL | |
| code | decimal(4,0) | YES | | NULL | |
| fclass | varchar(28) | YES | | NULL | |
| name | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
If you right click on the 'BLOB' graphic under the shape column you will see an option to Show Point In Browser.
So Stewart Beach Park is right on the Galveston Sea Wall and around the corner from the cruise ship terminals.
Texas
I wanted some test data to use for some GIS exercises and was very happy to find many useful sets curated by the OpenStreetMaps folks. Shapefiles are used to hold the various data points of an item of interest. I had assumed that the data would have some sort of longitude/latitude pairs but was wondering what I would need to do to work with that data and what ever came bundled with it. I download the Texas data and then loaded it into the database.
You will need a copy of the ogr2org utility This wonderful program reads the raw shapefile and converts it into SQL. You may want to get the source but hopefully a version may be available for you Linux distribution. There is a copy of the ogr2ogr program that comes with MySQL Workbench but I have not been able to get it to convert the data and load that data into a MySQL instance, with either Windows, Fedora, or Ubuntu.
$ogr2ogr -overwrite -progress -f "MySQL" mysql:texas,user=dave,password=****** gis_osm_natural_a_free_1.shp
0...10...20...30...40...50...60...70...80...90...100 - done.
$ ogr2ogr -overwrite -progress -f "MySQL" mysql:texas,user=dave,password=***** gis_osm_natural_free_1.shp
0...10...20...30...40...50...60...70...80...90...100 - done.
The Data
The ogr2org utility reads the shape files and puts all the attributes into a a table.
mysql> DESC gis_osm_natural_free_1;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| OGR_FID | int(11) | NO | PRI | NULL | auto_increment |
| SHAPE | geometry | NO | MUL | NULL | |
| osm_id | varchar(10) | YES | | NULL | |
| code | decimal(4,0) | YES | | NULL | |
| fclass | varchar(28) | YES | | NULL | |
| name | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
And a sample line from that table.
mysql> select OGR_FID,ST_ASTEXT(SHAPE) as Shape, osm_id, code, fclass, name FROM gis_osm_natural_free_1 limit 1;
+---------+-------------------------------+----------+------+--------+--------------------+
| OGR_FID | Shape | osm_id | code | fclass | name |
+---------+-------------------------------+----------+------+--------+--------------------+
| 1 | POINT(29.3060929 -94.7679897) | 80029566 | 4141 | beach | Stewart Beach Park |
+---------+-------------------------------+----------+------+--------+--------------------+
1 row in set (0.00 sec)
The SHAPE column has the desired longitude and latitude. So now we have this data but I had zero clue to where Stewart Beach Park was located.
MySQL Workbench to the Rescue
Workbench makes it easy to 'see' where the data was. You can see in the below example that Stewart Beach Park is the first row returned.
 |
| MySQL Workbench has an amazing number of features including the ability to help display GIS data. |
If you right click on the 'BLOB' graphic under the shape column you will see an option to Show Point In Browser.
 |
| The location of Stewart Beach Park on the Galveston Sea Wall |
So Stewart Beach Park is right on the Galveston Sea Wall and around the corner from the cruise ship terminals.
Mid-Atlantic Developer's Conference
I will be speaking on MySQL & GIS at the Mid-Atlantic Developer's Conference and you can still attend but tickets are going quickly.
Monday, July 1, 2019
Plot your Location MySQL Workbench and OpenStreetMap
MySQL has added a lot of functionality for Geographical Information System (GIS) data in the last two releases. This has given us better ways to save and explore GIS data. MySQL Workbench is an amazing tool that many do not realize has vastly upped its game for supporting GIS information. But did you know you can use MySQL Workbench with OpenStreetMap to plot locations?
These, believe it or not, is the hard part. Go to https://www.latlong.net/ and type in the name of your location. It will report back your longitude and your latitude. For the example I will be using the information for the city of Justin, Texas.
CREATE TABLE city (id int unsigned auto_increment primary key,
name char(25),
loc point);
And then add the information and as before the example is for my home town.
INSERT INTO city (name,loc) VALUES
('Justin', ST_GeomFromText('point(33.084843 -97.296127)',4326));
Now query your data from workbench. If you only have the one record SELECT * FROM city; will suffice or use SELECT loc FROM city WHERE name='<yourcitynamehere>';
If you right click on the 'BLOB' icon under the 'loc' column, one of the choice is show point in browser. Pick that choice and you will get redirected to OpenStreetMap.org
1. Get your Longitude and Latitude
These, believe it or not, is the hard part. Go to https://www.latlong.net/ and type in the name of your location. It will report back your longitude and your latitude. For the example I will be using the information for the city of Justin, Texas.
 |
| Using www.longlat.net to find the location of Justin, Texas |
2. Create Table and Add Data
Now create a table using MySQL Workbench in your favorite test schema to store your data.CREATE TABLE city (id int unsigned auto_increment primary key,
name char(25),
loc point);
And then add the information and as before the example is for my home town.
INSERT INTO city (name,loc) VALUES
('Justin', ST_GeomFromText('point(33.084843 -97.296127)',4326));
3. Query With Workbench
Now query your data from workbench. If you only have the one record SELECT * FROM city; will suffice or use SELECT loc FROM city WHERE name='<yourcitynamehere>';
 |
| Running the query to get the longitude and latitude of Jsutin, Texas using MySQL Workbench. Note the 'BLOB' under the loc column |
4. Open In a Browser
If you right click on the 'BLOB' icon under the 'loc' column, one of the choice is show point in browser. Pick that choice and you will get redirected to OpenStreetMap.org
 |
| The OpenStreetMap.org map of Justin, Texas |
Thursday, June 27, 2019
The '$' As The JSON Document
Recently on Stackoverflow was a question about the handling of a JSON document stored in a MySQL Database. The data looked like this:
[{"name":"cdennett","address":"123 street","Postcode":"ABCDE"}]
The data above is valid JSON. That data is in an array because it is surrounded by []s while objects are surrounded by {}s. And the author was trying to use the following JSON_TABLE function
SELECT people.*
FROM t1,
JSON_TABLE(json_col, '$.people[*]'
COLUMNS (
name VARCHAR(40) PATH '$.name',
address VARCHAR(100) PATH '$.address')
) people;
Some you who have been using the JSON data type are probably smiling at the code above.It is a simple problem caused by confusion about the path of the JSON document. The problem is the way the data is referenced. Or to put it another way the path to the document is wrong. And, sadly, you probably only run into this after getting confused and having to learn the proper way to look at the path of a JSON document.
select json_extract(json_col,'$') from t1;
+----------------------------------------------------------------------+
| json_extract(json_col,'$') |
+----------------------------------------------------------------------+
| [{"name": "cdennett", "address": "123 street", "Postcode": "ABCDE"}] |
+----------------------------------------------------------------------+
Or we could use the shortcut method select json_col->"$" from t1; to get the same results.
Where this gets confusing is if you use a function like JSON_KEYS like such:
select JSON_KEYS(json_col) from t1x;
+---------------------+
| JSON_KEYS(json_col) |
+---------------------+
| NULL |
+---------------------+
Why did we get a NULL and not the same output as from the JSON_EXTRACT? Well, JSON_KEYS will return null if a) any argument is NULL, the document in question is not an object, or a path. And an array is not an object.
But other functions are not bothered by the fact that the document is not an object.
select json_pretty(json_col) from t1;
+------------------------------------------------------------------------------------------+
| json_pretty(json_col) |
+------------------------------------------------------------------------------------------+
| [
{
"name": "cdennett",
"address": "123 street",
"Postcode": "ABCDE"
}
] |
+------------------------------------------------------------------------------------------+
So if '$' is [{"name":"cdennett","address":"123 street","Postcode":"ABCDE"}] how do we 'peel off one onion layer' to get to the {"name":"cdennett","address":"123 street","Postcode":"ABCDE"}?
The contents of the first array is denoted as $[0].
'$' is [{"name":"cdennett","address":"123 street","Postcode":"ABCDE"}]
and
'$[0] is {"name":"cdennett","address":"123 street","Postcode":"ABCDE"}
and
'$[0].name is "cdbennet"
So if we refer back to the code snippet from Stackoverflow, it becomes evident that the path was certainly not '$.people[*]' but '$[*]' or '$[0]'.
[{"name":"cdennett","address":"123 street","Postcode":"ABCDE"}]
The data above is valid JSON. That data is in an array because it is surrounded by []s while objects are surrounded by {}s. And the author was trying to use the following JSON_TABLE function
SELECT people.*
FROM t1,
JSON_TABLE(json_col, '$.people[*]'
COLUMNS (
name VARCHAR(40) PATH '$.name',
address VARCHAR(100) PATH '$.address')
) people;
Some you who have been using the JSON data type are probably smiling at the code above.It is a simple problem caused by confusion about the path of the JSON document. The problem is the way the data is referenced. Or to put it another way the path to the document is wrong. And, sadly, you probably only run into this after getting confused and having to learn the proper way to look at the path of a JSON document.
'$' is Your Document
The '$' character refers to the entire document. Is we use JSON_EXTRACT or the arrow operator it is easy to retrieve the entire document.select json_extract(json_col,'$') from t1;
+----------------------------------------------------------------------+
| json_extract(json_col,'$') |
+----------------------------------------------------------------------+
| [{"name": "cdennett", "address": "123 street", "Postcode": "ABCDE"}] |
+----------------------------------------------------------------------+
Or we could use the shortcut method select json_col->"$" from t1; to get the same results.
Where this gets confusing is if you use a function like JSON_KEYS like such:
select JSON_KEYS(json_col) from t1x;
+---------------------+
| JSON_KEYS(json_col) |
+---------------------+
| NULL |
+---------------------+
Why did we get a NULL and not the same output as from the JSON_EXTRACT? Well, JSON_KEYS will return null if a) any argument is NULL, the document in question is not an object, or a path. And an array is not an object.
But other functions are not bothered by the fact that the document is not an object.
select json_pretty(json_col) from t1;
+------------------------------------------------------------------------------------------+
| json_pretty(json_col) |
+------------------------------------------------------------------------------------------+
| [
{
"name": "cdennett",
"address": "123 street",
"Postcode": "ABCDE"
}
] |
+------------------------------------------------------------------------------------------+
So if '$' is [{"name":"cdennett","address":"123 street","Postcode":"ABCDE"}] how do we 'peel off one onion layer' to get to the {"name":"cdennett","address":"123 street","Postcode":"ABCDE"}?
The contents of the first array is denoted as $[0].
'$' is [{"name":"cdennett","address":"123 street","Postcode":"ABCDE"}]
and
'$[0] is {"name":"cdennett","address":"123 street","Postcode":"ABCDE"}
and
'$[0].name is "cdbennet"
So if we refer back to the code snippet from Stackoverflow, it becomes evident that the path was certainly not '$.people[*]' but '$[*]' or '$[0]'.
Conclusions
So we end up with two conclusions. First is that to remember that '$' refers to the entire document and walking down the document of the structure means walking down a path that starts at '$'. And second, you might want to consider not burring things in a top level array.
Monday, May 20, 2019
Structuring Your Unstructured JSON data
The world seems awash in unstructured, NoSQL data, mainly of the JSON variety. While this has a great many benefits as far as data mutability and not being locked into a rigid structure there are some things missing that are common in the structured world of SQL databases.
What if there was a way to take this unstructured NoSQL JSON data and cast it, temporarily, into a structured table? Then you could use all the processing functions and features found in a relation database on you data. There is a way and it is the JSON_TABLE function.
You can find the documentation for JSON_TABLE here but there are some examples below that may make learning this valuable function easier than the simple RTFM.
I will be using the world_x dataset for the next example
If we run a simple SELECT JSON_PRETTY(doc) FROM countryinfo LIMIT 1; the server will return something similar to the following:
{
"GNP": 828,
"_id": "ABW",
"Name": "Aruba",
"IndepYear": null,
"geography": {
"Region": "Caribbean",
"Continent": "North America",
"SurfaceArea": 193
},
"government": {
"HeadOfState": "Beatrix",
"GovernmentForm": "Nonmetropolitan Territory of The Netherlands"
},
"demographics": {
"Population": 103000,
"LifeExpectancy": 78.4000015258789
}
}
We can use JSON_TABLE to extract the Name, the Head of State, and the Governmental Form easily with the following query. If you are not used to the MySQL JSON Data type, the "$" references the entire document in the doc column (and doc is out JSON data type column in the table). And notice that the $.government.HeadOfState and $.government.GovernmentForm are the full path to the keys in the document.
select jt.* FROM countryinfo,
json_table(doc, "$" COLUMNS (
name varchar(20) PATH "$.Name",
hofstate varchar(20) PATH '$.government.HeadOfState',
gform varchar(50) PATH '$.government.GovernmentForm')) as jt
limit 1;
The syntax is JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias) where expr is either a column column from a table or a JSON document passed to the function ('{"Name": "Dave"}' as an example). Then the desired columns are specified where we name the new column, give it a relational type, and then specify the path of the JSON values we want to cast.
And the results are in the form of a relational table.
+-------+----------+----------------------------------------------+
| name | hofstate | gform |
+-------+----------+----------------------------------------------+
| Aruba | Beatrix | Nonmetropolitan Territory of The Netherlands |
+-------+----------+----------------------------------------------+
This is JSON_TABLE in its most basic form. The only thing I would like to emphasize is that the keys of the JSON data are case sensitive and it is import to check your spelling!
select jt.* FROM countryinfo,
json_table(doc, "$" COLUMNS (
name varchar(20) PATH "$.Name",
hofstate varchar(20) PATH '$.government.HeadOfState',
xyz int(4) PATH '$.xyz' DEFAULT '999' ON ERROR DEFAULT '888' ON EMPTY,
gform varchar(50) PATH '$.government.GovernmentForm')) as jt
limit 1;
And how the result looks:
+-------+----------+-----+----------------------------------------------+
| name | hofstate | xyz | gform |
+-------+----------+-----+----------------------------------------------+
| Aruba | Beatrix | 888 | Nonmetropolitan Territory of The Netherlands |
+-------+----------+-----+----------------------------------------------+
Now be careful with Null values. If you change the new line to xyz int(4) PATH '$.IndepYear' DEFAULT '999' ON ERROR DEFAULT '888' ON EMPTY, we can easily see that the NULL value for Aruba's year of independence will return the default '999' value. And if you change the path to '$.Name' to try and force the string value into the integer it will trake the ON ERROR path.
And you can assign missing values to NULL
Iterating nested arrays can be painful but JSON_TABLE makes it very simple. So creating some dummy data, we can start work on digging through the nested information.
select * from a;
+----+-----------------------+
| id | x |
+----+-----------------------+
| 1 | {"a": 1, "b": [1, 2]} |
| 2 | {"a": 2, "b": [3, 4]} |
| 3 | {"a": 3, "b": [5, 6]} |
+----+-----------------------+
The query features the NESTED PATH argument
select d.* FROM a,
JSON_TABLE(x, "$" columns
(mya varchar(50) PATH "$.a",
NESTED PATH "$.b[*]"
columns (myb int path '$'))
) as d;
The output.
+-----+-----+
| mya | myb |
+-----+-----+
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
| 2 | 4 |
| 3 | 5 |
| 3 | 6 |
+-----+-----+
6 rows in set (0.0013 sec)
Not bad but lets add another level.
select * from b;
+----+-----------------------------------------------------+
| id | x |
+----+-----------------------------------------------------+
| 1 | {"a": 2, "b": [{"c": 101, "d": [44, 55, 66]}]} |
| 2 | {"a": 1, "b": [{"c": 100, "d": [11, 22, 33]}]} |
| 3 | {"a": 3, "b": [{"c": 102, "d": [77, 88, 99, 101]}]} |
+----+-----------------------------------------------------+
3 rows in set (0.0009 sec)
So lets embed another level
select d.* FROM b,
JSON_TABLE(x, "$" columns
(mya varchar(50) PATH "$.a",
NESTED PATH "$.b[*]"
columns (myc int path '$.c',
NESTED PATH '$.d[*]'
columns (dpath int path '$')))
) as d order by myc;
+-----+-----+-------+
| mya | myc | dpath |
+-----+-----+-------+
| 1 | 100 | 22 |
| 1 | 100 | 33 |
| 1 | 100 | 11 |
| 2 | 101 | 44 |
| 2 | 101 | 55 |
| 2 | 101 | 66 |
| 3 | 102 | 77 |
| 3 | 102 | 88 |
| 3 | 102 | 99 |
| 3 | 102 | 101 |
+-----+-----+-------+
10 rows in set (0.0006 sec)
And we can get ordinal numbers too.
select d.* FROM b,
JSON_TABLE(x, "$" columns
(mya varchar(50) PATH "$.a",
NESTED PATH "$.b[*]"
columns (myc int path '$.c',
nested path '$.d[*]'
columns (dcount for ordinality,
dpath int path '$'))) ) as d
order by dpath;
+-----+-----+--------+-------+
| mya | myc | dcount | dpath |
+-----+-----+--------+-------+
| 1 | 100 | 1 | 11 |
| 1 | 100 | 2 | 22 |
| 1 | 100 | 3 | 33 |
| 2 | 101 | 1 | 44 |
| 2 | 101 | 2 | 55 |
| 2 | 101 | 3 | 66 |
| 3 | 102 | 1 | 77 |
| 3 | 102 | 2 | 88 |
| 3 | 102 | 3 | 99 |
| 3 | 102 | 4 | 101 |
+-----+-----+--------+-------+
10 rows in set (0.0009 sec)
And not that we have the data structured, we can start using the WHERE clause such as where myc > 100 and dpath < 100.
What if there was a way to take this unstructured NoSQL JSON data and cast it, temporarily, into a structured table? Then you could use all the processing functions and features found in a relation database on you data. There is a way and it is the JSON_TABLE function.
JSON_TABLE
You can find the documentation for JSON_TABLE here but there are some examples below that may make learning this valuable function easier than the simple RTFM.
I will be using the world_x dataset for the next example
If we run a simple SELECT JSON_PRETTY(doc) FROM countryinfo LIMIT 1; the server will return something similar to the following:
{
"GNP": 828,
"_id": "ABW",
"Name": "Aruba",
"IndepYear": null,
"geography": {
"Region": "Caribbean",
"Continent": "North America",
"SurfaceArea": 193
},
"government": {
"HeadOfState": "Beatrix",
"GovernmentForm": "Nonmetropolitan Territory of The Netherlands"
},
"demographics": {
"Population": 103000,
"LifeExpectancy": 78.4000015258789
}
}
We can use JSON_TABLE to extract the Name, the Head of State, and the Governmental Form easily with the following query. If you are not used to the MySQL JSON Data type, the "$" references the entire document in the doc column (and doc is out JSON data type column in the table). And notice that the $.government.HeadOfState and $.government.GovernmentForm are the full path to the keys in the document.
select jt.* FROM countryinfo,
json_table(doc, "$" COLUMNS (
name varchar(20) PATH "$.Name",
hofstate varchar(20) PATH '$.government.HeadOfState',
gform varchar(50) PATH '$.government.GovernmentForm')) as jt
limit 1;
The syntax is JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias) where expr is either a column column from a table or a JSON document passed to the function ('{"Name": "Dave"}' as an example). Then the desired columns are specified where we name the new column, give it a relational type, and then specify the path of the JSON values we want to cast.
And the results are in the form of a relational table.
+-------+----------+----------------------------------------------+
| name | hofstate | gform |
+-------+----------+----------------------------------------------+
| Aruba | Beatrix | Nonmetropolitan Territory of The Netherlands |
+-------+----------+----------------------------------------------+
This is JSON_TABLE in its most basic form. The only thing I would like to emphasize is that the keys of the JSON data are case sensitive and it is import to check your spelling!
Data Problems
There is also a nice feature to JSON_TABLE where you assign a default value if that key/value pair is missing or yet another value if there is something can not be cast. If we use a non-existent key/value pair here named 'xyz' for an example, we can insert the value '888' for any JSON document missing values.select jt.* FROM countryinfo,
json_table(doc, "$" COLUMNS (
name varchar(20) PATH "$.Name",
hofstate varchar(20) PATH '$.government.HeadOfState',
xyz int(4) PATH '$.xyz' DEFAULT '999' ON ERROR DEFAULT '888' ON EMPTY,
gform varchar(50) PATH '$.government.GovernmentForm')) as jt
limit 1;
And how the result looks:
+-------+----------+-----+----------------------------------------------+
| name | hofstate | xyz | gform |
+-------+----------+-----+----------------------------------------------+
| Aruba | Beatrix | 888 | Nonmetropolitan Territory of The Netherlands |
+-------+----------+-----+----------------------------------------------+
NULL Handling
Now be careful with Null values. If you change the new line to xyz int(4) PATH '$.IndepYear' DEFAULT '999' ON ERROR DEFAULT '888' ON EMPTY, we can easily see that the NULL value for Aruba's year of independence will return the default '999' value. And if you change the path to '$.Name' to try and force the string value into the integer it will trake the ON ERROR path.
And you can assign missing values to NULL
Nested Path Data
Iterating nested arrays can be painful but JSON_TABLE makes it very simple. So creating some dummy data, we can start work on digging through the nested information.
select * from a;
+----+-----------------------+
| id | x |
+----+-----------------------+
| 1 | {"a": 1, "b": [1, 2]} |
| 2 | {"a": 2, "b": [3, 4]} |
| 3 | {"a": 3, "b": [5, 6]} |
+----+-----------------------+
The query features the NESTED PATH argument
select d.* FROM a,
JSON_TABLE(x, "$" columns
(mya varchar(50) PATH "$.a",
NESTED PATH "$.b[*]"
columns (myb int path '$'))
) as d;
The output.
+-----+-----+
| mya | myb |
+-----+-----+
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
| 2 | 4 |
| 3 | 5 |
| 3 | 6 |
+-----+-----+
6 rows in set (0.0013 sec)
Not bad but lets add another level.
select * from b;
+----+-----------------------------------------------------+
| id | x |
+----+-----------------------------------------------------+
| 1 | {"a": 2, "b": [{"c": 101, "d": [44, 55, 66]}]} |
| 2 | {"a": 1, "b": [{"c": 100, "d": [11, 22, 33]}]} |
| 3 | {"a": 3, "b": [{"c": 102, "d": [77, 88, 99, 101]}]} |
+----+-----------------------------------------------------+
3 rows in set (0.0009 sec)
So lets embed another level
select d.* FROM b,
JSON_TABLE(x, "$" columns
(mya varchar(50) PATH "$.a",
NESTED PATH "$.b[*]"
columns (myc int path '$.c',
NESTED PATH '$.d[*]'
columns (dpath int path '$')))
) as d order by myc;
+-----+-----+-------+
| mya | myc | dpath |
+-----+-----+-------+
| 1 | 100 | 22 |
| 1 | 100 | 33 |
| 1 | 100 | 11 |
| 2 | 101 | 44 |
| 2 | 101 | 55 |
| 2 | 101 | 66 |
| 3 | 102 | 77 |
| 3 | 102 | 88 |
| 3 | 102 | 99 |
| 3 | 102 | 101 |
+-----+-----+-------+
10 rows in set (0.0006 sec)
And we can get ordinal numbers too.
select d.* FROM b,
JSON_TABLE(x, "$" columns
(mya varchar(50) PATH "$.a",
NESTED PATH "$.b[*]"
columns (myc int path '$.c',
nested path '$.d[*]'
columns (dcount for ordinality,
dpath int path '$'))) ) as d
order by dpath;
+-----+-----+--------+-------+
| mya | myc | dcount | dpath |
+-----+-----+--------+-------+
| 1 | 100 | 1 | 11 |
| 1 | 100 | 2 | 22 |
| 1 | 100 | 3 | 33 |
| 2 | 101 | 1 | 44 |
| 2 | 101 | 2 | 55 |
| 2 | 101 | 3 | 66 |
| 3 | 102 | 1 | 77 |
| 3 | 102 | 2 | 88 |
| 3 | 102 | 3 | 99 |
| 3 | 102 | 4 | 101 |
+-----+-----+--------+-------+
10 rows in set (0.0009 sec)
And not that we have the data structured, we can start using the WHERE clause such as where myc > 100 and dpath < 100.
Subscribe to:
Posts (Atom)