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.
Conclusion
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.