EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL JSON Data Type

MySQL JSON Data Type

MySQL JSON Data Type

Introduction to MSQL JSON Data Type

MYSQL is a relational database management system that is open source, and in this article, we will see how it works with JSON Data Type. We will look at how to insert JSON data type into an MYSQL table and see various functions available in MYSQL to select further and query the JSON documents or objects. From version 5.7.8, we can even store JSON data natively, and MYSQL has a 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, strings, numbers, and Booleans. This format is also language-independent, so it doesn’t matter if you use Java, SQL, kotlin, ruby, or any other language it is compatible with. To learn more information about JSON format, you can refer to https://www.json.org/json-en.html. This article will show how MYSQL interacts with JSON and what functions are available. Following is the table that we are using for the demonstration purpose to show the usage of MSQL for JSON.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

MySQL JSON Data Type-1.1

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

MySQL JSON Data Type-1.2

MySQL JSON Data Type-1.3

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 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, a JSON object, and add its string representation, 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 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.

MySQL JSON Data Type-1.4

The next question is how to 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:

MySQL JSON Data Type-1.5

In this query, we select person_data, which is in the json format, and then we use the JSON_EXTRACT method to get the value of the identified 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:

MySQL JSON Data Type-1.6

In this query, we have used three 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 ensure the key is always in quotes. Then JSON_TYPE will give the data type stored; in this case, we have to 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:

MySQL JSON Data Type-1.7

Code:

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

Output:

MySQL JSON Data Type-1.8

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

2. Merge Preserve

Syntax:

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

Code:

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

Output:

1.9

Code:

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

Output:

1.10

In these two queries, we preserve the old value along with the new value as we can see in the first query, the values are duplicated, and in the 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:

1.11

In this query, we created one test string representing a JSON object and another variable we want to search in the object. Then we used the JSON_CONTAINS function, which takes three parameters: a target to search in, the search value, and the property. The return value is either one if found or 0 if not found. A null value is returned if any arguments are null or 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:

1.12

Code:

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

Output:

1.13

These queries utilize the JSON_CONTAINS_PATH function, which takes an input JSON and determines the existence of specified paths based on the provided arguments. It returns 1 if all paths exist or if at least one of the paths exists; otherwise, it returns 0. The first query uses all parameters, but only one path exists, which leads to a return value of 0. In the second query, only one parameter is used, and there is one existing path, resulting in a return value of 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. We have also learned various helper methods that make it easy to manipulate the stored JSON values.

Recommended Articles

We hope that this EDUCBA information on “MySQL JSON Data Type” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL vs MSSQL
  2. UUID in MySQL
  3. MySQL Data Type
  4. SQL Data Types
C++ PROGRAMMING Certification Course
38+ Hours of HD Videos
9 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ASP.NET Certification Course
149+ Hours of HD Videos
28 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SQL - Everything in SQL
253+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SOFTWARE TESTING Certification Course
74+ Hour of HD Videos
13 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
MYSQL Certification Course
 115+ Hours of HD Videos
18 Courses
3 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more