MySQL JSON Operations | Advantages and Limitations

JSON’s popularity has risen steadily since its inception nearly 15 years ago. JSON is used for data exchange by the vast majority of public Web services today. JSON functions as a string and are useful for sending data across a network. You must, however, convert it into a JavaScript object first. After that, you can access the JSON data that has been transferred. JavaScript includes a global JSON Object that simplifies JSON conversions and makes it simple to work with this format.

This article talks about MySQL JSON Operations in detail. It also gives an introduction to JSON.

Table of?Contents

• What is JSON?
• How to Create JSON Value?
• How to Search and Modify JSON Values?
• What is JSON Path Syntax?
• What are Operations on MySQL JSON?
• Conclusion

What is?JSON?

JSON is a text notation/format for structured data that is widely used. This schema-less format works with ordered lists and stores data in key-value pairs. Most programming languages now support JSON, which began as a derivation of JavaScript. They have libraries that can be used to get the JSON Data Type. JSON is primarily used to exchange data between web clients and web servers.

With JSON’s JSONP method, you can get around cross-domain limitations. It uses a callback function to transfer data from one domain to another in JSON format.

How to Create JSON?Value?

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

[“xyz”, 27, null, true, false]

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

{“z1”: “value1”, “z2”: 23}

Scalar values such as strings or numbers, the JSON null literal, and JSON boolean true or false literals can all be found in JSON arrays and objects. Strings are required for keys in JSON objects. In JSON, temporal scalar values (date, time, or DateTime) are also allowed.

Within JSON arrays and JSON object key values, nesting is also allowed.

You can also get JSON values by using several MySQL-provided functions or by casting values of other types to the JSON type using CAST (value AS JSON).

Some MySQL function that return JSON values are JSON_ARRAY,JSON_OBJECT,JSON_QUOTE

Etc. These functions take a list of values or key pairs and return a JSON array or object that contains them. In MySQL, JSON data is stored as strings. If a string is used in a context that requires a JSON value, MySQL parses it and generates an error if it isn’t valid as JSON.

You can convert a Non-JSON value to a JSON value using CAST. JSON values can be created by casting a string or any other type as a JSON value. There are some rules and principles that need to be followed while casting value of other types to JSON.

How to Search and Modify JSON?Values?

Searching JSON?Values

JSON Values can be searched using JSON path expressions.

A JSON path expression is a formula that selects a value from a JSON document.

Path expressions are useful with functions that extract parts of or modify a JSON document because they allow you to specify where you want to operate within that document.

A leading $ character denotes the JSON document in question, which can be followed by selectors that denote more specific parts of the document:

• A period followed by a key name identifies the member in an object with the specified key. If using a name without quotes in path expressions isn’t allowed, the key name must be enclosed in double quotation marks (for example, if it contains a space).
• By appending [N] to a path that selects an array, the value at position N in the array is named. Array positions are represented using integers beginning with zero. If the path doesn’t select an array value, path[0] evaluates to the same value as path:

mysql> SELECT JSON_SET(‘“x”’, ‘$[0]’, ‘a’);

+?—?—?—?—?—?+

| JSON_SET(‘“x”’, ‘$[0]’, ‘a’) |

+?—?—?—?—?—?+

| “a” |

+?—?—?—?—?—?+

1 row in set (0.00 sec)

• Starting at position M and ending at position N, [M to N] denotes a subset or range of array values. Last can be used to refer to the index of the rightmost array element. The array of elements can also be addressed in terms of how close they are to each other. If the path does not select an array value, path[last] returns the same result as the path.
• An example to search JSON Values using the path expression is given below:

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

• $[0] has the value of 3.
• $[1] has the values “a”: [5, 6], “b”: 10
• [99, 100] is the value of $[2.]
• $[3] has no value (it refers to the fourth array element, which does not exist).

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] |

+?—?—?—?—?—?—?—?—?—?— +

In the following example, the path $**.b evaluates to multiple paths ($.a.b and $.c.b), yielding 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] |

+?—?—?—?—?—?—?—?—?—?-+

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)

The syntax is M to N, where M and N are the first and last indexes of a range of JSON array elements, respectively. M and N must both be greater than or equal to zero. An array element’s index begins at zero.

In scenarios where wildcards are permitted, ranges can be used.

mysql> SELECT JSON_REPLACE(‘“Sakila”’, ‘$[last]’, 10);

+?—?—?—?—?—?—?—?+

| JSON_REPLACE(‘“Sakila”’, ‘$[last]’, 10) |

+?—?—?—?—?—?—?—?+

| 10 |

+?—?—?—?—?—?—?—?+

1 row in set (0.00 sec)

Modifying JSON?Values

Some functions modify an existing JSON document before returning it. Changes to the document should be made where path expressions specify. For example, the JSON SET(), JSON INSERT(), and JSON REPLACE() functions each accept a JSON document and one or more path-value pairs that specify where the document should be modified and what values should be used. Existing and non-existing document values are handled differently by the functions.

mysql> SET @j = ‘[“a”, {“b”: [true, false]}, [10, 20]]’;

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]] |

+?—?—?—?—?—?—?—?-+

In this case, the path $[1] b[0] substitutes the path argument’s value for an existing value (true) (1). The value (2) is added to the value selected by $[2] because the path $[2][2] does not exist.

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]] |

+?—?—?—?—?—?—?—?— +

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]] |

+?—?—?—?—?—?—?—?— +

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]}] |

+?—?—?—?—?—?—?—?— -+

What is JSON Path?Syntax?

One or more path legs, as well as the path’s scope, make up a path. As indicated by the $ character at the beginning of the path, the scope of paths used in MySQL JSON functions is always the document being searched or otherwise operated on. The path’s legs are separated by period characters (.). The symbol for cells in arrays is [N], with N being a non-negative integer. Double-quoted strings or ECMAScript identifiers are required for key names Path expressions, like JSON text, should be encoded using the ASCII, utf8, or utf8mb4 character sets. From other character encodings, utf8mb4 is implicitly converted.

pathExpression:

member | arrayLocation | doubleAsterisk

arrayLocation:

leftBracket ( nonNegativeInteger | asterisk ) rightBracket

ESIdentifier | doubleQuotedString

doubleAsterisk:

‘**’

‘.’

‘*’

leftBracket:

‘[‘

rightBracket:

‘]’

The scope of the path in MySQL, as previously stated, is always the document being worked on, denoted by $. The document can be substituted for ‘$’ in JSON path expressions.

• * represents the total value of the object’s members.
• [*] represents the value of every cell in the array.

The and * wildcard tokens are used as follows:

[prefix]**suffix represents all paths that start with a prefix and end with a suffix. The prefix is optional, but the suffix is required; for example, a path cannot end in **.

In addition, the sequence *** might not show up in a path.

What are Operations on MySQL?JSON?

• Normalization
• Merging and AutoWrapping
• Sorting and Type Conversion
• Aggregation

There are 4 major operations in MySQL JSON:

Normalization

Normalization is required when parsing a string in a JSON document. To avoid ambiguity and redundancy, this normalization is performed. When you insert a JSON document, the MySQL engine performs automatic normalization.

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

After normalizing the data you get,

+?—?—?—?—?—?—?—?—?—?+

| JSON_OBJECT(‘key1’, 1, ‘key2’, ‘abc’, ‘key1’, ‘def’) |

+?—?—?—?—?—?—?—?—?—?+

| {“key1”: “def”, “key2”: “abc”} |

+?—?—?—?—?—?—?—?—?—?+

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

Merging And AutoWrapping

Merging

You can combine multiple arrays to create a single common array that contains all of the data. The JSON MERGE PRESERVE() function concatenates arrays so that later-named arrays are appended to the end of the first. In addition, JSON MERGE PATCH() treats each argument as a single-element array and uses “last duplicate key wins” normalization to select only the last argument.

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]

The merged array is reduced 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

Auto Wrapping involves combining values or “last duplicate key wins” (depending on whether JSON MERGE PRESERVE() or JSON MERGE PATCH() is used to merge arrays and objects).

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”

Sorting and Type Conversion

Sorting

• Use SQL NULL before all JSON values if you want to sort in ascending order, and SQL NULL after all JSON values if you want to sort in descending order.
• The max sort length variable limits the length of JSON values’ Sort keys. This means that only the keys that differ after the first max sort length bytes will be compared as equal.
• The sorting of nonscalar values isn’t supported by MySQL.

JSON values are sorted using the ORDER BY and GROUP BY commands, which follow the rules below:

Type Conversion

The following table shows how the conversion from JSON to Non-JSON and vice versa works:

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

Aggregation

MySQL ignores NULL values and converts Non-NULL values to a numeric type to aggregate JSON values. Although the MIN(), MAX(), and GROUP CONCAT() functions do not require this numeric conversion, the other aggregation functions will always convert the values to numeric first.

Non-NULL values should always yield a meaningful result when converted to various JSON types. MySQL frequently truncates values to keep this from happening, resulting in the precision loss. There is no guarantee that you will get a meaningful result if the conversion is not of the JSON type.

Conclusion

This article explains MYSQL JSON operations in detail. In addition to that, it also talks about various aspects of MySQL JSON such as Creating JSON Values, JSON Path Syntax, and Searching & Modifying JSON Values.

Hevo Data provides a No-code Data Pipeline to automate your data transfer process. This allows you to work on other key aspects of your business like Analytics, Customer Management, etc. This platform supports 100+ sources and transfers their data to Cloud-based Data Warehouses such as Snowflake, Google BigQuery, Amazon Redshift, Firebolt, etc. It will ensure a hassle-free experience for you.

Originally published at https://www.namasteui.com on April 14, 2022.