{"id":1515,"date":"2015-11-17T16:21:29","date_gmt":"2015-11-17T15:21:29","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=1515"},"modified":"2019-04-02T10:10:30","modified_gmt":"2019-04-02T09:10:30","slug":"30-mins-with-json-in-mysql","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2015\/11\/17\/30-mins-with-json-in-mysql\/","title":{"rendered":"30 mins with JSON in MySQL"},"content":{"rendered":"

Lire cet article en fran\u00e7ais<\/a><\/em><\/p>\n

Note<\/span><\/em>: This article is inspired by <\/i>Morgan Tocker<\/i><\/a>‘s talk <\/i>MySQL 5.7 + JSON<\/i><\/a>.<\/p>\n

Note<\/span> 2:\u00a0You may also be interested by\u00a030 mins with MySQL JSON functions<\/a><\/strong><\/em><\/p>\n

Note<\/em><\/span> 3: Handling JSON documents could be also done with MySQL Document Store<\/a>.<\/p>\n

As you may know MySQL 5.7<\/a> is GA<\/a> and has over than 150 new features<\/a>. One of them is a Native JSON Data Type and JSON Functions<\/b>: \u00ab\u00a0Allows for efficient and flexible storage, search and manipulation of schema-less data. Enhancements include a new internal binary format, support for easy integration within SQL, and index management on the JSON Documents using generated columns\u00a0\u00bb<\/i>.<\/p>\n

Sounds interesting! Let’s take half an hour to have a foretaste…<\/p>\n

Get JSON documents<\/h1>\n

First let’s get data in JSON format. Mirco Zeiss<\/a> provides a really big JSON file representing san francisco’s subdivision parcels (from SF Open Data<\/a>) at https:\/\/github.com\/zemirco\/sf-city-lots-json<\/a><\/p>\n

To use these data some tweaks are necessary:<\/p>\n

$ grep \"^{ \\\"type\" citylots.json > properties.json\n\n$ head -n1 properties.json \n{  \n   \"type\":\"Feature\",\n   \"properties\":{  \n      \"MAPBLKLOT\":\"0001001\",\n      \"BLKLOT\":\"0001001\",\n      \"BLOCK_NUM\":\"0001\",\n      \"LOT_NUM\":\"001\",\n      \"FROM_ST\":\"0\",\n      \"TO_ST\":\"0\",\n      \"STREET\":\"UNKNOWN\",\n      \"ST_TYPE\":null,\n      \"ODD_EVEN\":\"E\"\n   },\n   \"geometry\":{  \n      \"type\":\"Polygon\",\n      \"coordinates\":[  \n         [  \n            [  \n               -122.422003528252475,\n               37.808480096967251,\n               0.0\n            ],\n            [  \n               -122.422076013325281,\n               37.808835019815085,\n               0.0\n            ],\n            [  \n               -122.421102174348633,\n               37.808803534992904,\n               0.0\n            ],\n            [  \n               -122.421062569067274,\n               37.808601056818148,\n               0.0\n            ],\n            [  \n               -122.422003528252475,\n               37.808480096967251,\n               0.0\n            ]\n         ]\n      ]\n   }\n}<\/pre>\n
Looks good!<\/p>\n
Note: The size of JSON documents stored in JSON columns is limited to the value of the <\/i>max_allowed_packet<\/i><\/a> system variable. (While the server manipulates a JSON value internally in memory, it can be larger; the limit applies when the server stores it.)<\/i>.<\/p>\n
Our JSON document will be stored in an InnoDB table: features<\/i><\/p>\n
CREATE TABLE features (\n id int(11) NOT NULL AUTO_INCREMENT,\n feature json NOT NULL,\n PRIMARY KEY (id)\n) ENGINE=InnoDB;<\/pre>\n
Another way to store JSON documents is to put them in a string (VARCHAR or TEXT).<\/p>\n
Let’s see if there are some differences between JSON documents stored in a string or in a JSON column.<\/p>\n
CREATE TABLE features_TEXT (\n id int(11) NOT NULL AUTO_INCREMENT,\n feature longtext NOT NULL,\n PRIMARY KEY (id)\n) ENGINE=InnoDB;<\/pre>\n
Note: TEXT type is not large enough to handle our JSON data. (ERROR 1406 (22001): Data too long for column ‘feature’ at row 17360). LONGTEXT will do the job<\/em>.<\/p>\n
Populate tables<\/h1>\n
Note: In order to have a better idea of query execution time on my old (and not so stable) laptop with a small MySQL config (e.g. Buffer pool = 128MB), I ran the queries many time using <\/i>mysqlslap<\/i><\/a>: mysqlslap -c1 -i <N> { Concurrency = 1 \/ Iteration > 20 (depending on the query duration) }<\/i><\/p>\n
So most of the time I’ll show mysqlslap output e.g. \u00ab\u00a0Minimum number of seconds to run all queries: 59.392 seconds<\/i>\u00a0\u00bb<\/p>\n
provides by mysqlslap instead of regular query output e.g. \u00ab\u00a0Query OK, 206560 rows affected (59.39 sec)<\/em>\u00ab\u00a0.<\/p>\n
Copy JSON data in features<\/span><\/p>\n
LOAD DATA INFILE 'properties.json' INTO TABLE features (feature);<\/pre>\n
Minimum number of seconds to run all queries: 59.392 seconds<\/b><\/span><\/p>\n
Copy JSON data in features_TEXT<\/span><\/p>\n
LOAD DATA INFILE 'properties.json' INTO TABLE features_TEXT (feature);<\/pre>\n
Minimum number of seconds to run all queries: 39.784 seconds<\/b><\/span><\/p>\n
Loading 206560 records into my respective tables shows performance difference about 40% slower in JSON column compare to TEXT.<\/p>\n
However, be aware that MySQL JSON data type provides:<\/p>\n