Json.stringify () and json.parse () have been added to MySQL 5.7 to support JSON data types. Previously, we could only store JSON.stringify() and json.parse (), and there was no way to query JSON data. All operations must be read from parse, which is very troublesome. With native JSON data type support, we can query and modify JSON data directly, which is much more convenient than before.

For demonstration purposes, I’ll create a user table with the INFO field to store basic information about the user. To define a field as a JSON type of data, you simply follow the field name with JSON.

CREATE TABLE user (
  id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY.name VARCHAR(30) NOT NULL,
  info JSON
);
Copy the code

After the table has been created, we will follow the classic CRUD data manipulation to show how to manipulate JSON data types.

Add data

Adding the data block is a bit easier, but you need to understand that MySQL is essentially storing strings for JSON. However, when the data is defined as JSON, internal indexes are created to facilitate subsequent operations. So you need to use string wrapping when adding JSON data.

mysql> INSERT INTO user (`name`, `info`) VALUES('lilei', '{"sex": "male", "age": 18, "hobby": ["basketball", "football"], "score": [85, 90, 100]}');
Query OK, 1 row affected (0.00 sec)
Copy the code

In addition to composing JSON yourself, you can also create it by calling MySQL’s JSON creation function.

  • JSON_OBJECT: To quickly create a JSON object with an odd number as key and an even number as valueJSON_OBJECT(key,value,key1,value1)
  • JSON_ARRAY: Quickly create a JSON array, using the methodJSON_ARRAY(item0, item1, item2)
mysql> INSERT INTO user (`name`, `info`) VALUES('hanmeimei', JSON_OBJECT(
    ->   'sex', 'female', 
    ->   'age', 18, 
    ->   'hobby', JSON_ARRAY('badminton', 'sing'), 
    ->   'score', JSON_ARRAY(90, 95, 100)
    -> ));
Query OK, 1 row affected (0.00 sec)
Copy the code

However, for JavaScript engineers, whether to use strings to write or use native functions to create JSON is a very troublesome thing, far less useful than JS native objects. So in the think-model module we added support for jSON.stringify () automatically for JSON data types, so just pass in THE JS object data directly.

Since automatic serialization and parsing of data is based on field types, you need to configure jsonFormat: True in the module to enable this functionality in order not to affect running projects.

//adapter.js
const MySQL = require('think-model-mysql');
exports.model = {
  type: 'mysql'.mysql: {
    handle: MySQL,
    ...
    jsonFormat: true}};Copy the code
//user.js
module.exports = class extends think.Controller {
  async indexAction() {
    const userId = await this.model('user').add({
      name: 'lilei'.info: {
        sex: 'male'.age: 16.hobby: ['basketball'.'football'].score: [85.90.100]}});return this.success(userId); }}Copy the code

Let’s take a look at what the final data stored in the database looks like

mysql> SELECT * FROM `user`; +----+-----------+-----------------------------------------------------------------------------------------+ | id | name  | info | +----+-----------+-----------------------------------------------------------------------------------------+ |  1 | lilei | {"age": 18, "sex": "male", "hobby": ["basketball", "football"], "score": [85, 90, 100]} | | 2 | hanmeimei | {"age": 18, "sex": "female", "hobby": ["badminton", "sing"], "score": [90, 95, 100]} | + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 Rows in set (0.00 SEC)Copy the code

Query data

To better support JSON data manipulation, MySQL provides some JSON data manipulation class methods. The methods associated with query operations are as follows:

  • JSON_EXTRACT(): Obtains partial JSON data based on PathJSON_EXTRACT(json_doc, path[, path] ...)
  • ->:JSON_EXTRACT()The equivalent of
  • ->>:JSON_EXTRACT() 和 JSON_UNQUOTE()The equivalent of
  • JSON_CONTAINS(): Queries whether the JSON data contains the specified data in the specified Path. If the specified Path contains the specified data, 1 is returned; otherwise, 0 is returned. Method of useJSON_CONTAINS(json_doc, val[, path])
  • JSON_CONTAINS_PATH(): Queries whether the specified path exists. If the specified path exists, 1 is returned; otherwise, 0 is returned.one_or_allThe value can be “one” or “all”. “one” indicates that only one exists, and “all” indicates that all exist. Method of useJSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
  • JSON_KEYS(): Gets all key values of the JSON data in the specified path. Method of useJSON_KEYS(json_doc[, path]), similar to JavaScriptObject.keys()Methods.
  • JSON_SEARCH(): Queries for Paths containing the specified string and returns them as a JSON Array. The query string can be matched with either ‘%’ or ‘_’ as in LIKE. Method of useJSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...] ), similar to JavaScriptfindIndex()Operation.

Instead of describing each method individually, we present some scenarios to illustrate how it should work.

Returns the age and gender of the user

The purpose of this example is to show you how to retrieve a portion of JSON data and return it as a normal table field. This can be done using JSON_EXTRACT or the equivalent -> operation. JSON_UNQUOTE() = JSON_UNQUOTE() = JSON_UNQUOTE();

mysql> SELECT `name`, JSON_EXTRACT(`info`, '$.age') as `age`, `info`->'$.sex' as sex FROM `user`; +-----------+------+----------+ | name | age | sex | +-----------+------+----------+ | lilei | 18 | "male" | | hanmeimei 16 | | | + "female" -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC)Copy the code

This is where we get our first taste of how to write Path. MySQL uses string Path descriptions to help us map to data. Similar to JavaScript objects, we use. To get next-level properties and [] to get array elements.

The difference is that you need to represent yourself with $, which is also easier to understand. * and ** are wildcards. For example,.* represents the values of all members of the current hierarchy, and [*] represents the values of all members of the current array. ** Can be followed by a prefix or suffix, for example, a**b indicates a path that starts with a and ends with b.

The path is written very simply and will appear later. The above query corresponds to the think-model written as

//user.js
module.exports = class extends think.Controller {
  async indexAction() {
    const userModel = this.model('user');
    const field = "name, JSON_EXTRACT(info, '$.age') AS age, info->'$.sex' as sex";
    const users = await userModel.field(field).where('1 = 1').select();
    return this.success(users); }}Copy the code

Return to male users who like basketball

mysql> SELECT `name` FROM `user` WHERE JSON_CONTAINS(`info`, '"male"', '$.sex') AND JSON_SEARCH(`info`, 'one', 'basketball', null, '$.hobby'); + -- -- -- -- -- -- -- + | name | + -- -- -- -- -- -- -- + | lilei | + -- -- -- -- -- -- -- + 1 row in the set, 1 warning (0.00 SEC)Copy the code

This example simply shows you how to search properties and arrays. Note that the JSON_CONTAINS() query string needs to be wrapped with “” because there is no conversion problem, or JSON_QUOTE(‘male’) can also be used.

If you are using MySQL 8, you can also use the new JSON_VALUE() method instead of JSON_CONTAINS(). The advantage of this new method is that it has a type conversion and avoids the double quotes problem. JSON_SEARCH() can also be replaced here with the new MEMBER OF or JSON_OVERLAPS() method if the return path is not required.

mysql> SELECT `name` FROM `user` WHERE JSON_VALUE(`info`, '$.sex') = 'male' AND 'basketball' MEMBER OF(JSON_VALUE(`info`, '$.hobby')); + -- -- -- -- -- -- -- + | name | + -- -- -- -- -- -- -- + | lilei | + -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) mysql > SELECT ` name ` FROM ` user ` WHERE JSON_VALUE(`info`, '$.sex') = 'male' AND JSON_OVERLAPS(JSON_VALUE(`info`, '$.hobby'), JSON_QUOTE('basketball')); + -- -- -- -- -- -- -- + | name | + -- -- -- -- -- -- -- + | lilei | + -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

The above query corresponds to the think-model written as

//user.js
module.exports = class extends think.Controller {
  async indexAction() {
    const userModel = this.model('user');
    const where = {
      _string: [
        "JSON_CONTAINS(info, '\"male\"', '$.sex')"."JSON_SEARCH(info, 'one', 'basketball', null, '$.hobby')"]};const where1 = {
      _string: [
        "JSON_VALUE(`info`, '$.sex') = 'male'"."'basketball' MEMBER OF (JSON_VALUE(`info`, '$.hobby'))"]};const where2 = {
      _string: [
        "JSON_VALUE(`info`, '$.sex') = 'male'"."JSON_OVERLAPS(JSON_VALUE(`info`, '$.hobby'), JSON_QUOTE('basketball'))"]}const users = await userModel.field('name').where(where).select();
    return this.success(users); }}Copy the code

Modify the data

MySQL provides JSON manipulation functions that are related to modification operations as follows:

  • JSON_APPEND/JSON_ARRAY_APPEND: These two names are two names for the same function, which was used in MySQL 5.7JSON_APPENDMySQL 8 is updated toJSON_ARRAY_APPENDAnd the previous name was scrapped. This method adds a value to the array, just as it does literally. Method of useJSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
  • JSON_ARRAY_INSERT: adds a value to the arrayJSON_ARRAY_APPEND()It can interpolate at a given position. Method of useJSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
  • JSON_INSERT/JSON_REPLACE/JSON_SETThe above three methods are used to insert data into JSONJSON_[INSERT|REPLACE|SET](json_doc, path, val[, path, val] ...)However, there are some differences in the principle of insertion.
    • JSON_INSERT: Is inserted only when the path does not exist
    • JSON_REPLACE: Is replaced only when the path exists
    • JSON_SET: Regardless of whether the path exists
  • JSON_REMOVE: Removes data from a specified path. Method of useJSON_REMOVE(json_doc, path[, path] ...)

Since the JSON_INSERT, JSON_REPLACE, JSON_SET, and JSON_REMOVE methods support properties and arrays, the first two JSON_ARRAY methods use slightly less. Let’s continue with a few examples based on previous data.

Change the age of the user

mysql> UPDATE `user` SET `info` = JSON_REPLACE(`info`, '$.age', 20) WHERE `name` = 'lilei'; Query OK, 1 row affected (0.00 SEC) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT JSON_VALUE(`info`, '$.age') as age FROM `user` WHERE `name` = 'lilei'; + -- -- -- -- -- -- + | age | + -- -- -- -- -- -- + | | + -- -- -- -- -- - 20 + 1 row in the set (0.00 SEC)Copy the code

The JSON_INSERT and JSON_SET examples are similar and won’t be demonstrated here. When corresponding to think-model, EXP conditional expression is used for processing, which is written as

//user.js
module.exports = class extends think.Controller {
  async indexAction() {
    const userModel = this.model('user');
    await userModel.where({name: 'lilei'}).update({
      info: ['exp'."JSON_REPLACE(info, '$.age', 20)"]});return this.success(); }}Copy the code

Modify user preferences

mysql> UPDATE `user` SET `info` = JSON_ARRAY_APPEND(`info`, '$.hobby', 'badminton') WHERE `name` = 'lilei'; Query OK, 1 row affected (0.00 SEC) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT JSON_VALUE(`info`, '$.hobby') as hobby FROM `user` WHERE `name` = 'lilei'; +-----------------------------------------+ | hobby | +-----------------------------------------+ | ["basketball", "Football", "massive"] | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.00 SEC)Copy the code

JSON_ARRAY_APPEND is still much easier to manipulate arrays than JSON_INSERT or the like, at least you don’t need to know the length of the array. The corresponding think-model is written as

//user.js
module.exports = class extends think.Controller {
  async indexAction() {
    const userModel = this.model('user');
    await userModel.where({name: 'lilei'}).update({
      info: ['exp'."JSON_ARRAY_APPEND(info, '$.hobby', 'badminton')"]});return this.success(); }}Copy the code

Delete user scores

mysql> UPDATE `user` SET `info` = JSON_REMOVE(`info`, '$.score[0]') WHERE `name` = 'lilei'; Query OK, 1 row affected (0.00 SEC) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT `name`, JSON_VALUE(`info`, '$.score') as score FROM `user` WHERE `name` = 'lilei'; +-------+-----------+ | name | score | +-------+-----------+ | lilei | [90, 100] | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.00 SEC)Copy the code

Deleting this section is similar to the previous modification operation, there is not much to say. But a lot of times when we’re working with arrays we just want to delete the value, but we don’t know what the Path of that value is. In this case, we need to use the JSON_SEARCH() method we talked about earlier, which finds the path based on the value. For example, we want to delete the badminton option in lilei’s interest.

mysql> UPDATE `user` SET `info` = JSON_REMOVE(`info`, JSON_UNQUOTE(JSON_SEARCH(`info`, 'one', 'badminton'))) WHERE `name` = 'lilei'; Query OK, 1 row affected (0.00 SEC) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT JSON_VALUE(`info`, '$.hobby') as hobby FROM `user` WHERE `name` = 'lilei'; +----------------------------+ | hobby | +----------------------------+ | ["basketball", "Football"] | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

Note that JSON_SEARCH does not perform type conversions, so JSON_UNQUOTE() is required for the matched path string. It is also important to note that JSON_SEARCH cannot look up numeric data and does not know if this is a Bug or Feature. That’s why I did my Hobby instead of score. If a numeric type is present, it can only be taken out and handled in code.

mysql> SELECT JSON_VALUE(`info`, '$.score') FROM `user` WHERE `name` = 'lilei'; +-------------------------------+ | JSON_VALUE(`info`, '$.score') | +-------------------------------+ | [90, 100] | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.00 SEC) mysql > SELECT JSON_SEARCH (` info `, 'one', 90, null, '$.score') FROM `user` WHERE `name` = 'lilei'; +-------------------------------------------------+ | JSON_SEARCH(`info`, 'one', 90, null, '$.score') | +-------------------------------------------------+ | NULL | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

The above corresponding think-model is written as

//user.js
module.exports = class extends think.Controller {
  async indexAction() {
    const userModel = this.model('user');
    // Delete the score
    await userModel.where({name: 'lilei'}).update({
      info: ['exp'."JSON_REMOVE(info, '$.score[0]')"]});// Delete interest
    await userModel.where({name: 'lilei'}).update({
      info: ['exp'."JSON_REMOVE(`info`, JSON_UNQUOTE(JSON_SEARCH(`info`, 'one', 'badminton')))"]});return this.success(); }}Copy the code

Afterword.

Recently, there is a requirement that there is a pile of data, and we need to record the sort of this pile of data, so that we can output it according to the sort. The general rule is to add an order field to each piece of data to record the sorting of that piece of data. But because of batch operations, using a single field to store can be particularly cumbersome. At the suggestion of my server colleague, I chose to use JSON fields to store arrays to solve this problem.

Also because of this understanding of MySQL support for JSON, at the same time to do some optimization of the think-model, JSON data type added support. Since most JSON operations require built-in functions, this itself can be done with EXP conditional expressions. You only need to optimize the addition and query of JSON data.

Overall, with the JSON manipulation functions provided, MySQL support for JSON to complete some of the daily needs or no problem. In addition to being a WHERE condition and query field, other operations such as ORDER, GROUP, and JOIN also support JSON data.

However, compared to MongoDB’s natural support for JSON, it is more difficult to operate. In particular, the conversion area is very easy to crash after a while. It’s easy for newbies to freak out about when quotes are and aren’t going to be used, when quotes are and aren’t needed. JSON_SEARCH() does not support numeric searches.