Difference between Arrow Operator -> and ->> in MySQL

MySQL

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.
  • ->> Operator
    • Double-arrow ->> is for accessing and converting.
    • Returns the extracted value as a string.
    • Extracts the value and unquotes it.

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:

SQL
-- 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:

SQL
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.

See also  Difference between CREATE INDEX and ALTER TABLE ADD INDEX in MySQL

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:

SQL
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.

SQL
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:

SQL
-- 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}');
SQL
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.

SQL
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.

See also  How to See Running Queries in MySQL - SHOW PROCESSLIST

Here’s a tabular representation of the differences between the -> and ->> operators in MySQL when working with JSON data:

Feature-> Operator->> Operator
PurposeExtracts JSON objects or arrays with original JSON format.Extracts scalar values and returns them as strings.
Alias ofJSON_EXTRACT()JSON_UNQUOTE(JSON_EXTRACT())
Result TypePreserves the original JSON format.Returns extracted values as strings.
UsageUsed for extracting JSON objects or arrays.Used for extracting scalar values.
ExampleSELECT 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 ValuesPreserves numeric data type if the value is a number.Always returns the extracted value as a string, even if it’s a number.
ConvenienceUseful 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:

Leave a Reply

Your email address will not be published. Required fields are marked *