top of page

Taking the MySQL 5.7 JSON features

Updated: Mar 26, 2019

MySQL 5.7 make known to both a new natural JSON datatype, and a set of SQL functions to be bright to operate and search data in a very accepted means on the server-side. Today I needed to show a modest of example of these features in act using example data from SF OpenData.

Importing Example Data

Taking good example data is suitable, as it helps you self-validate that outcomes are precise. It also benefits make available good data dispersal, which is significant when addition indexes.

My selected data set beginning SF OpenData is the most common item further down “Geographic Locations and Boundaries” and covers roughly 200K city lots. The first stage is to download and import it into MySQL:

Here is an sample what each one of the structures (parcel of land) expressions like:

In this situation all 200K documents do track a mutual format, but I must point ready that this is not a prerequisite. JSON is schema-less

Sample Queries

Query #1: Find a piece of land on Market street, one of the main streets in San Francisco:

By means of the short hand JSON_EXTRACT operator (->) I can request into a JSON column in a very usual way. The syntax "$.properties.STREET" is what we sound a JSON path, and for individuals aware with javascript I like to relate this to a CSS selector like to what you would use with jQuery.

To study more round the JSON path syntax, I mention checking out guide page.

Query #2: Find any parcels of land that do not specify a street:

With JSON presence schema less, this finds the forms which do not take the projected structure. In this sample we can see that all forms have a $.properties.STREET stated, and thus the request yields zero outcomes.

Comparing the JSON type to TEXT

In this sample I am in succession a request which intentionally needs to contact all 200K JSON documents. This might be well-thought-out a micro-benchmark, as it does not fairly replicate what you would knowledge in creation, where you will frequently have indexes:

To describe what is trendy here in more detail:

  • For ease, I’ve guaranteed that in both samples the dataset fits in memory.

  • The JSON functions, as well as the short-hand json_extract() operator (->) will work on both a natural JSON data type, as glowing TEXT/BLOB/VARCHAR data types. This is very beneficial because it delivers a nice advancement for users previous to MySQL 5.7 who regularly before now store JSON.

  • We can understand that the natural JSON datatype is definitely about 10x faster than TEXT – 1.25 seconds as opposed to 12.85 seconds. This can be described because the natural type does not have to do any analyzing or authentication of the data, and it can regain elements of a JSON document very proficiently.


With a bit of luck this helps as a useful sample of importing example JSON data, and consecutively a few example requests. In my next tutorial I will show some more information about json data aggregates.

9 views0 comments

Recent Posts

See All

What are the future prospects of Java

According to a survey conducted, nearly 63% programmers mentioned that they will likely continue to work with Java along with coding with Python, SQL and HTML/CSS. In addition, the giants in the corpo

Deleting Duplicate Rows in MySQL

In this blog, you will study several ways to delete duplicate rows in MySQL. In this blog, I have shown you how to find duplicate values in a table. Once the duplicates rows are recognized, you may ne

Upload Data to MySQL tables using mysqlimport

Uploading quite a lot of rows of data from a text, csv, and excel file into a MySQL table is a repetitive task for sysadmins and DBAs who are handling MySQL database. This blog clarifies 4 applied exa


Couldn’t Load Comments
It looks like there was a technical problem. Try reconnecting or refreshing the page.
bottom of page