How to store JSON in MySQL Database

Developers use MySQL databases in every corner of the world to create cloud-based applications. As they continually look for tools that offer better scalability, performance, and flexibility, many are pairing MySQL with the JSON data format.

Combined, these provide a wealth of benefits for developers. We’re going to briefly examine the ins and outs of MySQL and JSON to get you up to speed, then take a look at some of the things you can achieve using them together.

What is MySQL?

MySQL is a free, open-source relational database management system (RDBMS). It implements structured query language (SQL), the standard language for relational database management systems.

MySQL offers a robust, scalable solution for a variety of data processing requirements. Free versions of MySQL are available for anyone, but more advanced versions are available as paid software. 

The benefits of MySQL have seen it find uses across a wide range of industries.

Image sourced from enlyft.com

Key Features

MySQL has several features that make it an attractive choice as an RDBMS:

Performance

MySQL offers good data processing performance. It can accommodate multiple users and be accessed from any location, making it a popular choice for dispersed teams and remote developers working at all stages of thedata engineer pipeline.

Scalability 

MySQL is scalable and flexible and can be used by businesses of all sizes in various industries. It can be run either from your local system or directly from adedicated server. MySQL is scalable and flexible and can be used by businesses of all sizes in various industries. It can handle large volumes of data and offers options for horizontal scaling through techniques like sharding or replication. 

By implementing sharding, you can distribute the data across multiple servers, allowing for increased storage capacity and improved performance as the application grows. Replication enables you to create multiple copies of the database, providing high availability and the ability to handle more read requests. These scalability features of MySQL ensure that your application can efficiently handle increasing data processing requirements and accommodate the growth of your system.

Compatibility 

MySQL is compatible with many popular operating systems, including Windows, macOS, and Linux. It's also compatible with the majority of web development tools and can be integrated easily with services like cloud-based data warehouses. Additionally, MySQL's compatibility extends tosocial media tools, allowing seamless integration and data exchange between your MySQL database and social media platforms for efficient management and analysis of social media data.

Ease of Use

As DBMS systems go, MySQL is considered very easy to use. It features an easy-to-learn interface and is easy to install and set up. Even users with no prior experience with MySQL can create and populate a database or activate a WordPress install with little trouble.

What is JSON?

JavaScript Object Notation (JSON) is a lightweight text data exchange format, mainly used for transmitting data between servers and web applications. 

It is a schemaless format which operates on ordered lists and stores data using key-value pairs. JSONis supported by most modern programming languages.

JSON’s syntax supports values, strings, nulls, booleans, and objects and arrays based on it.

To send data across a network using JSON, you must first convert it into a JavaScript object. JSON objects are available, which help to streamline JSON conversions.   

Key Features

JSON’s usage has continued to expand in recent years.

 Image sourced from medium.com

JSON has several features that make it an attractive choice over alternatives such as XML and a critical tool for professionals working inDevOps. 

Lightweight 

JSON is a lightweight and compact format. The average JSON string is roughly two-thirds the size of the same data expressed in XML. 

Simple 

JSON is simple text. This makes it easy to learn, read, and understand. It can be displayed and edited in simple text editors and is suitable for sharing across platforms that may not support more complex document types.

Self-Describing

In some cases, the hierarchical structures and syntax of JSON strings can be interpreted by applications, even if they don’t know what data to expect.

Scalable 

JSON is language independent, meaning it works with almost all modern programming languages, making it suitable for many developers working in various use cases. For example, it has applications in website building,QA testing services like Global App Testing, and data extraction.

What Are The Benefits of Using JSON in MySQL?

Deploying the JSON format in MySQL can help you greatly enhance your data management, especially when it comes to user interactions and conversions. For example, you can leverage JSON to capture and analyze user behavior, track events, and implement personalized experiences such asexit intent popupsto reduce bounce rates and increase engagement. 

Exit intent popups can reduce bounce rates and increase engagement by capturing the attention of users who are about to leave a website, presenting them with targeted offers or compelling content that encourages them to stay and explore further, ultimately decreasing the likelihood of immediate abandonment and increasing the chances of conversion or prolonged engagement on the site.

Here are just a few of the advantages of using JSON in MySQL.

Convenience 

JSON features an inline syntax for its columns, which means that document queries can be easily integrated using SQL.

Performance 

A JSON MySQL connection can providehigh speed query performance. The functional indexes feature uses the values inside the JSON columns to create indexes, which speeds up the whole experience.

Efficiency

As well as being fast, a JSON MySQL connection is also very efficient. Any data saved in a  JSON column in MySQL is converted into a binary format rather than stored as plain text, allowing quick and easy access when using an array and objects.

Document Validation

JSON columns only accept validated documents, so a JSON MySQL connection ensures that all data is validated automatically, and without having to manually perform data validation, it saves you even more time.

This helps provide a secure environment in many scenarios, including providing secure remote accessIoT technologies.

What Are the Limitations of Using JSON in MYSQL?

While a connection between JSON and MySQL has many benefits, there are also a few drawbacks that you should be aware of.

No Comments 

JSON does not allow for comments or annotations, so you’ll need to use additional documentation to annotate fields, which may slow you down in some scenarios.

Single Number Type 

JSON operates solely on the IEEE-754 double-precision floating-point format. This means that input number types cannot be diversified. If developers need to insert a date, they must use a string when utilizing JSON, as no fixed date data type is offered, either. 

No Schema

JSON doesn’t feature a fixed schema, which can benefit experienced developers as it offers them total flexibility. For rookie developers, however, this can increase the risk of misshapen data being accidentally inserted. 

Using JSON in MySQL 

Now that we’ve got a good background knowledge of MySQL and JSON, it’s time to look at how they can be used together. We’ll focus on how to use JSON in MySQL to create a simple table containing product information for a clothing store’s website. 

(While we’re on the subject of websites, if you’re looking for a new domain for your website, have you considered the benefits of .io domains)?

Our example uses the JSON_EXTRACT function to extract and organize all the data you need from the database.

Build a Dataset 

The first step is to build a new MySQL dataset.

Connect to your MySQL server by inputting the command:

sudo mysql -u root -p

You’ll need to enter your root password to log in. Then, you can proceed to create a json_test database and switch to it:

mysql> CREATE DATABASE json_test;

mysql> USE json_test; 

mysql> CREATE table products (
    product_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    brand VARCHAR(50)
    gender VARCHAR(50)
    attributes JSON
    ) ENGINE = InnoDB;

Insert Data

Now that you’ve built your table, you can begin to insert data into it. 

You can store the attributes of your products in the variable ‘@attributes’:

mysql> SET @attributes = (‘{
                           “type”:“shirt”
                           “size”:“xl”
                           “colour”:{
                                   “primary”:“blue”
                                   “secondary”:“white”
                           },
                           “fabric”:“cotton”
                  }’);

Now you can use the INSERT statement to store the product information in the dataset you created. The ‘@attributes’ variable will represent the various attributes you’ve applied to the product:

mysql> INSERT INTO products (brand, gender, attributes) VALUES
(‘Armani’, ‘Male’, @attributes);

Extract Data

Using the MySQL JSON_EXTRACT function, you can extract the value of a named key in the database. For example:

mysql> SELECT
        Brand,
        gender,
              JSON_EXTRACT(attributes, ‘$.size’)  as size
              FROM products;

This input will provide you with the brand and gender of the product, as well as its size.

Update Data

Should you need to update any data in the JSON fields, you can use the JSON_INSERT and JSON_REPLACE functions.

Each function will require a path expression that will specify which parts of the JSON object are to be modified.

UPDATE `products`
SET `attributes` = JSON_INSERT(
        `attributes` ,
        '$.style' ,
        'long sleeve'
)

This will add the style ‘long sleeve’ to all appropriate items, providing the property does not exist for those objects. 

If you wish to replace the gender of certain products, you can do so using the JSON_REPLACE function:

UPDATE `products`
SET `attributes` = JSON_REPLACE(
        `attributes` ,
         $.gender' ,
         'unisex'
)

This should replace the gender with unisex for all applicable items, if the property already exists for those objects.

Deleting Data

Should you wish to delete a particular key or value from your JSON columns, you can use the JSON_REMOVE function. 

UPDATE `products`
SET `attributes` = JSON_REMOVE(`attributes` , '$.style')

You can also use DELETE and JSON_EXTRACT to remove entire columns from the database:

DELETE FROM `products`
WHERE JSON_EXTRACT(`attributes` , '$.brand') LIKE '%armani%';

This would remove any Armani products from the database.

Sorting 

JSON values can be sorted using the ORDER BY and GROUP BY commands. 

To sort values in ascending order, you should use SQL NULL before all JSON values.

To sort values in descending order, use SQL NULL after all JSON values.

The max_sort_length variable is a constraining factor for JSON values. Keys that only exhibit differences after the first max_sort_length byte will be treated as equals.

maged sourced from unsplash.com

Accomplish More With MySQL and JSON

We’ve looked at some of the most basic functions you can accomplish with JSON and MySQL, and you can already see the benefits of using these flexible, scalable, and easy-to-use tools. 

As you further develop your skills, there are other actions you will be able to perform, such as SQL server pivot rows to columns, which will allow you to rotate the values of a tabled expression and give the unique values from one column their own individual columns. 

Once you’ve got your head wrapped around MySQL and JSON, the sky really is the limit. Have a go at using them to develop cloud apps, and see what you can accomplish.

Author:

Pohan Lin - Senior Web Marketing and Localizations Manager #1:

Pohan Lin is the Senior Web Marketing and Localizations Manager at Databricks, a global Data and AI provider connecting the features of data warehouses and data lakes to create lakehouse architecture. With over 18 years of experience in web marketing, online SaaS business, and ecommerce growth. Pohan is passionate about innovation and is dedicated to communicating the significant impact data has in marketing. Here is his LinkedIn.

Fully managed Jakarta EE Deployment. ✅Handles Kubernetes for you.✅ 15 day free trial available.✅

Payara Cloud.

TRY FREE.