JSON data type

Introduction

Relational databases provide a very strict data schema, which avoids all kinds of data anomalies and non-consistent states. However, this can sometimes interfere with a more flexible table structure: even adding new fields to a table may be very inconvenient. For this reason, SQL databases have begun to support JSON. With JSON it's simpler to add new fields. You can also create indexes on those fields and easily search by JSON field values. Using JSON can save your resources and reduce the number of database entries and additional queries.

Since most database management systems are organized around a single data model, it could be said that by supporting JSON, relational databases became somewhat multi-model.

The JSON format is used to store data by large corporations such as Google, Microsoft, Apple, and Amazon. According to Google Trends, JSON has the highest search interest among all formats. JSON is the preferred format for storing structured information in databases. To exchange database information with services, you need to be able to work with JSON in SQL. Let's have a look at the power of JSON and learn how to work with it in MySQL.

Along with the JSON data type, MySQL has a set of SQL functions that allow you to create, merge, query, modify, compare, and order. Are you excited to get started? So, let's learn how to create and query the JSON format.

Creating JSON Values in MySQL

Without thinking twice, create a table with the JSON format field. Simply specify the column format as JSON:

CREATE TABLE gifts (doc JSON);

A JSON value can be presented in 3 forms: an array, an object, and a nested object.

JSON array

Suppose you have a list of presents you want for Christmas and you want Santa to know about all of them, not just one. Well, the JSON array will help you here!

A JSON array contains a list of values separated by commas and enclosed in square brackets.

['Aston Martin', 1, true, 'love', null]

To add this JSON array to the table, you need to use double quotes for each internal array object and single quotes for the whole array.

INSERT INTO gifts 
VALUES ('["Aston Martin", 1, true, "love", null]');

MySQL has built-in functions for working with JSON format. To simplify adding new arrays, you can use the JSON_ARRAY function.

INSERT INTO gifts 
VALUES (JSON_ARRAY('Aston Martin', 1, true, 'love', null));

>>> ["Aston Martin", 1, true, "love", null]

JSON object

Santa Claus has seen your array and says that he has limited funds, but in a few years he will be able to give you all the gifts you've listed. However, now he asks you to prioritize the list of gifts by urgency. A JSON object can help you with that.

A JSON object contains key-value pairs separated by commas and enclosed in curly braces. Keys must be strings, while values must be a valid JSON data type (string, number, object, array, boolean or null).

{'ASAP': 1, 'second': 'love', '3': 'Aston Martin', 'random': null}

Again, add double quotes for internal key-value pairs and single quotes for the entire JSON object.

INSERT INTO gifts 
VALUES ('{"ASAP": 1, "second": "love", "3": "Aston Martin", "random": null}');

You can use the JSON_OBJECT function to easily work with JSON objects.

SELECT JSON_OBJECT('ASAP', 1, 'second', 'love', '3', 'Aston Martin', 'random', null);

>>> {"3": "Aston Martin", "ASAP": 1, "random": null, "second": "love"}

In the output, you can see that there is an automatic sorting going on within the object. MySQL sorts JSON keys inside the object to improve performance. You can read more about it in the MySQL documentation.

JSON nested object

Crisis averted! Santa Claus is ready to give you many gifts this year. Now he's asking for a list of gifts for this year with their rank by urgency and a separate list of gifts for the years to come. To combine object and array, you can use a nested object.

A JSON nested object allows you to nest objects within each other.

[{'ASAP': 1, 'second': 'love', '3': 'Aston Martin', 'random': null}, ['house', 'health', 2000000]]

To combine objects and arrays into a nested object, use JSON_MERGE_PRESERVE. The function takes several JSON objects (at least 2 of them) and merges them.

SELECT JSON_MERGE_PRESERVE(
    JSON_OBJECT('ASAP', 1, 'second', 'love', '3', 'Aston Martin', 'random', null), 
    JSON_ARRAY('house', 'health', 2000000)
);


>>> [{"3": "Aston Martin", "ASAP": 1, "random": null, "second": "love"}, "house", "health", 2000000]

Great! We've learned how to order presents for Santa Claus. Now let's switch to another example: how about dogs? Let's make an overview of different dogs' characteristics for which we need to make queries.

Querying JSON data

The JSON_EXTRACT function allows you to retrieve data from a JSON object by specifying a path. This path is indicated with the $ symbol followed by options for selecting a specific part of the document.

• You can specify the JSON object key with a dot after the $ sign.

SELECT JSON_EXTRACT('{"name": "Dexter", "age": 7, "breed": "Labrador Retriever"}', '$.breed');

>>> "Labrador Retriver"

• After the $ sign you can specify the position number in square brackets, i.e. the index of the object you need in the JSON document. Indexing starts from 0.

SELECT JSON_EXTRACT('["Labrador Retriever", "German Shepherd", "Beagle", "Bulldog"]', '$[3]');

>>> "Bulldog"

• Using the keyword, you can specify the range to search for in the JSON object.

SELECT JSON_EXTRACT('["Labrador Retriever", "German Shepherd", "Beagle", "Bulldog"]', '$[1 to 3]');

>>> ["German Shepherd", "Beagle", "Bulldog"]

Conclusion

Now you know how great the JSON format is! Not only it has the advantages of flexibility, compactness, readability, but also it supports a lot of programming languages and helps you perform many useful operations.

MySQL has numerous special functions for the JSON format. In this topic, you got introduced to some of them. Now you can both create new tables using the JSON format and make queries to existing tables that use JSON format. We highly recommend reading the MySQL documentation to learn more about other JSON functions.