mysql JSON_REPLACE json_set

原创

mob649e815da088 2024-05-01 05:13:53 ©著作权

©著作权归作者所有：来自51CTO博客作者mob649e815da088的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL JSON_REPLACE and JSON_SET Explained

In MySQL, JSON data type was introduced in version 5.7.8, allowing users to store and manipulate JSON data. JSON_REPLACE and JSON_SET are two important functions that can be used to update or add values in a JSON document stored in a MySQL database.

JSON_REPLACE

The JSON_REPLACE function is used to replace existing values in a JSON document with new values. It takes three arguments: the original JSON document, the path to the value that needs to be replaced, and the new value to replace it with.

Here is an example of how JSON_REPLACE works:

SET @json = '{"name": "John", "age": 30}';
SELECT JSON_REPLACE(@json, '$.age', 25);

In this example, the original JSON document is {"name": "John", "age": 30}. The JSON_REPLACE function is used to replace the value of the key age with 25. The result would be {"name": "John", "age": 25}.

JSON_SET

The JSON_SET function is used to add or update values in a JSON document. It takes multiple arguments: the original JSON document, one or more key-value pairs representing the paths and new values to be added or updated.

Here is an example of how JSON_SET works:

SET @json = '{"name": "John", "age": 30}';
SELECT JSON_SET(@json, '$.city', 'New York', '$.country', 'USA');

In this example, the original JSON document is {"name": "John", "age": 30}. The JSON_SET function is used to add two new key-value pairs city: New York and country: USA. The result would be {"name": "John", "age": 30, "city": "New York", "country": "USA"}.

Example Use Case

Let's consider a scenario where we have a table users that stores user details in JSON format. We want to update the address of a specific user with user_id = 1 to 123 Main Street.

UPDATE users
SET details = JSON_SET(details, '$.address', '123 Main Street')
WHERE user_id = 1;

In this example, we are using the JSON_SET function to update the address key in the details JSON column of the users table for the user with user_id = 1.

Conclusion

In conclusion, JSON_REPLACE and JSON_SET are powerful functions in MySQL that allow users to manipulate JSON data stored in a database. These functions provide a convenient way to update or add values in JSON documents without having to fetch the entire document, modify it, and then save it back to the database.

By leveraging these functions, developers can efficiently work with JSON data in MySQL databases and simplify their data manipulation tasks.

References:

[MySQL JSON Functions](

Image Source:

pie
    title JSON Functions
    "JSON_EXTRACT" : 40
    "JSON_REPLACE" : 30
    "JSON_SET" : 30

Remember to always backup your data before performing any updates or modifications to your database. Exercising caution and testing your queries thoroughly is crucial to avoid unintended consequences. Happy coding!