MySQL JSON

JSON abbreviated as JavaScript Object Notation. It is a lightweight data-interchange format similar to other data types and can be easily read and write by humans. It can also be parsed and generate by machines easily.

Generally, the JSON data type supports two structures:

A collection of name/value pairs chain, which acts as a data array.
An ordered list of values.

Since it manages the individual values in a name-value pair chain that acts as a data array, we can retrieve the whole field using a single command. This useful feature allows us to retrieve the data in a large system quickly.

MySQL provides supports for native JSON data type from version 5.7.8 that stores JSON document in an internal format, which enables quick and efficient read access to document objects. This data type can store JSON documents more accurately than the JSON text format we had used in the past MySQL versions.

The following are the advantages that the JSON data type gives over storing JSON-format strings:

The JSON columns allow us to store the automatic validation of JSON documents. Otherwise, we will get an error.
Optimized/Fast storage format means when the server reads a JSON value stored in binary format, it does not need to parse from a text representation. The binary format allows searching values within JSON documents directly with a key or array indexes without reading whole values.

The storage space required for the JSON document is roughly the same as the storage requirements for LONGBLOB and LONGTEXT.

We can define the JSON data type column in the MySQL table using the following index:

NOTE: It is to note that we cannot store a non-null default value in the JSON column. Also, the JSON column cannot be indexed directly because it creates an index by extracting a scalar value from the JSON column. If we want to retrieve data from the JSON column, the MySQL optimizer searches compatible indexes that match the JSON expressions.

Why we use JSON?

We will use the JSON data type in MySQL because of its use-cases, where we can use a make-shift approach. Let us understand it with the help of an example.

Suppose we are creating a web application and want to save a user’s configurations or preferences in the table. Generally, we used to create a separate table that contains user_id, key, and value fields or saves it as a formatted string so that it can be parsed at runtime. This method is good for limited users. If the user’s list will be large and more configuration/preference keys, this method is not good.

To overcome these issues, MySQL allows us to use a JSON data type field for storing the user’s configurations or preferences that saves the table’s space and stores the records separately, which will be the same as the number of visitors who visits the website.

MySQL JSON Data Type Example

Suppose we want to track the user and their actions who visit our website, such as some users only see the pages and others would visit the page and buy the products. Let’s create a new table called “events” that will store this information using the following statement:

  CREATE TABLE events(     event_id INT AUTO_INCREMENT PRIMARY KEY,     event_name varchar(75),     visitors varchar(25),     properties json,     browser_name json  );  

Event id is used to uniquely identify each event in the events table. The event name stores the name of an event such as page-view, purchase, etc. The visitor column stores the user information who visits the websites, and properties and browser_name columns are used to keep the JSON values. Browser_name column stores the browser’s specifications such as browser name, operating system, resolution, etc. that visitors use to browse the website.

Next, we will insert the data into this table using the INSERT statement given below:

  INSERT INTO events (event_name, visitors, properties, browser_name)   VALUES (    ‘page-view’,      ‘2’,     ‘{“page”: “/”}’,     ‘{ “name”: “Google Chrome”, “OS”: “Windows”, “resolution”: {“x”: 1920, “y”: 1080} }’  ),  (‘page-view’,     ‘3’,    ‘{“page”: “/products”}’,    ‘{ “name”: “Safari”, “OS”: “UNIX”, “resolution”: {“x”: 2560, “y”: 1600} }’  ),  (    ‘page-view’,     ‘1’,    ‘{“page”: “/contacts”}’,    ‘{ “name”: “Mozilla Firefox”, “OS”: “Mac”, “resolution”: {“x”: 1920, “y”: 1080} }’  ),  (    ‘purchase’,      ‘4’,    ‘{“amount”: 250}’,    ‘{ “name”: “Google Chrome”, “OS”: “Windows”, “resolution”: {“x”: 1280, “y”: 800} }’  ),  (    ‘purchase’,      ‘3’,    ‘{“amount”: 350}’,    ‘{ “name”: “Safari”, “OS”: “Mac”, “resolution”: {“x”: 1600, “y”: 900} }’  ),  (    ‘purchase’,     ‘4’,    ‘{“amount”: 400}’,    ‘{ “name”: “Mozilla Firefox”, “OS”: “Windows”, “resolution”: {“x”: 1280, “y”: 800} }’  );  

Now, we can verify the table, execute the SELECT statement like below:

We will see the output as follows:

MySQL JSON

Suppose we want to retrieve any particular values such as browser name from the JSON columns. We can filter these types of the result by using the column path operator (->). See the below query:

It will return the below output:

MySQL JSON

In this image, you will notice that double quotation marks surround the browser column’s data. If you want to get the browser column’s data without any quotation marks, we need to use the inline path operator (->>) as follows:

In the below output, we can see that the quotation marks have been removed successfully.

MySQL JSON

If we want to retrieve the browser usage, we can use the statement as follows:

  mysql> SELECT browser_name->>’$.name’ AS browser, count(browser_name)      FROM events GROUP BY browser_name->>’$.name’;  

We will get the below result:

MySQL JSON

If we want to calculate the visitor’s total revenue, we can use the query as follows:

  mysql> SELECT visitors, SUM(properties->>’$.amount’) As total_revenue  FROM events WHERE properties->>’$.amount’ > 0 GROUP BY visitors;  

We will get the result as follows:

MySQL JSON

Next TopicMySQL Standard Deviation

MySQL JSON

MySQL JSON

Why we use JSON?

MySQL JSON Data Type Example

MySQL Import CSV File in Database/Table

MySQL regexp_like() function

You may also like