JSON functions in MySQL allow working with JSON data in the database and retrieve JSON data from it. These functions help in storing and accessing JSON data efficiently and such data is widely used in web applications as it is flexible and easy to use.
In this article, We will learn about the MySQL JSON Functions by understanding various functions in detail along with the examples and so on.
What are MySQL JSON Functions?
- MySQL JSON functions are procedures that are innately integrated in MySQL to offer the users the ability to create, manage, and manipulate JSON documents.
- These functions are needed to deal with JSON data stored in the MySQL tables and allow organizing the work with complex data.
Syntax
Here is the basic syntax for some common JSON functions:
Creating JSON Data:
SELECT JSON_OBJECT('key1', 'value1', 'key2', 'value2');
Extracting JSON Values:
SELECT JSON_EXTRACT(json_doc, path);
Modifying JSON Data:
SELECT JSON_SET(json_doc, path, value);
Why Use JSON in MySQL?
Using JSON in MySQL offers several advantages:
1. Flexibility
- JSON also has capability to store and manage the structured data with hierarchal structure. This in turn allows one to place objects and arrays contained in a single JSON document.
- It simplifies the representation of relationships between the data components when designing their structure.
2. Interoperability
- Their main advantages are that JSON is accepted in most cases as a data format, is used without reheating in different platforms and languages.
- Because of its light weight and text format, it is very easy to understand and create; thus it flows well and interoperates and exchanges data between different systems, applications and programming languages.
3. Efficiency
- JSON functions help in simplifying the task of using JSON data within the database by loading/sorting the data at the time of its use.
- MySQL’s native support of JSON operations also implies that there is no need for JSON to be parsed and further transformed before the command can work, which makes query responses faster and requires less computation time.
4. Scalability
- This flexibility makes it easier to add new fields and structures to JSON since adding new keys will not affect the current software database structures.
- When the application changes, however, for example perhaps weekly, this flexibility is useful in the database as it is able to adapt and expand as necessary without too much alteration.
5. Ease of Use
- Yet, JSON is quite simple and easily readable by developers due to being an extension of the JavaScript scripting language.
- Because of its easily understandable structure, data can be easily consigned, checked for mistakes and modified which overall saves development time.
6. Compatibility with NoSQL Features
- MySQL provides support for JSON in terms of functions, which makes it a middle ground between Relational and Non-relational/NoSQL databases.
- It means developers are facilitated with the benefits of both paradigms, the structured query to interact with data and the flexible NoSQL background to store data.
Examples of MySQL JSON Functions
Example 1: Creating JSON Data
Let's create a JSON object using JSON_OBJECT.
SELECT JSON_OBJECT('name', 'Alice', 'age', 25, 'city', 'Wonderland');
Output:
{
"name": "Alice",
"age": 25,
"city": "Wonderland"
}
Example 2: Extracting JSON Values
Let's extract a value from a JSON document using JSON_EXTRACT.
SELECT JSON_EXTRACT('{"name": "Alice", "age": 25, "city": "Wonderland"}', '$.age');
Output:
25
Explanation: This query extracts the value of the age key from the given JSON document.
Example 3: Modifying JSON Data
Let's modify a JSON document using JSON_SET.
SELECT JSON_SET('{"name": "Alice", "age": 25, "city": "Wonderland"}', '$.age', 26);
Output:
{
"name": "Alice",
"age": 26,
"city": "Wonderland"
}
Explanation: This query modifies the value of the age key in the JSON document from 25 to 26.
MySQL JSON Functions Overview
1. JSON_OBJECT
JSON_OBJECT is the other function which is used to built the JSON object from keys and values in a set. As also mentioned above, this function is especially essential when it comes to the formation of JSON data directly in the SQL statement.
2. JSON_ARRAY
JSON_ARRAY constructs a JSON array depending on the particular specified values on a list of values. These can used to represent ordered collections of elements.
Example:
SELECT JSON_ARRAY('Alice', 25, 'Wonderland');
Output:
["Alice", 25, "Wonderland"]
3. JSON_MERGE
The JSON_MERGE function combines two or more JSON documents into one. This is useful for aggregating data from multiple JSON objects.
Example:
SELECT JSON_MERGE('{"name": "Alice"}', '{"age": 25}');
Output:
{"name": "Alice", "age": 25}
4. JSON_REMOVE
The JSON_REMOVE function deletes a specified key or path from a JSON document.
Example:
SELECT JSON_REMOVE('{"name": "Alice", "age": 25}', '$.age');
Output:
{"name": "Alice"}
5. JSON_ARRAY_APPEND
The JSON_ARRAY_APPEND function adds a value to the end of a JSON array.
Example:
SELECT JSON_ARRAY_APPEND('["Alice", 25]', '$', 'Wonderland');
Output:
["Alice", 25, "Wonderland"]
6. JSON_SEARCH
The JSON_SEARCH function searches for a value within a JSON document and returns the path to the matching value.
Example:
SELECT JSON_SEARCH('{"name": "Alice", "city": "Wonderland"}', 'one', 'Wonderland');
Output:
"$.city"
Conclusion
Overall, MySQL JSON functions that are available help when one wants to use JSON within the MySQL database. These functions are specifically designed to create, extract and modify JSON documents which simplifies the data structure processes. When properly utilized, these functions allow the improvement of configurability.
Similar Reads
JSON Functions in PL/SQL
JSON (JavaScript Object Notation) has become more popular in modern applications, and databases like Oracle support JSON data. JSON is widely used in modern applications for its simplicity and flexibility, making it a popular format for exchanging data between systems. In this article, we will expla
5 min read
PL/SQL Functions
PL/SQL functions are reusable blocks of code that can be used to perform specific tasks. They are similar to procedures but must always return a value. A function in PL/SQL contains:Function Header: The function header includes the function name and an optional parameter list. It is the first part o
4 min read
TIME() Function in MySQL
The TIME() function in MySQL is used to extract the time portion from a date or datetime expression, returning the time in the format 'HH:MM'. This function is particularly useful when working with time components in databases, such as scheduling or logging systems. In this article, We will learn ab
4 min read
SQRT() Function in MySQL
The SQRT() function in MySQL calculates the square root of a non-negative number, returning NULL for negative inputs. It is a built-in function that provides high precision and is optimized for performance and making it ideal for mathematical and scientific applications. In the article, we will cove
4 min read
LEAST() Function in MySQL
The LEAST() function in MySQL is a versatile tool that returns the smallest (minimum) value from a list of expressions. It can be used with numbers, strings, or even columns of data to find the minimum value according to their data type. In this article, We will learn about LEAST() Function in MySQL
4 min read
MySQL NOW() function
The NOW() function in MySQL is a powerful tool that returns the current date and time based on the server's time zone. This function is widely used when you need to capture the exact moment an event occurs such as when recording timestamps for records like orders, deliveries, or log entries. In this
3 min read
GREATEST() function in MySQL
The GREATEST() function in MySQL is designed to return the largest value from a list of expressions. It is particularly useful for identifying the maximum value among multiple columns or literal values, simplifying tasks that involve comparisons of multiple values. In this article, We will learn abo
4 min read
COUNT() Function in MySQL
The COUNT() function in MySQL is a versatile aggregate function used to determine the number of rows or non-NULL values that match a specific condition in a query. It can be applied to an entire table or a particular column and is widely used in database operations to analyze the volume of data in a
3 min read
SQL General Functions
SQL general functions are built-in tools provided by SQL databases to perform a variety of operations on data. These functions are crucial for manipulating, analyzing, and transforming data efficiently. In this article, We will learn about the what are the SQL General Functions along with the exampl
5 min read
SQL | Numeric Functions
SQL Numeric Functions are essential tools for performing mathematical and arithmetic operations on numeric data. These functions allow you to manipulate numbers, perform calculations, and aggregate data for reporting and analysis purposes. Understanding how to use SQL numeric functions is important
4 min read