all that is technology

Using SQLAlchemy Expression for Partial JSON Update


In this article, I'll show how to partially update JSON column using SQLAlchemy expressions in MySQL database.

Say, we have a table named TableName with a JSON column named JsonColumn and the values in this JSON column is in the following format;

    "JsonAttribute": 5,
    "AnotherJsonAttribute": "Hello"

SQL statement to update a single JSON attribute is as below;

UPDATE TableName SET JsonColumn = JSON_SET(JsonColumn, '$.JsonAttribute', 10) WHERE Id = 100;

Converting the above SQL statement to the corresponding SQLAlchemy expression is as below;

from sqlalchemy import update, func

table_name_id = 100
json_attribute_value = 10

update_sql = update(TableName).values(
    JsonColumn=func.json_set(TableName.JsonColumn, '$.JsonAttribute', json_attribute_value)).\
    where(TableName.Id == table_name_id)

result = db.session.execute(update_sql)

For running a bulk update on the array of dicts using WHERE condition.

from sqlalchemy import update, func, bindparam

update_data = [{
    '_Id': 100,
    'Value': 10
}, {
    '_Id': 101,
    'Value': 11
}, {
    '_Id': 102,
    'Value': 12

update_sql = update(TableName).values(
    JsonColumn=func.json_set(TableName.JsonColumn, '$.JsonAttribute', bindparam('Value'))).\
    where(TableName.Id == bindparam('_Id'))

result = db.session.execute(update_sql, update_data)

Hope this helps!