mysql how to update json field

How to use MySQL to update a JSON field?

To update a JSON field in MySQL, use the JSON_SET() function. This function takes two arguments: a JSON path (specifying the location of the field to update) and the new value.

For example, to update the name field of the user object in the following JSON document:

<code>{
  "user": {
    "name": "John Doe",
    "age": 30
  }
}</code>

You would use the following query:

<code>UPDATE table_name SET json_field = JSON_SET(json_field, '$.user.name', 'Jane Doe')
WHERE ...;</code>

How do I efficiently update multiple values within a JSON field in MySQL?

To efficiently update multiple values within a JSON field in MySQL, use the JSON_REPLACE() function. This function takes two arguments: a JSON path (specifying the location of the field to update) and the new value. The new value can be a JSON object, array, or scalar.

For example, to update the name and age fields of the user object in the previous example, you would use the following query:

<code>UPDATE table_name SET json_field = JSON_REPLACE(json_field, '$.user', JSON_OBJECT('name', 'Jane Doe', 'age', 31))
WHERE ...;</code>

What is the syntax for updating a nested JSON field in MySQL?

To update a nested JSON field in MySQL, use the JSON_SET() function with a nested JSON path. A nested JSON path is a JSON path that contains a dot (.) to separate nested field names.

For example, to update the street field of the address object within the user object in the previous example, you would use the following query:

<code>UPDATE table_name SET json_field = JSON_SET(json_field, '$.user.address.street', '123 Main Street')
WHERE ...;</code>

The above is the detailed content of mysql how to update json field. For more information, please follow other related articles on the PHP Chinese website!