JSON data is a wonderful way to store data without needing a
schema but what about when you have to yank that data out of the
database and apply some sort of formatting to that data?
Well, then you need JSON_TABLE.

JSON_TABLE takes free form JSON data and applies some formatting
to it. For this example we will use the world_x
sample database's countryinfo table. What is
desired is the name of the country and the year of independence
but only for the years after 1992. Sound like a SQL query
against JSON data, right? Well that is exactly what we are
doing.

We tell the MySQL server that we are going to take the $.Name and
$.IndepYear key's values from the JSON formatted doc column
in the table, format them into a string and a integer
respectively, and alias the key value's name to a table column
name that we can use for qualifiers in an SQL statement.

MySQL is not only a relational database, but can also be used as
a schemaless/NOSQL document store, or a mix of
both. This is realized by the JSON datatype, and is useful
for any data that is hard to fit in the ”tabular” format of a
traditional table.…

JSON in Flat File to MySQL DatabaseSo how do you load that JSON
data file into MySQL. Recently I had this question presented to
me and I thought I would share a handy script I use to do such
work. For this example I will use the US Zip (postal) codes from
JSONAR. Download and unzip the file. The data file
is named zips.json and it can not be bread directly into
MySQL using the SOURCE command. It needs to have the information
wrapped in a more palatable fashion.

The VideoThe find() function for the MySQL Document Store is a very powerful tool
and I have just finished a handy introductory video. By the way --
please let me have feed back on the pace, the background music,
the CGI special effects (kidding!), and the amount of the
content. The ScriptFor those who want to follow along with the
videos, the core examples are below. The first step is to connect
to a MySQL server to talk to the world_x schema
(Instructions on loading that schema at the first link above).

\connect root@localhost/world_x

db is an object to points to the world_x schema. To
find the records in the countryinfo collection, use
db.countryinfo.find(). But that returns 237 JSON
documents, too many! So lets cut it down to …

I am starting a series of videos on the MySQL Document Store. The Document Store
allows those who do not know Structured Query Language (SQL) to
use a database without having to know the basics of relational
databases, set theory, or data normalization. The goal is to have
sort 2-3 minute episodes on the various facets of the Document
Store including the basics, using various programming languages
(Node.JS, PHP, Python), and materializing free form schemaless,
NoSQL data into columns for use with SQL.

JSON Data Into MySQLJSON is now a very hot format for sharing
data and MySQL's 5.7 Data Set is now a very hot way of storing
that data. But I have had some requests on getting a raw JSON
data set into MySQL. So lets start with Global Airfields data from the Awesome JSON Datasets collection.
airfields.jsonThe data is in a file named airfields.json
and comes as one very long line. MySQl has now way of taking out
the individual JSON documents from that string and putting them
in a row. The jq tool will let use see the data broken down into
objects. Try jq -C '.[]' airfields.json and you will see
individual documents colorized.

Welcome to the first day of the Percona Live
Open Source Database Conference: Percona Live 2017 tutorials
day! While technically the first day of the conference, this day
focused on provided hands-on tutorials for people interested in
learning directly how to use open source tools and technologies.

Today attendees went to training sessions taught by open source
database experts and got first-hand experience configuring,
working with, and experimenting with various open source
technologies and software.

Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.