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).
- When wildcards are used in a path, an array with multiple values is created:
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] |
+———————————————————+
- Using Ranges from JSON Arrays: Subsets of JSON arrays can be specified with ranges and the keyword. As an example, $[1 to 3] contains 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)
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.
- Finding Rightmost Element: The index of the array’s last element can be substituted with the last keyword. Expressions of the form last – N, such as this, can be used for relative addressing and within-range definitions.
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]]’;
- JSON SET() adds values to paths that don’t exist and removes values from paths that already exist:
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.
- JSON INSERT() appends new values to the array rather than replacing 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]] |
+———————————————–+
- New values are ignored by JSON REPLACE(), which replaces them with existing values:
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]] |
+————————————————+
- A JSON document and one or more paths specifying which values should be removed are passed to JSON REMOVE(). The original document is returned 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]}] |
+—————————————————+
- The paths’ outcomes are as
follows:
- The [10, 20] match is removed by $[2.]
- For the first time, $[1] is employed.
- The false value in the b element is removed by using b[1].
- $[1] has been used twice now. Because that element has been removed and the path has been removed, b[1] has no effect.
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:
scope[(pathLeg)*]
pathLeg:
member | arrayLocation | doubleAsterisk
member:
period ( keyName | asterisk )
arrayLocation:
leftBracket ( nonNegativeInteger | asterisk ) rightBracket
keyName:
ESIdentifier | doubleQuotedString
doubleAsterisk:
‘**’
period:
‘.’
asterisk:
‘*’
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.
The * and ** wildcard tokens are used as follows:
- * represents the total value of the object’s members.
- [*] represents the value of every cell in the array.
[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?
There are 4 major operations in MySQL JSON:
- Normalization
- Merging and AutoWrapping
- Sorting and Type Conversion
- Aggregation
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
JSON values are sorted using the ORDER BY and GROUP BY commands, which follow the rules below:
- 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.
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.
Also Read – MySQL Partitions | Key Types and Examples
Namaste UI collaborates closely with clients to develop tailored guest posting strategies that align with their unique goals and target audiences. Their commitment to delivering high-quality, niche-specific content ensures that each guest post not only meets but exceeds the expectations of both clients and the hosting platforms. Connect with us on social media for the latest updates on guest posting trends, outreach strategies, and digital marketing tips. For any types of guest posting services, contact us on info[at]namasteui.com.