Introduction
In the ever-evolving landscape of database management, MySQL stands out as a reliable and powerful choice. One of its strengths lies in its support for JSON data, allowing developers to handle complex and nested structures seamlessly. In this article, we’ll explore the potential of two essential operators in MySQL when working with JSON data: -> and ->>.
->Operator- Single-arrow
->is for directly accessing the JSON. - Returns the extracted value in its original JSON format.
- Extracts the value without unquoting it.
- Single-arrow
->>Operator- Double-arrow
->>is for accessing and converting. - Returns the extracted value as a string.
- Extracts the value and unquotes it.
- Double-arrow
The -> Operator
The -> operator serves as an alias for the JSON_EXTRACT(). It is a JSON path operator used to extract JSON objects or arrays from a JSON document. Unlike ->>, it preserves the original JSON format, providing a powerful tool for working with nested structures.
Example Usage
Let’s create a table named example_table with a JSON column named json_column and insert some sample data:
-- Create the table
CREATE TABLE example_table (
id INT AUTO_INCREMENT PRIMARY KEY,
json_column JSON
);
-- Insert some sample data with a numeric value
INSERT INTO example_table (json_column) VALUES
('{"user": {"name": "John", "age": 30, "address": {"city": "New York", "zipcode": "10001"}}}');To extract the nested address object, you would use the -> operator as follows:
SELECT json_column->'$.user.name'
FROM example_table;
Output:
+----------------------------+
| json_column->'$.user.name' |
+----------------------------+
| "John" |
+----------------------------+
SELECT json_type(json_column->'$.user.name')
FROM example_table;
Output:
+---------------------------------------+
| json_type(json_column->'$.user.name') |
+---------------------------------------+
| STRING |
+---------------------------------------+This query returns the user.name as a JSON object, maintaining its structure for further processing.
The ->> Operator
This is an improved, unquoting extraction operator. Whereas the -> operator simply extracts a value, the ->> operator in addition unquotes the extracted result. It returns the extracted value as a string, regardless of its original data type. The ->> operator is equivalent to JSON_UNQUOTE(JSON_EXTRACT()).
Example Usage:
Building on the previous example, suppose you want to extract the city from the address:
SELECT json_column->>'$.user.name'
FROM example_table;
Output: -- Extracted value as string and quotes removed
+-----------------------------+
| json_column->>'$.user.name' |
+-----------------------------+
| John |
+-----------------------------+This query returns user.name as string and with quotes removed.
SELECT json_type(json_column->>'$.user.name')
FROM example_table;
Output:
ERROR 3141 (22032) at line 1: Invalid JSON text in argument 1 to function json_type: "Invalid value." at position 0.This query returns error as json_type expect json value but extracted result is a string.
Handling Numeric Values:
It’s crucial to note that while ->> returns numeric values as strings, -> preserves the numeric data type. For instance, with the following JSON data:
-- Create the table
CREATE TABLE example_table2 (
id INT AUTO_INCREMENT PRIMARY KEY,
json_column JSON
);
-- Insert some sample data with a numeric value
INSERT INTO example_table2 (json_column) VALUES
('{"numeric_value": 42}'),
('{"numeric_value": 322}'),
('{"numeric_value": 273}'),
('{"numeric_value": 8}');SELECT id, json_column->>'$.numeric_value'
FROM example_table2 order by json_column->>'$.numeric_value' desc;
Output:
+----+---------------------------------+
| id | json_column->>'$.numeric_value' |
+----+---------------------------------+
| 4 | 8 |
| 1 | 42 |
| 2 | 322 |
| 3 | 273 |
+----+---------------------------------+Using ->> would return the numeric value as string and would order the result lexicographically.
SELECT id, json_column->'$.numeric_value'
FROM example_table2 order by json_column->'$.numeric_value' desc;
Output:
+----+--------------------------------+
| id | json_column->'$.numeric_value' |
+----+--------------------------------+
| 2 | 322 |
| 3 | 273 |
| 1 | 42 |
| 4 | 8 |
+----+--------------------------------+Using -> would return the numeric value as actual number and would order the result numerically.
Here’s a tabular representation of the differences between the -> and ->> operators in MySQL when working with JSON data:
| Feature | -> Operator | ->> Operator |
|---|---|---|
| Purpose | Extracts JSON objects or arrays with original JSON format. | Extracts scalar values and returns them as strings. |
| Alias of | JSON_EXTRACT() | JSON_UNQUOTE(JSON_EXTRACT()) |
| Result Type | Preserves the original JSON format. | Returns extracted values as strings. |
| Usage | Used for extracting JSON objects or arrays. | Used for extracting scalar values. |
| Example | SELECT json_column->'$.nested' AS extracted_json_object FROM your_table; | SELECT json_column->>'$.nested.inner_key' AS extracted_scalar_value FROM your_table; |
| Handling Numeric Values | Preserves numeric data type if the value is a number. | Always returns the extracted value as a string, even if it’s a number. |
| Convenience | Useful for dealing with nested JSON structures. | Convenient for extracting individual elements or scalar values. |
Understanding these differences will help you choose the appropriate operator based on your specific use case when working with JSON data in MySQL.
See more:
