How to partial update JSON column in MySQL with sample codes

Pisuke

How to partial update JSON in MySQL.

This article explains how to do so with SQL examples.

Table of Contents
  1. What’s JSON data type in MySQL?
  2. Create table with JSON column
  3. Partial update JSON column
  4. MySQL functions for JSON updates
  5. Increment partial value within JSON

What’s JSON data type in MySQL?

The JSON type has been supported since MySQL.5.7.

What’ JSON data type? :

JSON: MySQL now supports a native JSON data type that enables efficient access to data in JSON (JavaScript Object Notation) documents. The JSON data type provides these advantages over storing JSON-format strings in a string column:

  • Automatic validation of JSON documents stored in JSON columns. Invalid documents produce an error.
  • Optimized storage format. JSON documents stored in JSON columns are converted to an internal format that permits efficient access to document elements.

Along with the JSON data type, a set of SQL functions is available to enable operations on JSON values, such as creation, manipulation, and searching. In addition, the CONVERT() and CAST() functions can convert values between JSON and other types.

Quote : https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-8.html#mysqld-5-7-8-json

This data type allows for the creation, manipulation, and retrieval of JSON (= JavaScript Object Notation), and even allows for partial updates of specific data within JSON.

Create table with JSON column

First of all, let’s create table having JSON data column.

Sample SQL to create a table with JSON column:

CREATE TABLE surveys (
    id BIGINT AUTO_INCREMENT,
    question VARCHAR(256),
    answers_json JSON,
    PRIMARY KEY(id)
)

Sample SQL to insert record to the above table :

INSERT INTO surveys (question, answers_json) VALUES(
    'What colors do you like?',
    '{
        "red": 9,
        "orange": 16,
        "yellow": 8,
        "green": 14,
        "cyan": 6,
        "blue": 17,
        "purple": 5
    }'
);

The JSON data type is treated internally as a LONGTEXT type.

However, unlike the text type, it only accepts JSON format. If a string is malformed as JSON (e.g., missing commas or double quotes), an insertion error will occur.

Partial update JSON column

Now let’s try a partial JSON update.

Sample code to partial update JSON column :

UPDATE surveys SET answers_json 
= JSON_SET(answers_json,'$.red',10);

Just use JSON_SET() function.

The first argument is the JSON column to be updated, the second argument is the key name in JSON starting with $ and separated by dots, and the third argument is the update value.

If the key not exists, new data will be added.

MySQL functions for JSON updates

The following three update functions are available

  • JSON_SET() replaces existing values and adds nonexisting values.
  • JSON_INSERT() inserts values without replacing existing values.
  • JSON_REPLACE() replaces only existing values.

Quote : https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html#function_json-set

Use the three functions for different purposes.

Increment partial value within JSON

It’s also possible to increment JSON partial values.

How to do the increments described here :

  • If target key already exists :
    Just increment the value having the key.
  • If target key not exists :
    Insert new key and set 0 to the value

This requires a slightly more complex SQL.

Sample SQL to increment value within JSON :

UPDATE surveys SET answers_json = JSON_SET(
    answers_json,

    /** Increment $.purple key's value **/
    '$.purple', 
    IF(
        JSON_CONTAINS_PATH(answers_json, 'one', '$.purple') > 0,
        CAST(JSON_EXTRACT(answers_json, '$.purple') AS UNSIGNED)+1,
        0
    ),

    /** * Increment $.black key's value **/
    '$.black', 
    IF(
        JSON_CONTAINS_PATH(answers_json, 'one', '$.black') > 0,
        CAST(JSON_EXTRACT(answers_json, '$.black') AS UNSIGNED)+1,
        0
    )
)

In the above SQL, $.purple is an existing key, so the value obtained by JSON_EXTRACT() will be incremented and updated. On the other hand, $.black is a key that doesn’t exist in JSON, so a new key is inserted with the value 0.

Good luck 👍👍

Leave a Reply