MySQL JSON Data Type

Introduction to MSQL JSON Data Type

MYSQL is an open-source relational database management system and in this article, we will see how it works with JSON Data Type. We will look how to insert JSON data type into a MYSQL table and we will also see various functions available in MYSQL to further select and query the JSON documents or objects. From version 5.7.8 we can even store JSON data natively and MYSQL has whole bunch of datatype and functions to manage it.

Getting Started with JSON

JSON or JavaScript Object Notation is a format for data exchange that usually consists of objects, arrays, string, numbers, and Booleans. This format is also language-independent, so it doesn’t matter if you are using java, SQL, kotlin, ruby or any other language it is compatible with it. To know more information about JSON format you can refer to https://www.json.org/json-en.html. In this article, we will see how MYSQL interacts with JSON and what functions are available. Following is the table which we are using for the demonstration purpose to show the usage of MSQL has for JSON.

Then we will add a column with the type of JSON to store the native JSON data. The database is setup in phpMyAdmin and for the ease of access Datagrip from Jetbrains is used for querying and altering the database.

First, we will add a column person_data of the type JSON using the following query.

alter table people
add column person_data JSON

Then we will use the following insert statement. Before using it make sure that person_id is a primary key and also autoincrement.

insert into people(first_name, last_name, person_data)
values ('Clark', 'Kent', '{"identity": "Superman"}'),
('Bruce', 'Wayne', '{"identity": "Batman"}'),
('Bruce', 'Banner', '{"identity": "Hulk"}')

In this query we add first name, last name and person_data which is a JSON object and we are adding the string representation of it which is typically in the form of key and value pairs. The value can consist of null, true or false but all must be in lower case and it is case sensitive whereas mysql is case insensitive like Select NULL, Null, null this all will work fine.

As we can see our data is added in JSON form.

The next question is how we can select the required data from the entire json data, for this, there are many helper methods in MYSQL the first one is JSON_EXTRACT

Syntax: 

JSON_EXTRACT(json_doc, path[, path] ...)
JSON_UNQUOTE(json_val)

Code:

select person_data,
JSON_EXTRACT(person_data, '$.identity') as secret_id,
JSON_UNQUOTE(JSON_EXTRACT(person_data, '$.identity')) as super_secret_id
from people
where JSON_UNQUOTE(JSON_EXTRACT(person_data, '$.identity')) = "Superman";

Output:

In this query we select person_data which is in the json format and then we use JSON_EXTRACT method to get the value of the identify property using a dollar symbol. It takes 2 parameters the column name or expression and the position or property. Notice that the results are in quotes. So, we can use JSON_UNQUOTE and supply the extracted value to get an unquoted value. We can even use it in the where clause to compare the value of the result.

Create Array and Objects Using MYSQL

Syntax:

JSON_ARRAY([val[, val] ...])
JSON_OBJECT([key, val[, key, val] ...])
JSON_TYPE(json_val)

Code:

select json_array(1, true, "name", "2009-09-12") as array,
json_object("a", 1, "b", "Steve", "c", true, "d", "null") as object,
json_type(person_data) as type
from people;

Output:

In this query we have used 3 functions, JSON_ARRAY will take all the valid json types and create an array as you can see with square brackets in the result. JSON_OBJECT will take the comma-separated key and value pairs and make sure that the key is always in quotes. Then JSON_TYPE will give the type of the data stored and in this case we have store the entire json object.

Merge Functions in MYSQL for JSON

Merge functions in mysql for json are explained below:

1. Merge Patch

Syntax:

JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)

Code:

select json_merge_patch('{"name":"Steve"}', '{"name":"Steve"}');

Output:

Code:

select json_merge_patch('{"name":"Steve"}', '{"name":"Dave"}');

Output:

In these 2 queries we have used JSON_MERGE_PATCH function which will merge the existing value the new value by patching or replacing it. As we can see in first query the value is same and in second the new value is replaced.

2. Merge Preserve

Syntax:

JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)

Code:

select json_merge_preserve('{"name":"Steve"}', '{"name":"Steve"}');

Output:

Code:

select json_merge_preserve('{"name":"Steve"}', '{"name":"Dave"}')

Output:

In these 2 queries we preserve the old value along with the new value as we can see in first query the values are duplicated and in second query the new value is appended.

Search JSON Values

Syntax:

JSON_CONTAINS(target, candidate[, path])

Code:

SET @test_string_object = '{"a": 1, "b": 2, "c": {"d": 4}}';
SET @search_value = '1';
SELECT JSON_CONTAINS(@test_string_object, @search_value, '$.a') as check_if_exist;

Output:

In this query, we have created one test string that represent a JSON object and another variable which we want to search in the object. Then we have used the JSON_CONTAINS function which takes 3 parameters, the first is a target to search in, then the search value, and then the property. The return value is either 1 if found or 0 if not found. A null value is returned if any of the arguments are null or if the path is invalid.

Syntax:

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

Code:

SET @test_string_object = '{"a": 1, "b": 2, "c": {"d": 4}}';
SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');

Output:

Code:

SET @test_string_object = '{"a": 1, "b": 2, "c": {"d": 4}}';
SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');

Output:

In these queries we use JSON_CONTAINS_PATH which takes the input json and depending on the one or all argument it will determine whether all path exist or one of the paths exist and return 1 or 0. In the first query, we have used all parameter and only one exist so it returns 0 and in the second query, we use one parameter and one path exist so it returns 1.

Conclusion – MySQL JSON Data Type

Hopefully, now you know how to use JSON data with the MYSQL database, we have learned how to insert JSON values in an MYSQL table along with that we have learned various helper methods that makes easy to manipulate the stored JSON values.

Recommended Articles

This is a guide to MSQL JSON Data Type. Here we also discuss the introduction and create array and objects using mysql along with an explanation. You may also have a look at the following articles to learn more –

1. MySQL vs MSSQL
2. MySQL UUID
3. MySQL Data Type
4. SQL Data Types