gogoWebsite

JSON in MySQL

Updated to 16 days ago

MySQL has started to support it since 5.7.8JSONType, used to store JSON data.

JSONThe addition of types blurs the boundaries between relational databases and NoSQL, and also brings great convenience to daily development.

This article mainly introduces the use of JSON type in MySQL. Please refer to the MySQL manual: /doc/refman/8.0/en/

1. Why use JSON

Since MySQL added support for JSON, some table structure changes have become easier.

1.1 JSON usage scenarios

Although relational databases have always been very effective, document databases are more flexible and convenient in the face of changing needs.

After MySQL supports JSON, the boundary between relational and document-based databases is blurred.

During the development process, the following situations are often encountered:

  • Only a small part of the data in the table requires newly added fields;
  • When this newly added field is likely to be abandoned after being temporarily used;
  • When you don't know what new field to add later, but you are likely to add it.

At these times, it is more appropriate to use a JSON for storage, without changing the table structure, which is very convenient.

1.2 String or JSON type

Before the MySQL version 5.7 that does not support JSON, there was no choice but to use one string type to store JSON data.

But if the database supports itJSONType, then useJSONType.

JSONTypes have the following benefits compared to using strings to store JSON data:

  • It can automatically verify the stored JSON data, and an error will be reported when inserting illegal JSON data;
  • Optimized storage structure.JSONType converts data into internal structures for storage, so thatJSONSearch and local changes are made to type data; for strings, they need to be retrieved and updated.

2. Add, delete, modify and check JSON

Here will briefly introduce itJSONThe use of types mainly involves operations such as adding, deleting, modifying and checking.

Used in MySQLutf8mb4Character sets andutf8mb4_binCharacter order to process strings in JSON, so strings in JSON are case sensitive.

2.1 Create a JSON column

Create aJSONA column of type is simple:

CREATE TABLE videos (
	id int NOT NULL AUTO_INCREMENT,
	ext json NOT NULL,
	PRIMARY KEY (id)
);

We built a tablevideos, there is one insideJSONType ofextFields, used to store some extended data.

2.2 Insert JSON value

Like other types, useINSERTTo insert data:

INSERT INTO videos
VALUES (1, '{"vid": "vid1", "title": "title1", "tags": ["news", "china"], "logo": true}'),
	(2, '{"vid": "vid2", "tags":[], "title": "title2", "logo": false}'),
	(3, '{"vid": "vid3", "title": "title3"}');

Let’s take a look at the data in the table now:

mysql> select * from videos;
+----+-----------------------------------------------------------------------------+
| id | ext                                                                         |
+----+-----------------------------------------------------------------------------+
|  1 | {"vid": "vid1", "logo": true, "tags": ["news", "china"], "title": "title1"} |
|  2 | {"vid": "vid2", "logo": false, "tags": [], "title": "title2"}               |
|  3 | {"vid": "vid3", "title": "title3"}                                          |
+----+-----------------------------------------------------------------------------+

EachextAll are JSON data.

2.3 Verify JSON

useJSONOne advantage of type is that MySQL can automatically check the validity of data and avoid inserting illegal JSON data.

2.3.1 JSON legality verification

First, you need to verify whether a value is a legal JSON, otherwise the insertion will fail:

mysql> insert into videos values (1, '{');
ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 1 in value for column ''.

Can also be usedJSON_VALID()Functions to check whether a JSON value is legal:

mysql> select json_valid('{');
+-----------------+
| json_valid('{') |
+-----------------+
|               0 |
+-----------------+

mysql> select json_valid('{"vid": "vid1"}');
+-------------------------------+
| json_valid('{"vid": "vid1"}') |
+-------------------------------+
|                             1 |
+-------------------------------+

2.3.2 JSON mode verification

If you go further, in addition to whether the value is legal JSON, you also need to check the mode, such as the JSON value that needs to contain certain fields, etc.

At this time, you can define a schema and then useJSON_SCHEMA_VALID()orJSON_SCHEMA_VALIDATION_REPORT()Function to verify.

JSON_SCHEMA_VALID()andJSON_SCHEMA_VALIDATION_REPORT()The two functions were introduced in version 8.0.17, but not yet available in version 5.7.

Define a pattern:

{
	"id": "schema_for_videos",
	"$schema": "/draft-04/schema#",
	"description": "Schema for the table videos",
	"type": "object",
	"properties": {
		"vid": {
			"type": "string"
		},
		"tags": {
			"type": "array"
		},
		"logo": {
			"type": "boolean"
		},
		"title": {
			"type": "string"
		}
	},
	"required": ["title", "tags"]
}

Field meaning:

  • id: The unique ID of the pattern;
  • $schema: The standard for JSON mode verification should be that this value remains unchanged;
  • description: Description of the pattern;
  • type: The type of the root element. The root element of JSON in MySQL can also be an array;
  • properties: A list of JSON elements, each element should be described, and the corresponding type is listed;
  • required: Required elements.

Define a variable in MySQL:

mysql> set @schema = '{"id":"schema_for_videos","$schema":"/draft-04/schema#","description":"Schema for the table videos","type":"object","properties":{"title":{"type":"string"},"tags":{"type":"array"}},"required":["title","tags"]}';
Query OK, 0 rows affected (0.04 sec)

This will be usedJSON_SCHEMA_VALID()orJSON_SCHEMA_VALIDATION_REPORT()Verify whether a JSON meets the requirements:

mysql> select json_schema_valid(@schema, '{"title": "", "vid": "", "logo": false, "tags": []}') as 'valid?';
+--------+
| valid? |
+--------+
|      1 |
+--------+

mysql> select json_schema_validation_report(@schema, '{"title": "", "vid": "", "logo": false, "tags": []}') as 'valid?';
+-----------------+
| valid?          |
+-----------------+
| {"valid": true} |
+-----------------+

JSON_SCHEMA_VALID()andJSON_SCHEMA_VALIDATION_REPORT()The difference is that the latter can give some things that do not meet the requirements:

mysql> select json_schema_valid(@schema, '{"vid": "", "logo": false, "tags": []}') as 'valid?';
+--------+
| valid? |
+--------+
|      0 |
+--------+

mysql> select json_schema_validation_report(@schema, '{"vid": "", "logo": false, "tags": []}') as 'valid?'\G
*************************** 1. row ***************************
valid?: {"valid": false, "reason": "The JSON document location '#' failed requirement 'required' at JSON Schema location '#'", "schema-location": "#", "document-location": "#", "schema-failed-keyword": "required"}

Of course, if the second parameter of these two functions is a legal JSON, MySQL will report an error:

mysql> select json_schema_valid(@schema, '{') as 'valid?';ERROR 3141 (22032): Invalid JSON text in argument 2 to function json_schema_valid: "Missing a name for object member." at position 1.

We can also add this pattern to the table definition, so that inserting data can be used to verify using this pattern:

ALTER TABLE videos
ADD CONSTRAINT CHECK (JSON_SCHEMA_VALID('{"id":"schema_for_videos","$schema":"/draft-04/schema#","description":"Schema for the table videos","type":"object","properties":{"vid":{"type":"string"},"tags":{"type":"array"},"logo":{"type":"bool"},"title":{"type":"string"}},"required":["title","tags"]}', ext));

Of course, if there is already data in the table and does not meet this verification mode, MySQL will report an error:

ERROR 3819 (HY000): Check constraint 'videos_chk_1' is violated.

The original data should be modified to meet the requirements before adding verification.

After adding, the newly added data will be verified:

mysql> INSERT INTO videos VALUES (1, '{"vid": "vid1", "title": "title1", "tags": ["news", "china"], "logo": true}');Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO videos VALUES (2, '{"vid": "vid2", "title": "title2"}');ERROR 3819 (HY000): Check constraint 'videos_chk_1' is violated.

2.4 JSON formatting

useJSON_PRETTY()Functions beautify output:

mysql> select json_pretty(ext) from videos\G
*************************** 1. row ***************************
json_pretty(ext): {
  "vid": "vid1",
  "logo": true,
  "tags": [
    "news",
    "china"
  ],
  "title": "title1"
}

2.5 Getting JSON elements

JSONThe point where fields are better than JSON strings isJSONFields can directly get internal elements without having to get the entire document.

Supported in MySQLJSON_EXTRACT()Functions and->，->>Operator to get elements inside JSON:

mysql> select json_extract('{"a": 9, "b":[1,2,3]}', '$.a') as a;
+------+
| a    |
+------+
| 9    |
+------+
1 row in set (0.04 sec)

mysql> select json_extract('{"a": 9, "b":[1,2,3]}', '$.b') as b;
+-----------+
| b         |
+-----------+
| [1, 2, 3] |
+-----------+
1 row in set (0.04 sec)

mysql> select json_extract('{"a": 9, "b":[1,2,3]}', '$.b[1]') as 'b[1]';
+------+
| b[1] |
+------+
| 2    |
+------+
1 row in set (0.04 sec)

use->：

mysql> select * from videos;
+----+-----------------------------------------------------------------------------+
| id | ext                                                                         |
+----+-----------------------------------------------------------------------------+
|  1 | {"vid": "vid1", "logo": true, "tags": ["news", "china"], "title": "title1"} |
|  2 | {"vid": "vid2", "logo": false, "tags": [], "title": "title2"}               |
|  3 | {"vid": "vid3", "logo": false, "tags": ["food"], "title": "title3"}         |
+----+-----------------------------------------------------------------------------+
3 rows in set (0.04 sec)

mysql> select ext->'$.title' from videos;
+----------------+
| ext->'$.title' |
+----------------+
| "title1"       |
| "title2"       |
| "title3"       |
+----------------+
3 rows in set (0.04 sec)

->that isJSON_EXTRACT()Alias ​​for the function.

useJSON_UNQUOTE()Remove quotes for function:

mysql> select json_unquote(ext->'$.title') from videos;
+------------------------------+
| json_unquote(ext->'$.title') |
+------------------------------+
| title1                       |
| title2                       |
| title3                       |
+------------------------------+
3 rows in set (0.04 sec)

Can also be used->>Achieve the same effect (->>that isJSON_UNQUOTE(JSON_EXTRACT(...))alias):

mysql> select ext->>'$.title' from videos;
+-----------------+
| ext->>'$.title' |
+-----------------+
| title1          |
| title2          |
| title3          |
+-----------------+
3 rows in set (0.04 sec)

2.6 JSONPath

In the process of getting JSON elements, we used something like$.title，$.b[1]Such a structure to specify elements, these are JSONPath.

JSONPath usage$Symbols represent the entire JSON document, and different symbols can be followed by different elements:

  • A dot number (.) Add key to get the value of the specified key;
  • [N]Get the subscript in the array asNelement (start 0);
  • [N to M]Array elements can also specify the beginning and end (both included);
  • [last]last represents the last element in the array;
  • [*]Gets all elements in the array;
  • prefix**suffixGet allprefixbeginningsuffixThe ending JSONPath.

The following JSON is an example:

{
	"a": "a_value",
	"b": [1, 2, 3, 4, 5],
	"c": true,
	"d": {
		"a": "inner_a",
		"b": [11, 22, "inner_b"]
	}
}
  • '$'Get the entire document;
  • '$.a'that is"a_value"；
  • '$.b'that is[1, 2, 3, 4, 5]；
  • '$.b[*]'Equivalent to'$.b'；
  • '$.b[2]'Get an arraybThe third element in3；
  • '$.'What you get is"inner_a"；
  • '$.[2]'What you get is"inner_b"；
  • '$.b[1 to 2]'return[2, 3]；
  • '$.b[last]'return5；
  • '$.b[last-2 to last-1]'return[3, 4]；
  • '$**.a'Returns allaArray of ending elements["a_value", "inner_a"]；
  • '$**.b'It's an array of arrays[[1, 2, 3, 4, 5], [11, 22, "inner_b"]]。

JSONPath can not only be used to obtain elements in JSON. Functions involving JSON value addition, deletion, modification and query basically require a JSONPath as a parameter to specify the element to be operated.

2.7 Search for JSON elements

JSONAnother advantage of the type is that it can be searched.

Search can be usedJSON_SEARCH()function, return the matching JSONPath.

The JSON_SEARCH() function prototype is as follows:

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

The first three are required parameters:

  • json_doc: A valid JSON document;
  • one_or_all: string, must be'one'or'all', used to specify the number of matches returned, if so'one'If it is returned only the first match, otherwise it will be returned;
  • search_str: It is the value that needs to be searched. Currently, only string search is supported. You can also add it%or_to fuzzy match;

The last two are optional parameters:

  • escape_char: Escape characters, default is\; if not specified orNULLIf so\; Otherwise, this parameter can only be empty (at this time\) or one character (specifying multiple characters will cause an error);
  • path: Specifies the location to start the search, if not, it is the entire document.

Next, let’s take a look at how to search the following JSON document as an example:

{
	"a": "a_value",
	"b": ["1", "2", "3", "4", "5"],
	"c": true,
	"d": {
		"a": "a_value",
		"b": ["1", "2", "bvalue"]
	}
}
  • json_search(@j, 'one', 'a_value')return"$.a"；
  • json_search(@j, 'all', 'a_value')return["$.a", "$."]；
  • json_search(@j, 'all', '1')return["$.b[0]", "$.[0]"]；
  • json_search(@j, 'all', '%_value')return["$.a", "$.", "$.[2]"]；
  • json_search(@j, 'all', '%\_value')return["$.a", "$."], pay attention to the difference from the previous one;
  • json_search(@j, 'all', '%|_value', '|')Specify escape character, return["$.a", "$."]；
  • json_search(@j, 'all', '%|_value', '|', '$.a')Specifies the location to start the search, return"$.a", no match$.；

Next, we canWHEREUsed inJSON_SEARCH()Now.

Still the previous onevideossurface:

mysql> select * from videos;
+----+-----------------------------------------------------------------------------+
| id | ext                                                                         |
+----+-----------------------------------------------------------------------------+
|  1 | {"vid": "vid1", "logo": true, "tags": ["news", "china"], "title": "title1"} |
|  2 | {"vid": "vid2", "logo": false, "tags": [], "title": "title2"}               |
|  3 | {"vid": "vid3", "logo": false, "tags": ["food"], "title": "title3"}         |
+----+-----------------------------------------------------------------------------+
3 rows in set (0.04 sec)

mysql> select * from videos where json_search(ext, 'all', 'title2');
+----+---------------------------------------------------------------+
| id | ext                                                           |
+----+---------------------------------------------------------------+
|  2 | {"vid": "vid2", "logo": false, "tags": [], "title": "title2"} |
+----+---------------------------------------------------------------+
1 row in set, 1 warning (0.04 sec)

mysql> select * from videos where json_search(ext, 'all', 'food', '', '$.tags');
+----+---------------------------------------------------------------------+
| id | ext                                                                 |
+----+---------------------------------------------------------------------+
|  3 | {"vid": "vid3", "logo": false, "tags": ["food"], "title": "title3"} |
+----+---------------------------------------------------------------------+
1 row in set, 1 warning (0.04 sec)

Can also be used->Operators to search:

mysql> select ext from videos where ext->'$.logo' = true;
+------------------------------------------------------------------------------------------------+
| ext                                                                                            |
+------------------------------------------------------------------------------------------------+
| {"vid": "vid1", "logo": true, "tags": ["news", "china"], "title": "title1", "protected": true} |
+------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

2.8 Inserting new elements in JSON

There are several functions in MySQL that can support adding new elements to JSON:

  • JSON_INSERT()
  • JSON_ARRAY_APPEND()
  • JSON_ARRAY_INSERT()

These functions support in-place updates, rather than taking out the JSON document and changing it fully overwrites.

useJSON_INSERT()New elements to function:

update videos set ext = json_insert(ext, '$.protected', true);

If the element to be added already exists, there is no change.

JSON_ARRAY_APPEND()Functions can append elements to an array:

update videos set ext = json_array_append(ext, '$.tags', 'tech') where json_search(ext, 'all', 'title2', '', '$.title');

Used here at the same timeJSON_SEARCH()Make a match.

JSON_ARRAY_INSERT()Functions can add elements to the specified location of an array:

update videos set ext=json_array_insert(ext, '$.tags[1]', 'beijing') where ext->'$.vid' = 'vid1';

result:

mysql> select ext from videos where ext->'$.vid' = 'vid1';
+-----------------------------------------------------------------------------------------------------------+
| ext                                                                                                       |
+-----------------------------------------------------------------------------------------------------------+
| {"vid": "vid1", "logo": true, "tags": ["news", "beijing", "china"], "title": "title1", "protected": true} |
+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

2.9 Update JSON elements

Use the JSON_REPLACE() or JSON_SET() functions to update elements in JSON.

The JSON_REPLACE() function can be used to update the value of an element:

update videos set ext = json_replace(ext, '$.protected', false) where ext->'$.vid' = 'vid1';

But if there is no key to be updated in JSON, then do nothing.

In addition to updating the element's value, JSON_SET() can also be added if the specified element does not exist:

update videos set ext = json_set(ext, '$.size', 100) where ext->'$.vid' = 'vid1';

2.10 Delete JSON elements

useJSON_REMOVE()Functions can delete elements in JSON:

update videos set ext = json_remove(ext, '$.size') where ext->'$.vid' = 'vid1';

update videos set ext = json_remove(ext, '$.tags[1]') where ext->'$.vid' = 'vid1';

JSON_REMOVE()Functions can specify multiple JSONPaths to delete multiple elements. At this time, MySQL is deleted one by one from left to right.

In this way, even if the same JSONPath is the same but the order is different, the result will be different:

mysql> select json_remove('{"a": [1,2,3,4,5]}', '$.a[2]', '$.a[3]');
+-------------------------------------------------------+
| json_remove('{"a": [1,2,3,4,5]}', '$.a[2]', '$.a[3]') |
+-------------------------------------------------------+
| {"a": [1, 2, 4]}                                      |
+-------------------------------------------------------+
1 row in set (0.04 sec)

mysql> select json_remove('{"a": [1,2,3,4,5]}', '$.a[3]', '$.a[2]');
+-------------------------------------------------------+
| json_remove('{"a": [1,2,3,4,5]}', '$.a[3]', '$.a[2]') |
+-------------------------------------------------------+
| {"a": [1, 2, 5]}                                      |
+-------------------------------------------------------+
1 row in set (0.04 sec)

2.11 JSON merge

MySQL supports merging two JSON documents into one document. It can be done through the following two functions:

  • JSON_MERGE_PATCH(): It is equivalent to updating the first parameter with the second parameter;
  • JSON_MERGE_PRESERVE(): Keep elements with two parameters as much as possible.

These two functions are very different, so you must pay attention to it when using it.

2.11.1 JSON_MERGE_PATCH

The function receives at least two parameters. If more than two parameters, the result of the merged first two is merged with the latter.

Let's assume that there are two parameters for discussion, and more than two are similar.

If there is a parameterNULL, then the result isNULL：

mysql> select json_merge_patch('{"a": 1, "b": [1,2]}', null);
+------------------------------------------------+
| json_merge_patch('{"a": 1, "b": [1,2]}', null) |
+------------------------------------------------+
| NULL                                           |
+------------------------------------------------+
1 row in set (0.04 sec)

mysql> select json_merge_patch(null, '{"b": null}');
+---------------------------------------+
| json_merge_patch(null, '{"b": null}') |
+---------------------------------------+
| NULL                                  |
+---------------------------------------+
1 row in set (0.04 sec)

If the first parameter is not an object, the result is equivalent to a combination of an empty object and the second parameter, which is actually the second parameter:

mysql> select json_merge_patch('{}', '{"a": "a"}');
+--------------------------------------+
| json_merge_patch('{}', '{"a": "a"}') |
+--------------------------------------+
| {"a": "a"}                           |
+--------------------------------------+
1 row in set (0.04 sec)

mysql> select json_merge_patch('{}', '"a"');
+-------------------------------+
| json_merge_patch('{}', '"a"') |
+-------------------------------+
| "a"                           |
+-------------------------------+
1 row in set (0.04 sec)

mysql> select json_merge_patch('[]', '"a"');
+-------------------------------+
| json_merge_patch('[]', '"a"') |
+-------------------------------+
| "a"                           |
+-------------------------------+
1 row in set (0.04 sec)

mysql> select json_merge_patch('[1, 2, 3]', '{"a": "a"}');
+---------------------------------------------+
| json_merge_patch('[1, 2, 3]', '{"a": "a"}') |
+---------------------------------------------+
| {"a": "a"}                                  |
+---------------------------------------------+
1 row in set (0.04 sec)

If the second parameter is an array, then the result is still the second parameter:

mysql> select json_merge_patch('{"a": "a"}', '[]');
+--------------------------------------+
| json_merge_patch('{"a": "a"}', '[]') |
+--------------------------------------+
| []                                   |
+--------------------------------------+
1 row in set (0.04 sec)

mysql> select json_merge_patch('{"a": "a"}', '[1]');
+---------------------------------------+
| json_merge_patch('{"a": "a"}', '[1]') |
+---------------------------------------+
| [1]                                   |
+---------------------------------------+
1 row in set (0.04 sec)

Next is the case where both parameters are object.

The merged result contains those elements that have the first parameter but not the second parameter:

mysql> select json_merge_patch('{"a": 1}', '{}');
+------------------------------------+
| json_merge_patch('{"a": 1}', '{}') |
+------------------------------------+
| {"a": 1}                           |
+------------------------------------+
1 row in set (0.04 sec)

It also contains the first parameter without the second parameter (except the value isnullof):

mysql> select json_merge_patch('{"a": 1}', '{"b": 2}');
+------------------------------------------+
| json_merge_patch('{"a": 1}', '{"b": 2}') |
+------------------------------------------+
| {"a": 1, "b": 2}                         |
+------------------------------------------+
1 row in set (0.04 sec)

If there are both parameters, the result of the merge is the result of the recursive merge of the two values:

mysql> select json_merge_patch('{"a": 1}', '{"a": 2}');
+------------------------------------------+
| json_merge_patch('{"a": 1}', '{"a": 2}') |
+------------------------------------------+
| {"a": 2}                                 |
+------------------------------------------+
1 row in set (0.04 sec)

mysql> select json_merge_patch('{"a": [1,2]}', '{"a": 2}');
+----------------------------------------------+
| json_merge_patch('{"a": [1,2]}', '{"a": 2}') |
+----------------------------------------------+
| {"a": 2}                                     |
+----------------------------------------------+
1 row in set (0.04 sec)

mysql> select json_merge_patch('{"a": [1,2]}', '{"a": [3]}');
+------------------------------------------------+
| json_merge_patch('{"a": [1,2]}', '{"a": [3]}') |
+------------------------------------------------+
| {"a": [3]}                                     |
+------------------------------------------------+
1 row in set (0.04 sec)

mysql> select json_merge_patch('{"a": [1,2]}', '{"a": {"c": 1}}');
+-----------------------------------------------------+
| json_merge_patch('{"a": [1,2]}', '{"a": {"c": 1}}') |
+-----------------------------------------------------+
| {"a": {"c": 1}}                                     |
+-----------------------------------------------------+
1 row in set (0.04 sec)

In these three examples, the result is like the value of the second parameter overwrites the first one, because in these examples, the two parameters areaThe corresponding values ​​are not all object, and the result is the second parameteravalue.

The following example shows the result of recursive merge:

mysql> select json_merge_patch('{"a": {"c": [1,2]}}', '{"a": {"d": 9}}');
+------------------------------------------------------------+
| json_merge_patch('{"a": {"c": [1,2]}}', '{"a": {"d": 9}}') |
+------------------------------------------------------------+
| {"a": {"c": [1, 2], "d": 9}}                               |
+------------------------------------------------------------+
1 row in set (0.04 sec)

mysql> select json_merge_patch('{"a": {"c": [1,2]}}', '{"a": {"c": 9}}');
+------------------------------------------------------------+
| json_merge_patch('{"a": {"c": [1,2]}}', '{"a": {"c": 9}}') |
+------------------------------------------------------------+
| {"a": {"c": 9}}                                            |
+------------------------------------------------------------+
1 row in set (0.04 sec)

If the element value of the second parameter isnull, then the result does not contain this element:

mysql> select json_merge_patch('{"a": 1}', '{"b": null}');
+---------------------------------------------+
| json_merge_patch('{"a": 1}', '{"b": null}') |
+---------------------------------------------+
| {"a": 1}                                    |
+---------------------------------------------+
1 row in set (0.04 sec)

Use this feature to delete the element of the first parameter, just like JSON_REMOVE():

mysql> select json_merge_patch('{"a": 1, "b": [1,2]}', '{"b": null}');
+---------------------------------------------------------+
| json_merge_patch('{"a": 1, "b": [1,2]}', '{"b": null}') |
+---------------------------------------------------------+
| {"a": 1}                                                |
+---------------------------------------------------------+
1 row in set (0.04 sec)

2.11.2 JSON_MERGE_PRESERVE

JSON_MERGE_PRESERVE()Functions also merge two or more JSONs, butJSON_MERGE_PATCH()The difference is that the element of the second parameter does not overwrite the element of the first parameter.

First of all, if there is a parameterNULL, then it's savedNULL。

Combine adjacent arrays into an array:

mysql> select json_merge_preserve('[1,2]', '["a", "b"]');
+--------------------------------------------+
| json_merge_preserve('[1,2]', '["a", "b"]') |
+--------------------------------------------+
| [1, 2, "a", "b"]                           |
+--------------------------------------------+
1 row in set (0.04 sec)

Merge two adjacent objects into one object:

mysql> select json_merge_preserve('{"a": [1]}', '{"b": 1}');
+-----------------------------------------------+
| json_merge_preserve('{"a": [1]}', '{"b": 1}') |
+-----------------------------------------------+
| {"a": [1], "b": 1}                            |
+-----------------------------------------------+
1 row in set (0.04 sec)

Scalar values ​​are wrapped into an array and then merged as arrays:

mysql> select json_merge_preserve('{"a": 1}', '{"a": 2}');
+---------------------------------------------+
| json_merge_preserve('{"a": 1}', '{"a": 2}') |
+---------------------------------------------+
| {"a": [1, 2]}                               |
+---------------------------------------------+
1 row in set (0.04 sec)

mysql> select json_merge_preserve('{"a": 1}', '{"a": [2]}');
+-----------------------------------------------+
| json_merge_preserve('{"a": 1}', '{"a": [2]}') |
+-----------------------------------------------+
| {"a": [1, 2]}                                 |
+-----------------------------------------------+
1 row in set (0.04 sec)

mysql> select json_merge_preserve('{"a": 1, "b": 3}', '{"a": 2, "d": 4}');
+-------------------------------------------------------------+
| json_merge_preserve('{"a": 1, "b": 3}', '{"a": 2, "d": 4}') |
+-------------------------------------------------------------+
| {"a": [1, 2], "b": 3, "d": 4}                               |
+-------------------------------------------------------------+
1 row in set (0.04 sec)

Merge adjacent arrays and objects, first wrap the object into an array, and then combine two arrays and:

mysql> select json_merge_preserve('[1, 2]', '{"id": 47}');
+---------------------------------------------+
| json_merge_preserve('[1, 2]', '{"id": 47}') |
+---------------------------------------------+
| [1, 2, {"id": 47}]                          |
+---------------------------------------------+
1 row in set (0.04 sec)

2.12 Others

MySQL also has many useful functions for manipulating JSON types, here is a brief introductionJSON_TYPE, JSON_LENGTH, andJSON_STORAGE_SIZEOther functions can be used to refer to MySQL documentation: /doc/refman/8.0/en/

JSON_TYPEReturns the type of JSON element, includingobject, array, nullAnd other scalar types:

mysql> select json_type('{}'), json_type('[]'), json_type('true'), json_type(null), json_type('"a"');
+-----------------+-----------------+-------------------+-----------------+------------------+
| json_type('{}') | json_type('[]') | json_type('true') | json_type(null) | json_type('"a"') |
+-----------------+-----------------+-------------------+-----------------+------------------+
| OBJECT          | ARRAY           | BOOLEAN           | NULL            | STRING           |
+-----------------+-----------------+-------------------+-----------------+------------------+
1 row in set (0.04 sec)

JSON_LENGTHThe function returns the number of elements:

mysql> select json_length('[1]'), json_length('{"a": [1,2]}', '$.a'), json_length('{"a": [1,2]}', '$.a[1]');
+--------------------+------------------------------------+---------------------------------------+
| json_length('[1]') | json_length('{"a": [1,2]}', '$.a') | json_length('{"a": [1,2]}', '$.a[1]') |
+--------------------+------------------------------------+---------------------------------------+
|                  1 |                                  2 |                                     1 |
+--------------------+------------------------------------+---------------------------------------+
1 row in set (0.03 sec)

JSON_STORAGE_SIZEThe function returns the number of bytes occupied by JSON data:

mysql> select json_storage_size('{"a": true}'), char_length('{"a": true}');
+----------------------------------+----------------------------+
| json_storage_size('{"a": true}') | char_length('{"a": true}') |
+----------------------------------+----------------------------+
|                               13 |                         11 |
+----------------------------------+----------------------------+
1 row in set (0.04 sec)

JSONThe space occupied by the type is roughlyLONGBLOBorLONGTEXTSame. However, due to some metadata, it may be slightly larger.

3. Advanced usage of JSON

We introduced MySQL earlierJSONSome basic operations of types, MySQLJSONType support can also have some more advanced gameplay, such as the mutual conversion of relational data and JSON data, and even use MySQL as a document database.

3.1 Relational data to JSON

There are some functions in MySQL that support converting relational data into JSON data:

  • JSON_OBJECT
  • JSON_ARRAY
  • JSON_OBJECTAGG
  • JSON_ARRAYAGG

JSON_OBJECTFunctions can assemble multiple key-value pairs into one object:

mysql> select json_pretty(json_object("a", 1, "b", true, "null", null))\G
*************************** 1. row ***************************
json_pretty(json_object("a", 1, "b", true, "null", null)): {
  "a": 1,
  "b": true,
  "null": null
}
1 row in set (0.04 sec)

If the number of key value pairs is incorrect, an error will be reported:

mysql> select json_pretty(json_object("a", 1, "b", true, "null"))\G
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'json_object'

JSON_ARRAYThe function combines all the parameters into an array:

mysql> select json_array(1,1,"a",null,true,curtime());
+--------------------------------------------+
| json_array(1,1,"a",null,true,curtime())    |
+--------------------------------------------+
| [1, 1, "a", null, true, "17:38:39.000000"] |
+--------------------------------------------+
1 row in set (0.04 sec)

Two functions can be combined to build a complex JSON data:

mysql> select json_pretty(json_object('example', 'a complex example', 'user', json_object('name', 'valineliu', 'tags', json_array(1,2)), 'books', json_array('a','b'))) as r\G
*************************** 1. row ***************************
r: {
  "user": {
    "name": "valineliu",
    "tags": [
      1,
      2
    ]
  },
  "books": [
    "a",
    "b"
  ],
  "example": "a complex example"
}
1 row in set (0.04 sec)

JSON_OBJECTAGGandJSON_ARRAYAGGTwo functions can be passedGROUP BYReturns more advanced data.

JSON_OBJECTAGGYou can specify two fields of a relational table as key-value pairs for building JSON.

For example, a table looks like this:

mysql> select * from r_videos;
+----+------+---------+
| id | size | title   |
+----+------+---------+
|  1 |  100 | video 1 |
|  2 |  200 | video 2 |
|  3 |  300 | video 3 |
+----+------+---------+
3 rows in set (0.03 sec)

Specifytitlefor key,sizeBuild a JSON for value:

mysql> select json_pretty(json_objectagg(title, size)) as size from r_videos\G
*************************** 1. row ***************************
size: {
  "video 1": 100,
  "video 2": 200,
  "video 3": 300
}
1 row in set (0.04 sec)

JSON_ARRAYAGGFunctions can convert a one-to-many relationship into a JSON array. For example, the following table:

mysql> select * from r_videos;
+----+---------+---------+------+
| id | user_id | title   | size |
+----+---------+---------+------+
|  1 |     100 | title 1 | 1000 |
|  2 |     100 | title 2 | 2000 |
|  3 |     200 | title 3 | 3000 |
|  4 |     300 | title 4 | 4000 |
|  5 |     300 | title 5 | 5000 |
|  6 |     300 | title 6 | 6000 |
+----+---------+---------+------+
6 rows in set (0.03 sec)

The following statement can convert this relational table into auser_idfor key,titleandsizeThe object array composed is value JSON:

mysql> select json_pretty(json_object('user_id', user_id, 'videos', json_arrayagg(json_object('title', title, 'size', size)))) as videos from r_videos group by user_id\G
*************************** 1. row ***************************
videos: {
  "videos": [
    {
      "size": 1000,
      "title": "title 1"
    },
    {
      "size": 2000,
      "title": "title 2"
    }
  ],
  "user_id": 100
}
*************************** 2. row ***************************
videos: {
  "videos": [
    {
      "size": 3000,
      "title": "title 3"
    }
  ],
  "user_id": 200
}
*************************** 3. row ***************************
videos: {
  "videos": [
    {
      "size": 4000,
      "title": "title 4"
    },
    {
      "size": 5000,
      "title": "title 5"
    },
    {
      "size": 6000,
      "title": "title 6"
    }
  ],
  "user_id": 300
}
3 rows in set (0.04 sec)

3.2 JSON to table

AvailableJSON_TABLEFunction converts a JSON into relational data.

Let’s take a look at a simple example:

mysql> select * from json_table('{"null": null, "title": "hello json", "size": 1}', '$' columns(title varchar(32) path '$.title' error on error, size int path '$.size')) as jt;
+------------+------+
| title      | size |
+------------+------+
| hello json |    1 |
+------------+------+
1 row in set (0.03 sec)

JSON_TABLEThe function has two parameters. The first parameter is a JSON document and the second parameter is the column definition.

The JSONPath in front of the column definition specifies the location where the parsing begins. Each column in the column definition specifies the column name, type and JSONPath to obtain the value. Multiple column definitions use,segmentation.

The following example expands a JSON containing an array into a one-to-many relational data.

The original data is as follows:

mysql> select id, json_pretty(ext) as ext from videos\G
*************************** 1. row ***************************
 id: 1
ext: {
  "vid": "vid1",
  "logo": true,
  "tags": [
    "news",
    "china"
  ],
  "title": "title1",
  "protected": false
}
*************************** 2. row ***************************
 id: 2
ext: {
  "vid": "vid2",
  "logo": false,
  "tags": [
    "tech"
  ],
  "title": "title2",
  "protected": true
}
*************************** 3. row ***************************
 id: 3
ext: {
  "vid": "vid3",
  "logo": false,
  "tags": [
    "food",
    "asian",
    "china"
  ],
  "title": "title3",
  "protected": true
}
3 rows in set (0.03 sec)

Each row of data has an array typetags. Now I want to expand this one-to-many data into multiple rows of data:

mysql> select , jt.* from videos v, json_table(, '$' columns (title varchar(32) path '$.title', nested path '$.tags[*]' columns (tag varchar(32) path '$'))) as jt;
+----+--------+-------+
| id | title  | tag   |
+----+--------+-------+
|  1 | title1 | news  |
|  1 | title1 | china |
|  2 | title2 | tech  |
|  3 | title3 | food  |
|  3 | title3 | asian |
|  3 | title3 | china |
+----+--------+-------+
6 rows in set (0.04 sec)

Here fortagThe field definition is usednested path。

3.3 Using MySQL as a document-based database through JSON

MySQL Shell can even treat MySQL as a document-based database.

You can refer to /doc/mysql-shell/8.0/en/ for more information about MySQL Shell.

In essence, tables are used to store data, such as the following table:

CREATE TABLE `MyCollection` ( 
  `doc` json DEFAULT NULL, 
  `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote( json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL, 
  `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL, 
  PRIMARY KEY (`_id`), 
  CONSTRAINT `$val_strict_2190F99D7C6BE98E2C1EFE4E110B46A3D43C9751` 
  CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */ 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

passmysqlshAfter connecting to the database, you can use X DevAPI to operate the above table like MongoDB:

MyCollection = session.getDefaultSchema().getCollection('MyCollection')

X DevAPICollectionThe class is definedadd, find, modify, removeOther functions support addition, deletion, modification and search.

Add data:

MyCollection.add({"document": "one"}).add([{"document": "two"}, {"document": "three"}]).add(mysqlx.expr('{"document": "four"}'))

Like MongoDBfind：

MyCollection.find()
{
	"_id": "000060d5ab750000000000000012",
	"document": "one"
}
{
	"_id": "000060d5ab750000000000000013",
	"document": "two"
}
{
	"_id": "000060d5ab750000000000000014",
	"document": "three"
}
{
	"_id": "000060d5ab750000000000000015",
	"document": "four"
}
4 documents in set (0.0007 sec)

Here I just briefly introduce the interesting gameplay. For more information about X DevAPI, please refer to /doc/x-devapi-userguide/en/

4. Orm's support for JSON

All above are frommysqlUse client from the perspectiveJSONTypes, more or more use various orm in our program.

Here is a brief introduction to orm for MySQLJSONType support, for personal reasons, here are only two orm of Go language: xorm and gorm.

But it seems to be trueJSONThe support is not very rich.

The remaining orm and other languages ​​refer to their respective documents.

4.1 xorm

I haven't found xorm support yetJSONType, I may have missed it. If any big guy knows it, thank you for adding it~

4.2 gorm

Gorm supports JSON types with additional package datatypes:

import "/datatypes"

type User struct {
  gorm.Model
  Name       string
  Attributes datatypes.JSON
}

db.Create(&User{
  Name:       "jinzhu",
  Attributes: datatypes.JSON([]byte(`{"name": "jinzhu", "age": 18, "tags": ["tag1", "tag2"], "orgs": {"orga": "orga"}}`)),
}

// Query user having a role field in attributes
db.First(&user, datatypes.JSONQuery("attributes").HasKey("role"))
// Query user having orgs->orga field in attributes
db.First(&user, datatypes.JSONQuery("attributes").HasKey("orgs", "orga"))

But datatypes are currentlyJSONThe support for types is still very weak, and only supports search and search, and there are still no more useful updates:

import "/datatypes"

type UserWithJSON struct {
	gorm.Model
	Name       string
	Attributes datatypes.JSON
}

DB.Create(&User{
	Name:       "json-1",
	Attributes: datatypes.JSON([]byte(`{"name": "jinzhu", "age": 18, "tags": ["tag1", "tag2"], "orgs": {"orga": "orga"}}`)),
}

// Check JSON has keys
datatypes.JSONQuery("attributes").HasKey(value, keys...)

db.Find(&user, datatypes.JSONQuery("attributes").HasKey("role"))
db.Find(&user, datatypes.JSONQuery("attributes").HasKey("orgs", "orga"))
// MySQL
// SELECT * FROM `users` WHERE JSON_EXTRACT(`attributes`, '$.role') IS NOT NULL
// SELECT * FROM `users` WHERE JSON_EXTRACT(`attributes`, '$.') IS NOT NULL

// PostgreSQL
// SELECT * FROM "user" WHERE "attributes"::jsonb ? 'role'
// SELECT * FROM "user" WHERE "attributes"::jsonb -> 'orgs' ? 'orga'


// Check JSON extract value from keys equal to value
datatypes.JSONQuery("attributes").Equals(value, keys...)

DB.First(&user, datatypes.JSONQuery("attributes").Equals("jinzhu", "name"))
DB.First(&user, datatypes.JSONQuery("attributes").Equals("orgb", "orgs", "orgb"))
// MySQL
// SELECT * FROM `user` WHERE JSON_EXTRACT(`attributes`, '$.name') = "jinzhu"
// SELECT * FROM `user` WHERE JSON_EXTRACT(`attributes`, '$.') = "orgb"

// PostgreSQL
// SELECT * FROM "user" WHERE json_extract_path_text("attributes"::json,'name') = 'jinzhu'
// SELECT * FROM "user" WHERE json_extract_path_text("attributes"::json,'orgs','orgb') = 'orgb'

Reference /docs/v2_release_note.html#DataTypes-JSON-as-example, /go-gorm/datatypes

5. Some thoughts

At present, orm isJSONThe support is not very rich, and most of the above articles aremysqlOperations in the clientJSONField method, operate through orm in our programJSONThe fields are not very convenient yet.

In useJSONWhen it comes to type, I treat the elements inside as a candidate field.

For example, when I came today, I needed to add a field, and I will add this field toJSONIn the type field, you can save data in a record to increase data locality without getting this data elsewhere.

With the evolution of products and the changes in demand, some of the fields added before become useless, so this element can be deleted later; while some fields can be promoted to a relational field because they become more important.

In the end, it's putJSONOr add a field to it, it depends on the specific use. If this field is often used, read, write and search, it is more convenient to add to a new field. However, it seems that adding it as a virtual field is also useful.

When JSON becomes huge, you might consider using a real document-based database, such as MongoDB.