MySQL：Unlocking Power with JSON Functions（mysqljson函数）

Nowadays, data travels across the web in JSON form, and most applications rely on it to link and store information. MySQL has been the reader’s choice for over 20 years for web applications that rely on structured data storage. It is designed to meet the demands of modern web applications by meeting the storage and online processing needs of the applications. Since 2017, MySQL 5.7 has been supporting native JSON functions, enabling full control over JSON documents stored in the database.

First, let’s look at JSON basics. JSON data consists of key-value pairs represented in a hierarchical structure. The keys are always strings, while the values can be strings, numbers, booleans, null, objects and arrays. The keys, followed by a colon, are used to separate the key pair values. Objects and arrays are enclosed in curly brackets and square brackets respectively.

MySQL provides users with powerful functions to store, update, query and manipulate JSON documents. To store documents, users can use the MySQL `JSON_SET()` function. It takes in 3 arguments: a JSON document, the key to insert (or update) and the new value. Here is an example of using `JSON_SET`:

INSERT INTO tablename (fieldname) VALUES (JSON_SET(JSON_OBJECT('area', 'South', 'spots', 'none'), 'area', 'North'));

The first line inserts a new row into the table, containing a key of ‘area’ with value ‘South’ and another key of ‘spots’ with value ‘none.’ The second line adds the ‘area’ key with the updated value ‘North’ if the key already exists, or adds a new key-value pair if it doesn’t.

MySQL gives users the ability to query JSON documents with the `JSON_CONTAINS()` function, which is useful if the item they are looking for is an object or an array. This function helps to narrow down a large collection of documents to just the ones containing the object. For example:

SELECT * FROM tablename WHERE JSON_CONTAINS(fieldname, JSON_OBJECT('area','North'));

This query will return all rows from the table with a key ‘area’ and value ‘North’.

The `JSON_EXTRACT()` function is used to extract a JSON document value by referring to its key. It takes in two arguments, the JSON document and the path to the value, and returns the value. Here’s an example:

SELECT JSON_EXTRACT(fieldname, '$.spots') FROM tablename;

This query will return the value of the ‘spots’ key from each row in the table.

Finally, the `JSON_MERGE()` function is used to merge two or more JSON documents. It takes in multiple JSON documents and returns a single unified document:

SELECT JSON_MERGE(doc1, doc2) FROM tablename;

This query will return a single unified JSON document from merging two documents from the table.

Unlocking the power of MySQL’s native JSON support, users are able to store, query and manipulate JSON documents with ease. These functions enable better control over the data stored in a database, allowing developers to build robust, efficient applications.

To sum up, MySQL’s native JSON functions provide developers with the power to store and query JSON documents, as well as manipulate them easily. With its ability to handle complex operations and its scalability, MySQL is a go-to choice for efficient data storage and manipulation.