What is MySQL JSON Connection: 7 Important Aspects

Developers all around the world utilize MySQL Databases to develop secure cloud-based applications. This open-source platform provides them with high performance and scalability to deploy their projects in a hassle-free manner. Moreover, the portable MySQL server enables developers to operate on any operating system without having to worry about setup configurations. Some popular platforms like Facebook, Twitter, WordPress, and many more,  seamlessly manage their vast amounts of data using MySQL.

To take Data Management one step further, companies are drawn toward Dynamic Websites, where one can update data asynchronously. To facilitate such Websites with MySQL, companies store the data in the flexible JSON (JavaScript Object Notation) Data Format. It is a schema-free notation that provides convenient ways to manage structured data and promotes interdomain data exchange.

This article will introduce you to MySQL and JSON along with their importance and features. It will further explain the steps using which you can manually set up the MySQL JSON Connection. Moreover, the article will discuss the various operations that you can perform on the JSON data in MySQL. Read along to understand the MySQL Json Connection and learn its benefits and limitations!

What is MySQL?

MySQL, created in 1995, is a powerful Database Management System (DBMS) running on Structured Query Language (SQL). MySQL’s general version is an open-source platform and is easily accessible to anyone wishing to implement Data Management. However, to use the advanced versions of MySQL that will meet your specific needs, you must choose from several paid editions of this tool available in the market. The MySQL platform offers you a scalable, robust, and reliable solution for your business’s data processing needs.

Its ease of use gives MySQL an advantage over its contemporary DBMS platforms such as Microsoft SQL Server and Oracle Databases. Moreover, you can collaborate MySQL with any programming language and it can easily integrate with various operating systems like Linux, Unix, Windows, etc. So, based on your configuration, you can either choose to install MySQL on your local system or use it directly from a server. 

Key Features of MySQL

The following features make MySQL a must-have DBMS in the market: 

• HighPerformance: The MySQL Database offers high Data Processing performance and an easy-to-learn user interface that can accommodate multiple users. Furthermore, you can access it from any location. 
• Compatibility: MySQL provides robust, low latency transaction services. It is also compatible with web development tools and you can simply integrate it with any cloud-based data warehouse. 
• Scalability: MySQL adds high scalability and flexibility to your business irrespective of your industry. It also gels with many popular operating systems such as Linux, OS X, Windows, etc.

To get more details regarding MySQL, visit here.

What is JSON?

JSON is a widely-used text notation/format used for structured data. This schema-less format operates on ordered lists & uses key-value pairs to store data. JSON, launched as a derivation of JavaScript, is supported by most programming languages today. They have libraries from which you can retrieve the JSON Data Type. The key application of JSON is exchanging data among web clients and web servers. 

Since its inception around 15 years ago, JSON’s popularity has always increased. Today, most Web services that are available in the public domain, deploy JSON for their data exchange operations.JSON operate as a string, and are beneficial if you wish to send data across a network. However, you must first convert it into a JavaScript object. Afterward, you can access the transferred JSON data. JavaScript offers a global JSON Object that can streamline your JSON conversions and allow you to use this format easily. 

Why do we use JSON?

As the popularity of Asynchronous Java Scripts increased more and more websites are using this programming knowledge for functioning. These websites need to load data quickly in an asynchronous manner without causing page delays. Moreover, such websites, and social media platforms like Twitter, Flickr, and others use the RSS Feeds from these websites. Now loading these feeds from one website to other using JavaScript is not feasible. This is where JSON comes in!

JSON enables you to overcome the cross-domain constraints with its JSONP method. It implements a callback function that transfers data in JSON format from one domain to another.

To learn more about JSON, visit here

Creating JSON Values

A JSON array is a collection of values separated by commas and enclosed by the characters “[” and “]”:

["abc", 10, null, true, false]

A JSON object is made up of key-value pairs separated by commas and enclosed in the characters “{” and ” }”:

{"k1": "value", "k2": 10}

JSON arrays and objects can contain scalar values such as strings or numbers, the JSON null literal, or JSON Boolean true or false literals, as shown in the examples. Strings must be used as keys in JSON objects. Temporal scalar values (date, time, or DateTime) are also allowed:

["12:18:29.000000", "2015-07-29", "2015-07-29 12:18:29.000000"]

Within JSON array elements and JSON object key values, nesting is allowed:

[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}

You can also get JSON values by using a number of MySQL-supplied functions, as well as casting values of other types to the JSON type with CAST(value AS JSON).

JSON values are stored as strings in MySQL. Any string used in a context that requires a JSON value is parsed by MySQL, and if it isn’t valid as JSON, an error is generated. As the following examples show, these contexts include inserting a value into a column with the JSON data type and passing an argument to a function that expects a JSON value (usually shown as json doc or JSON val in the documentation for MySQL JSON functions):

• If the value is a valid JSON value, inserting it into a JSON column succeeds; if it is not, it fails:

mysql> CREATE TABLE t1 (jdoc JSON);
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 VALUES('[1, 2,');
ERROR 3140 (22032) at line 2: Invalid JSON text:
"Invalid value." at position 6 in value (or column) '[1, 2,'.

In such error messages, the positions for “at position N” are 0-based, but they should be regarded as rough indications of where the problem in a value actually occurs.

• The JSON TYPE() function takes a JSON argument and tries to convert it to a JSON value. If the value is valid, it returns its JSON type; otherwise, it returns an error:

mysql> SELECT JSON_TYPE('["a", "b", 1]');
+----------------------------+
| JSON_TYPE('["a", "b", 1]') |
+----------------------------+
| ARRAY                      |
+----------------------------+

mysql> SELECT JSON_TYPE('"hello"');
+----------------------+
| JSON_TYPE('"hello"') |
+----------------------+
| STRING               |
+----------------------+

mysql> SELECT JSON_TYPE('hello');
ERROR 3146 (22032): Invalid data type for JSON data in argument 1
to function json_type; a JSON string or JSON type is required.

MySQL uses the utf8mb4 character set and utf8mb4 bin collation to handle strings in JSON context. Other character sets’ strings are converted to utf8mb4 as needed. (Because ASCII and utf8 are subsets of utf8mb4, no conversion is required for strings in those character sets.)

Functions for composing JSON values from component elements exist as an alternative to writing JSON values using literal strings. JSON ARRAY() takes a list of values (which may or may not be empty) and returns a JSON array containing those values:

mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW())              |
+----------------------------------------+
| ["a", 1, "2015-07-27 09:43:47.000000"] |
+----------------------------------------+

JSON_OBJECT() takes a (possibly empty) list of key-value pairs and returns a JSON object containing those pairs:

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
+---------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc') |
+---------------------------------------+
| {"key1": 1, "key2": "abc"}            |
+---------------------------------------+

JSON MERGE PRESERVE() combines two or more JSON documents to produce the following result:

mysql> SELECT JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}');
+-----------------------------------------------------+
| JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}') |
+-----------------------------------------------------+
| ["a", 1, {"key": "value"}]                          |
+-----------------------------------------------------+
1 row in set (0.00 sec)

User-defined variables can be assigned JSON values:

mysql> SET @j = JSON_OBJECT('key', 'value');
mysql> SELECT @j;
+------------------+
| @j               |
+------------------+
| {"key": "value"} |
+------------------+

User-defined variables, on the other hand, cannot be of the JSON data type, so while @j in the preceding example looks like a JSON value and has the same character set and collation as a JSON value, it is not. Instead, when the result of JSON OBJECT() is assigned to the variable, it is converted to a string.

The character set of strings created by converting JSON values is utf8mb4 and the collation is utf8mb4 bin:

mysql> SELECT CHARSET(@j), COLLATION(@j);
+-------------+---------------+
| CHARSET(@j) | COLLATION(@j) |
+-------------+---------------+
| utf8mb4     | utf8mb4_bin   |
+-------------+---------------+

Because utf8mb4 bin is a binary collation, JSON values are case-sensitive when compared.

mysql> SELECT JSON_ARRAY('x') = JSON_ARRAY('X');
+-----------------------------------+
| JSON_ARRAY('x') = JSON_ARRAY('X') |
+-----------------------------------+
|                                 0 |
+-----------------------------------+

The null, true, and false literals in JSON are case sensitive as well, and must always be written in lowercase:

mysql> SELECT JSON_VALID('null'), JSON_VALID('Null'), JSON_VALID('NULL');
+--------------------+--------------------+--------------------+
| JSON_VALID('null') | JSON_VALID('Null') | JSON_VALID('NULL') |
+--------------------+--------------------+--------------------+
|                  1 |                  0 |                  0 |
+--------------------+--------------------+--------------------+

mysql> SELECT CAST('null' AS JSON);
+----------------------+
| CAST('null' AS JSON) |
+----------------------+
| null                 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST('NULL' AS JSON);
ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json:
"Invalid value." at position 0 in 'NULL'.

The JSON literals are case sensitive, unlike the SQL NULL, TRUE, and FALSE literals, which can be written in any letter case:

mysql> SELECT ISNULL(null), ISNULL(Null), ISNULL(NULL);
+--------------+--------------+--------------+
| ISNULL(null) | ISNULL(Null) | ISNULL(NULL) |
+--------------+--------------+--------------+
|            1 |            1 |            1 |
+--------------+--------------+--------------+

Case In some cases, inserting quote characters (” or ‘) into a JSON document is necessary or desirable. Assume you want to insert some JSON objects containing strings representing sentences stating some MySQL facts, each paired with an appropriate keyword, into a table created using the SQL statement below:

mysql> CREATE TABLE facts (sentence JSON);

This is one of the keyword-sentence pairs:

mascot: The MySQL mascot is a dolphin named "Sakila".

The MySQL JSON OBJECT() function can be used to insert this as a JSON object into the facts table. In this case, you must use a backslash to escape each quote character, as shown here:

mysql> INSERT INTO facts VALUES
     >   (JSON_OBJECT("mascot", "Our mascot is a dolphin named "Sakila"."));

If you want to insert the value as a JSON object literal, you’ll need to use the double backslash escape sequence, which looks like this:

mysql> INSERT INTO facts VALUES
     >   ('{"mascot": "Our mascot is a dolphin named "Sakila"."}');

When you use the double backslash, MySQL skips the escape sequence processing and instead sends the string literal to the storage engine to be processed. By performing a simple SELECT after inserting the JSON object in either of the ways just shown, you can see that the backslashes are present in the JSON column value:

mysql> SELECT sentence FROM facts;
+---------------------------------------------------------+
| sentence                                                |
+---------------------------------------------------------+
| {"mascot": "Our mascot is a dolphin named "Sakila"."} |
+---------------------------------------------------------+

You can use the column-path operator -> to look up this specific sentence with mascot as the key, as shown here:

mysql> SELECT col->"$.mascot" FROM qtest;
+---------------------------------------------+
| col->"$.mascot"                             |
+---------------------------------------------+
| "Our mascot is a dolphin named "Sakila"." |
+---------------------------------------------+
1 row in set (0.00 sec)

The backslashes, as well as the surrounding quote marks, are preserved. Use the inline path operator ->> to display the desired value with the mascot as the key, but without the surrounding quote marks or any escapes, as in:

mysql> SELECT sentence->>"$.mascot" FROM facts;
+-----------------------------------------+
| sentence->>"$.mascot"                   |
+-----------------------------------------+
| Our mascot is a dolphin named "Sakila". |
+-----------------------------------------+

Searching and Modifying JSON Values

In a JSON document, a JSON path expression selects a value.

Path expressions can be used with functions that extract or modify parts of a JSON document to specify where they should operate within that document. The query below, for example, extracts the value of the member with the name key from a JSON document:

mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan"                                               |
+---------------------------------------------------------+

The JSON document under consideration is represented by a leading $ character, which is optionally followed by selectors that indicate successively more specific parts of the document:

• The member in an object with the given key is identified by a period followed by a key name. If the name without quotes is not legal within path expressions, the key name must be specified within double quotation marks (for example, if it contains a space).

• The value at position N within an array is named by appending [N] to a path that selects an array. Integers beginning with zero are used to represent array positions. Path[0] evaluates to the same value as path: if the path does not select an array value.

mysql> SELECT JSON_SET('"x"', '$[0]', 'a');
+------------------------------+
| JSON_SET('"x"', '$[0]', 'a') |
+------------------------------+
| "a"                          |
+------------------------------+
1 row in set (0.00 sec)

• [M to N] denotes a subset or range of array values, beginning at position M and ending at position N. The index of the rightmost array element is supported as a synonym for last. The array elements can also be addressed relative to one another. Path[last] evaluates to the same value as path if path does not select an array value.

• and ** wildcards can be used in paths:
  • The value of all members in a JSON object is evaluated by.[*].
  • The values of all elements in a JSON array are evaluated by [*].
• All paths that begin with the named prefix and end with the named suffix are evaluated by prefix**suffix.

Let’s use $ to refer to this three-element JSON array:

[3, {"a": [5, 6], "b": 10}, [99, 100]]

Then:

• The value of $[0] is 3.

• “a”: [5, 6], “b”: 10 are the values of $[1].

• The value of $[2] is [99, 100].

• The value of $[3] is NULL (it refers to the fourth array element, which does not exist).

Because $[1] and $[2] return nonscalar values, they can be used as the foundation for more specific path expressions that select nested values. Examples:

• The value of $[1].a is [5, 6].
• The value of $[1].a[1] is 6.
• The value of $[1].b is 10.
• The value of $[2][0] is 99.

If the unquoted key name is not legal in path expressions, path components naming keys must be quoted, as previously stated. This value is denoted by $:

{"a fish": "shark", "a bird": "sparrow"}

Both keys have a space in them and must be quoted:

• $”a fish” is equivalent to “shark.”

• $”a bird” is equivalent to “sparrow.”

When you use wildcards in a path, you get an array with multiple values:

mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*');
+---------------------------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*') |
+---------------------------------------------------------+
| [1, 2, [3, 4, 5]]                                       |
+---------------------------------------------------------+
mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]');
+------------------------------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]') |
+------------------------------------------------------------+
| [3, 4, 5]                                                  |
+------------------------------------------------------------+

The path $**.b evaluates to multiple paths ($.a.b and $.c.b) in the following example, resulting in an array of path values:

mysql> SELECT JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');
+---------------------------------------------------------+
| JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b') |
+---------------------------------------------------------+
| [1, 2]                                                  |
+---------------------------------------------------------+

Ranges from JSON arrays:. Ranges and the to keyword can be used to specify subsets of JSON arrays. $[1 to 3], for example, includes the array’s second, third, and fourth elements, as shown here:

mysql> SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');
+----------------------------------------------+
| JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]') |
+----------------------------------------------+
| [2, 3, 4]                                    |
+----------------------------------------------+
1 row in set (0.00 sec)

M to N is the syntax, where M and N are the first and last indexes of a range of elements from a JSON array, respectively. M must be greater than or equal to 0, and N must be greater than or equal to 0. The index of an array element starts with 0.

Ranges can be used in situations where wildcards are allowed.

Rightmost array element: The last keyword can be used as a synonym for the index of the array’s last element. For relative addressing and within range definitions, expressions of the form last – N can be used, such as this:

mysql> SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-3 to last-1]');
+--------------------------------------------------------+
| JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-3 to last-1]') |
+--------------------------------------------------------+
| [2, 3, 4]                                              |
+--------------------------------------------------------+
1 row in set (0.01 sec)

When the path is applied to a value that isn’t an array, the result is the same as if the value had been wrapped in a single-element array:

mysql> SELECT JSON_REPLACE('"Sakila"', '$[last]', 10);
+-----------------------------------------+
| JSON_REPLACE('"Sakila"', '$[last]', 10) |
+-----------------------------------------+
| 10                                      |
+-----------------------------------------+
1 row in set (0.00 sec)

Some functions take an existing JSON document, make changes to it, and then return the modified document. Path expressions specify where in the document changes should be made. The JSON SET(), JSON INSERT(), and JSON REPLACE() functions, for example, each take a JSON document and one or more path-value pairs that specify where the document should be modified and what values should be used. The functions handle existing and non-existing values in the document differently.

Consider the following document:

mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]';

JSON SET() adds values to paths that don’t exist and replaces values for paths that do:

mysql> SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+--------------------------------------------+
| JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+--------------------------------------------+
| ["a", {"b": [1, false]}, [10, 20, 2]]      |
+--------------------------------------------+

The path $[1] in this case. b[0] replaces an existing value (true) with the value specified by the path argument (1). Because the path $[2][2] does not exist, the value (2) is added to the value chosen by $[2].

JSON INSERT() adds new values to the array but does not replace them:

mysql> SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+-----------------------------------------------+
| JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+-----------------------------------------------+
| ["a", {"b": [true, false]}, [10, 20, 2]]      |
+-----------------------------------------------+

JSON REPLACE() ignores new values and replaces them with existing ones:

mysql> SELECT JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+------------------------------------------------+
| JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+------------------------------------------------+
| ["a", {"b": [1, false]}, [10, 20]]             |
+------------------------------------------------+

Left to right, the path-value pairs are evaluated. The document created by evaluating one pair becomes the new value used to evaluate the next pair.

JSON REMOVE() takes a JSON document and one or more paths that specify which values should be removed. The returned value is the original document minus the values chosen by the document’s paths:

mysql> SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]');
+---------------------------------------------------+
| JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]') |
+---------------------------------------------------+
| ["a", {"b": [true]}]                              |
+---------------------------------------------------+

These are the effects of the paths:

• $[2] removes the [10, 20] match.
• $[1] is used for the first time.
• b[1] removes the false value from the b element.
• This is the second time $[1] has been used. b[1] has no effect because that element has already been removed and the path no longer exists.

What is the JSON Path Syntax?

A path is made up of one or more path legs and the scope of the path. The scope of paths used in MySQL JSON functions is always the document being searched or otherwise operated on, as indicated by the $ character at the beginning. Period characters divide the legs of the path (.). [N], where N is a non-negative integer, is the symbol for cells in arrays. Double-quoted strings or valid ECMAScript identifiers must be used for key names (see Identifier Names and Identifiers, in the ECMAScript Language Specification). The ASCII, utf8, or utf8mb4 character sets should be used to encode path expressions, just like JSON text. utf8mb4 is implicitly converted from other character encodings. Here’s how to write the complete syntax:

pathExpression:
    scope[(pathLeg)*]

pathLeg:
    member | arrayLocation | doubleAsterisk

member:
    period ( keyName | asterisk )

arrayLocation:
    leftBracket ( nonNegativeInteger | asterisk ) rightBracket

keyName:
    ESIdentifier | doubleQuotedString

doubleAsterisk:
    '**'

period:
    '.'

asterisk:
    '*'

leftBracket:
    '['

rightBracket:
    ']'

As previously stated, the scope of the path in MySQL is always the document being worked on, denoted by $. In JSON path expressions, ‘$’ can be used as a synonym for the document.

The wildcard tokens * and ** are used in the following way:

• The value of all members in the object is represented by.*.
• The value of all cells in the array is represented by [*].

All paths beginning with prefix and ending with suffix are represented by [prefix]**suffix. The prefix is optional, but the suffix is mandatory; in other words, a path cannot end in **.

Furthermore, the sequence *** may not appear in a path.

See the descriptions of JSON CONTAINS PATH(), JSON SET(), and JSON REPLACE() for examples of path syntax (). See the JSON SEARCH() function’s description for more examples of how to use the * and ** wildcards.

The to keyword (such as $[2 to 10]) in MySQL 8.0 also supports range notation for subsets of JSON arrays, as well as the last keyword as a synonym for the array’s rightmost element.

Converting between JSON and non-JSON values

The rules that MySQL follows when casting between JSON values and values of other types are summarised in the table below:

other type	CAST(other type AS JSON)	CAST(JSON AS other type)
JSON	No change	No change
utf8 character type (utf8mb4, utf8, ascii)	The string is parsed into a JSON value.	The JSON value is serialized into a utf8mb4 string.
Other character types	Other character encodings are implicitly converted to utf8mb4 and treated as described for utf8 character type.	The JSON value is serialized into a utf8mb4 string, then cast to the other character encoding. The result may not be meaningful.
NULL	Results in a NULL value of type JSON.	Not applicable.
Geometry types	The geometry value is converted into a JSON document by calling ST_AsGeoJSON().	Illegal operation. Workaround: Pass the result of CAST(json_val AS CHAR) to ST_GeomFromGeoJSON().
All other types	Results in a JSON document consisting of a single scalar value.	Succeeds if the JSON document consists of a single scalar value of the target type and that scalar value can be cast to the target type. Otherwise, returns NULL and produces a warning.

The principles of ORDER BY and GROUP BY for JSON values are as follows:

• The same rules apply to the ordering of scalar JSON values as they did in the previous section.
• SQL NULL comes before all JSON values, including the JSON null literal, in ascending sorting; SQL NULL comes after all JSON values, including the JSON null literal, in descending sorting.
• Because the value of the max sort length system variable is bound by the sort keys for JSON values, keys that differ only after the first max sort length bytes compare as equal.
• Nonscalar value sorting is currently not supported, and a warning is displayed.

Casting a JSON scalar to another native MySQL type can be useful for sorting. Use this expression to sort by id values if a column named jdoc contains JSON objects with a member consisting of an id key and a nonnegative value:

ORDER BY CAST(JSON_EXTRACT(jdoc, '$.id') AS UNSIGNED)

The MySQL optimizer recognizes this and considers using the index for the query execution plan if a generated column is defined to use the same expression as the ORDER BY.

How to Validate JSON Data?

MySQL JSON data type validates data for you automatically. It ensures that only valid JSON data can be entered into the field.

MySQL also has a function for determining whether a string is a valid JSON field. If you’re accepting a JSON string from another system, this can be useful.

JSON VALID is the name of the function. You give it a value, and it returns 1 if the string is valid JSON and 0 if it isn’t.

The syntax is as follows:

JSON_VALID (value)

Example of Valid JSON String:

Consider the following JSON string as an example:

'{"color": "black", "depth": "100cm", "material": "wood"}'

You can see if it’s correct by looking for quotes and other symbols in the appropriate places. We could also simply pass it to the JSON VALID function.

SELECT
JSON_VALID(
  '{"color": "black", "depth": "100cm", "material": "wood"}'
) AS valid_test;

valid_test

1

Example of an Invalid JSON String:

This is an example JSON string:

'{"color": "black", "depth" "100cm", "material": "wood"}'

It may appear to be valid at first glance. To check, we’ll use the JSON VALID function.

SELECT
JSON_VALID(
  '{"color": "black", "depth" "100cm", "material": "wood"}'
) AS valid_test;

valid_test

0

Using the JSON Structure in MySQL

You can easily implement JSON format in your MYSQL Database using the following steps:

• Step 1: Build a New MySQL Dataset
• Step 2: Insert Data into Datasets
• Step 3: Extracting Data from the Database

Step 1: Build a New MySQL Dataset

Connect to your MySQL server using the following code:

$ sudo mysql -u root -p

Log in to the MySQL server using your root password. Now, create a json_test database and switch to the newly generated dataset using the following code:

mysql> CREATE DATABASE json_test;
mysql> USE json_test;

Now, create a customers table to store your customer information. Use customer_id as the primary key and create columns like first_name, last_name, address, etc., according to your requirements. it will be a good practice to create the address column using the JSON data type to allow dynamic addresses. The following code will help you in building your customer table:

mysql> CREATE table customers (
         customer_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
         first_name VARCHAR(50),
         last_name  VARCHAR(50),
         address    JSON
       ) ENGINE = InnoDB;

Step 2: Insert Data into Datasets

Once the table is ready, you can insert the required data from JSON to MySQL. A good practice is to first test the syntax of your data, especially for the address part. You can perform the syntax check using the following code using the JSON_VALID function:

mysql> SELECT JSON_VALID('{
                           "street":"95 Yemen RD. NW #2",
                           "town":"WEST HAM",
                           "state":"ENG",
                           "zip":1232,
                           "phone":{
                              "home":123451,
                              "work":272734
                           },
                           "available_in_day_time":true
                        }') AS is_valid;

The following output will imply a correct syntax:

+----------+
| is_valid |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

Now, store the address in a variable “@address”

mysql> SET @address = ('{
                          "street":"95 Yemen RD. NW #2",
                           "town":"WEST HAM",
                           "state":"ENG",
                           "zip":1232,
                           "phone":{
                              "home":123451,
                              "work":272734
                           },
                           "available_in_day_time":true


                       }');

Now, use the INSERT statement to store the customer’s data in your dataset created in Step 1. You must use the @address variable to represent the customer address information. The code to insert data is as follows:

mysql> INSERT INTO customers (first_name, last_name, address) VALUES ('Chris', 'Evans', @address);

Similarly, you can add new entries to this MySQL Dataset.

Step 3: Extracting Data from the Database

You can easily retrieve the value of any named key from the MySQL Database by using the MySQL JSON_EXTRACT function. For example, the below code retrieves the customers’ names and town names:

mysql> SELECT
       first_name,
       last_name,
       JSON_EXTRACT(address, '$.town') as town
       FROM customers;

The below output will give you the desired result

+------------+-----------+--------------+
| first_name | last_name | town         |
+------------+-----------+--------------+
| Chris        | Evans       | "West Ham"   |
| John         | Kenny      | "New Shire"   |
+------------+-----------+--------------+

That’s it! Your MySQL JSON Connection is ready.

What is MySQL JSON Data Type?

Since MySQL 5.7.8, the native JSON data type has been supported. The native JSON data type is more efficient than the JSON text format in previous versions for storing JSON documents.

JSON documents are stored in MySQL in an internal format that allows for quick access to document elements. The JSON binary format is structured in such a way that the server can quickly search for values within the JSON document by key or array index.

A JSON document takes up about the same amount of space as a LONGBLOB or LONGTEXT file.

The following syntax is used to define a column with a JSON data type:

CREATE TABLE table_name (
    ...
    json_column_name JSON,
    ... 
);

A default value cannot be set for a JSON column. Furthermore, a JSON column cannot be directly indexed. Instead, you can use a generated column to create an index that contains values extracted from the JSON column. The MySQL optimizer will look for compatible indexes on virtual columns that match JSON expressions when you query data from the JSON column.

MySQL JSON data type example

Assume you need to keep track of your website’s visitors and their activities. Some visitors may simply browse the pages, while others may browse the pages and make purchases. You’ll make a new table called events to store this information.

CREATE TABLE events( 
  id int auto_increment primary key, 
  event_name varchar(255), 
  visitor varchar(255), 
  properties json, 
  browser json
);

Each event in the events table has a unique id that identifies it. A name for an event is given to it, such as pageview, purchase, and so on. The visitor column is used to keep track of visitors.

The JSON columns are the properties and browser columns. They’re used to keep track of event properties as well as the browser that visitors use to browse the site.

You must enter the following information into the events table:

INSERT INTO events(event_name, visitor,properties, browser) 
VALUES (
  'pageview', 
   '1',
   '{ "page": "/" }',
   '{ "name": "Safari", "os": "Mac", "resolution": { "x": 1920, "y": 1080 } }'
),
('pageview', 
  '2',
  '{ "page": "/contact" }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 2560, "y": 1600 } }'
),
(
  'pageview', 
  '1',
  '{ "page": "/products" }',
  '{ "name": "Safari", "os": "Mac", "resolution": { "x": 1920, "y": 1080 } }'
),
(
  'purchase', 
   '3',
  '{ "amount": 200 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1600, "y": 900 } }'
),
(
  'purchase', 
   '4',
  '{ "amount": 150 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1280, "y": 800 } }'
),
(
  'purchase', 
  '4',
  '{ "amount": 500 }',
  '{ "name": "Chrome", "os": "Windows", "resolution": { "x": 1680, "y": 1050 } }'
);

The column path operator (->) is used to extract values from JSON columns.

SELECT id, browser->'$.name' browser
FROM events;

The following is the result of this query:

+----+-----------+
| id | browser   |
+----+-----------+
|  1 | "Safari"  |
|  2 | "Firefox" |
|  3 | "Safari"  |
|  4 | "Firefox" |
|  5 | "Firefox" |
|  6 | "Chrome"  |
+----+-----------+
6 rows in set (0.00 sec)

The data in the browser column is enclosed in quotation marks. The inline path operator (->>) is used to remove the quote marks as follows:

SELECT id, browser->>'$.name' browser
FROM events;

The quote marks have been removed, as shown in the following output:

+----+---------+
| id | browser |
+----+---------+
|  1 | Safari  |
|  2 | Firefox |
|  3 | Safari  |
|  4 | Firefox |
|  5 | Firefox |
|  6 | Chrome  |
+----+---------+
6 rows in set (0.00 sec)

You can use the following statement to get the browser usage:

SELECT browser->>'$.name' browser, 
      count(browser)
FROM events
GROUP BY browser->>'$.name';

The query’s result is as follows:

+---------+----------------+
| browser | count(browser) |
+---------+----------------+
| Safari  |              2 |
| Firefox |              3 |
| Chrome  |              1 |
+---------+----------------+
3 rows in set (0.02 sec)

The following query is used to calculate the total revenue generated by the visitor:

SELECT visitor, SUM(properties->>'$.amount') revenue
FROM events
WHERE properties->>'$.amount' > 0
GROUP BY visitor;

The following is the result:

+---------+---------+
| visitor | revenue |
+---------+---------+
| 3       |     200 |
| 4       |     650 |
+---------+---------+
2 rows in set (0.00 sec)

What are the Operations on MySQL JSON?

Now, that you have understood how to use JSON with MYSQL, you can perform the following key operations on the JSON data stored in your MySQL Database:

• MySQL JSON Operations: Normalization
• MySQL JSON Operations: Merging and Auto Wrapping
• MySQL JSON Operations: Sorting and Type Conversion
• MySQL JSON Operations: Aggregation

1) MySQL JSON Operations: Normalization

When you parse a string in a JSON document, normalization is also required. This normalization is done to prevent ambiguity and redundancy. The MySQL engine automatically performs normalization when you insert a JSON document. The following code shows the inserted data

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');

After normalization, it is converted to:

+------------------------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def') |
+------------------------------------------------------+
| {"key1": "def", "key2": "abc"}                       |
+------------------------------------------------------+

In the MySQL versions launched before MySQL 8.0.3, the normalization was also carried out while values into JSON columns:

mysql> CREATE TABLE t1 (c1 JSON);

mysql> INSERT INTO t1 VALUES
     >     ('{"x": 17, "x": "red"}'),
     >     ('{"x": 17, "x": "red", "x": [3, 5, 7]}');
mysql> SELECT c1 FROM t1;

The normalization will result in:

+-----------+
| c1        |
+-----------+
| {"x": 17} |
| {"x": 17} |
+-----------+

MySQL, apart from normalization, also discards any extra whitespace between values, keys, or elements from the original JSON document. 

2) MySQL JSON Operations: Merging and Auto Wrapping

You can merge multiple arrays to create a unified common array holding all the data in one place. The JSON_MERGE_PRESERVE() function concatenates arrays in such a way that arrays named later are appended to the end of the first array. Moreover, the JSON_MERGE_PATCH() considers each argument as an array of a single element and implements “last duplicate key wins” normalization to select only the last argument. The following code shows the process of merging 3 arrays:

mysql> SELECT
    ->   JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]', '[true, false]') AS Preserve,
    ->   JSON_MERGE_PATCH('[1, 2]', '["a", "b", "c"]', '[true, false]') AS PatchG
*************************** 1. row ***************************
Preserve: [1, 2, "a", "b", "c", true, false]
   Patch: [true, false]

After normalization, the merged array reduces to:

mysql> SELECT
    ->   JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') AS Preserve,
    ->   JSON_MERGE_PATCH('{"a": 3, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') AS PatchG
*************************** 1. row ***************************
Preserve: {"a": [1, 4], "b": 2, "c": [3, 5], "d": 3}
   Patch: {"a": 4, "b": 2, "c": 5, "d": 3}

Auto Wrapping involves merging arrays with objects by combining values or by “last duplicate key wins” (depending on the chosen merging function JSON_MERGE_PRESERVE() or JSON_MERGE_PATCH()). The following code represents auto wrapping:

mysql> SELECT
	  ->   JSON_MERGE_PRESERVE('[10, 20]', '{"a": "x", "b": "y"}') AS Preserve,
	  ->   JSON_MERGE_PATCH('[10, 20]', '{"a": "x", "b": "y"}') AS PatchG
*************************** 1. row ***************************
Preserve: [10, 20, {"a": "x", "b": "y"}]
   Patch: {"a": "x", "b": "y"}

3) MySQL JSON Operations: Sorting and Type Conversion

The ORDER BY and GROUP BY commands are used to sort JSON values and they operate on the following rules:

• For sorting in ascending order, use SQL NULL before all JSON values, while for descending sorts use SQL NULL after all JSON values.

• The Sort keys for JSON values are constrained by the max_sort_length variable. This implies the keys that are different only after the first max_sort_length bytes will be compared as equal.

• MySQL does not support the sorting of nonscalar values

The Conversion from JSON to Non-JSON and vice versa work according to the following table:

The following code can both cast and sort JSON values using “id”, at the same time:

ORDER BY CAST(JSON_EXTRACT(jdoc, '$.id') AS UNSIGNED)

4) MySQL JSON Operations: Aggregation

To aggregate JSON values, MySQL ignores the NULL values and converts the Non-NULL values into a numeric type. The MIN(), MAX(), and GROUP_CONCAT() functions do not require this numeric conversion, however, the other aggregation function will always convert the values into numeric first.

The conversion of Non-NULL values to several JSON types should always produce a meaningful result. To maintain this, MySQL often truncates values and allows a loss of precision. However, if the conversion is not of JSON type, then there is no guarantee that you will get a meaningful result.

What are the Benefits of MySQL JSON Connection?

You can enhance your Data Management using MySQL by deploying the JSON format. MySQL parse JSON will provide you with the following benefits:

• Document Validation: The MySQL JSON connection ensures that your data will be validated automatically. This is due to the constraint that JSON columns only accept valid documents. Therefore, you never have to worry about manually performing the data validation every time you insert something new. 
• High Performance: The JSON MySQL connection can enhance your query performance in terms of speed. It creates indexes based on values inside the JSON columns. The “functional indexes” feature performs this task.
• Convenience: JSON offers an inline syntax for its columns. This makes it very easy for you to integrate Document queries using SQL in a hassle-free manner.
• Efficient Access: The data that you save in a JSON column in MySQL, is never stored as plain text. Instead, JSON data is converted into a binary format that is optimal for easy and fast access when using an array and objects.

What are the Limitations of MySQL JSON Connection?

Using JSON format for MySQL comes along with the following limitations:

• No Schema: JSON has no fixed schema which provides you with total flexibility for representing your Data. However,  a lack of schema can create issues for beginners who may accidentally insert misshapen data.
• Only a Single Number Type: JSON operates on the IEEE-754 double-precision floating-point format only. This implies that you cannot diversify your input number types like other programming languages. Moreover, JSON does not offer a fixed Date data type, thus developers are forced to use a string when they need to insert dates.
• No Comments: JSON does not allow comments, thus making it impossible to add annotations to fields. To overcome this you need to use additional documentation which will cost extra time and may create confusion. 
• Verbosity: JSON is better equipped than XML when it comes to data interchanging. However it often lacks precision and for more accurate conversions, you will have to rely on more efficient techniques.

Conclusion

The article introduced you to MySQL & JSON along with their important features. It then discussed the 3 steps using which you can easily set up your MySQL JSON Connection. Furthermore, the article explained several operations that you can perform on the JSON data in MySQL. The article also covered the benefits and limitations associated with the MySQL JSON Connection.

Visit our Website to Explore Hevo

Now, to run SQL queries or perform Data Analytics on your MySQL data, you first need to export this data to a Data Warehouse. This will require you to custom code complex scripts to develop the ETL processes. Hevo Data can automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc. This platform allows you to transfer data from 100+ multiple sources like MySQL to Cloud-based Data Warehouses like Amazon Redshift, Snowflake, Google BigQuery, etc. It will provide you with a hassle-free experience and make your work life much easier.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Share your understanding of MySQL JSON Connection in the comments below!