skip to content

In SQLAlchemy, a JSON column mapped with MutableDict.as_mutable(JSON) saves top-level key edits but drops a nested edit; why, and how do you fix it?

level: seniorimportance: should knowfreq 25%

answer

  1. change events, not value diffs
  2. plain JSON sees only reassignment
  3. MutableDict tracks one level
  4. flag_modified forces the write
  5. coerce nested values yourself

basics

~20 s

The ORM notices a JSON change only through attribute events. MutableDict emits them for its own keys, but nested dicts stay plain, so sub.billing['renewal']['auto'] = False marks nothing dirty. Assign a new dict, call flag_modified(sub, 'billing'), or coerce nested values.

solid answer

~40 s

SQLAlchemy's `JSON` type does not detect in-place mutation: changing a key of a plain `JSON` attribute fires no attribute event, so the flush writes nothing. `MutableDict.as_mutable(JSON)` coerces each loaded or assigned dict into a `MutableDict`, whose `__setitem__`, `update()` and other mutators call `changed()` and flag the parent attribute. It does not track the values inside it, so the dict at `'renewal'` is an ordinary `dict` and editing it is invisible. Three fixes: assign a new structure (`sub.billing = {**sub.billing, ...}`), call `sqlalchemy.orm.attributes.flag_modified(sub, 'billing')` after the edit, or subclass `MutableDict` so its `coerce()` wraps nested dicts and lists that report changes upward. Whichever you pick, the UPDATE rewrites the whole JSON value.

code

python · 35 lines
python
from sqlalchemy import JSON, create_engine
from sqlalchemy.ext.mutable import MutableDict
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column
from sqlalchemy.orm.attributes import flag_modified


class Base(DeclarativeBase):
    pass


class Subscription(Base):
    __tablename__ = "subscription"

    id: Mapped[int] = mapped_column(primary_key=True)
    billing: Mapped[dict] = mapped_column(MutableDict.as_mutable(JSON))


engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
with Session(engine) as session:
    sub = Subscription(billing={"tier": "basic", "renewal": {"auto": True}})
    session.add(sub)
    session.commit()

    sub.billing["tier"] = "pro"  # top-level key: MutableDict sees it
    print(sub in session.dirty)  # True
    session.commit()

    sub.billing["renewal"]["auto"] = False  # nested dict: plain dict, unseen
    print(sub in session.dirty)  # False

    flag_modified(sub, "billing")  # force the whole value to be written
    print(sub in session.dirty)  # True
    session.commit()
    print(sub.billing)

go deeper

for a junior

Recall that editing a JSON dict in place is not saved by default, and that reassigning the attribute always is.

for a middle

Explain attribute events, what MutableDict.as_mutable() adds, and how flag_modified() forces an UPDATE.

for a senior

Diagnose a lost nested edit from a commit with no UPDATE, and choose between reassignment, flag_modified() and deep coercion.

for a principal

Set the team rule for JSON columns: immutable reassignment, flagged edits, or moving often-edited keys into real columns.

## How the ORM notices changes A SQLAlchemy `Session` decides what to UPDATE from **attribute events**. Setting `sub.billing = {...}` goes through the attribute's descriptor, which records the change in the object's history; the flush then writes every attribute that has history. - Assigning a new value to the attribute is always recorded. - Mutating an object **held** by the attribute does not go through the descriptor, so nothing is recorded. - The flush does not scan loaded JSON values for differences; it writes only attributes whose history records a change. - The `JSON` type documentation states it directly: used with the ORM, `JSON` does not detect in-place mutations to the structure. ## What plain JSON misses With `billing: Mapped[dict] = mapped_column(JSON)`, the attribute holds an ordinary `dict`. `sub.billing["tier"] = "pro"` changes that dict, `sub in session.dirty` stays `False`, and the commit emits no UPDATE. The change survives only in memory, until the object is expired and reloaded. ## What MutableDict adds, and where it stops `sqlalchemy.ext.mutable.MutableDict` is a `dict` subclass whose mutating methods call `self.changed()`, which flags the attribute on every parent object holding it. `MutableDict.as_mutable(JSON)` returns a type that coerces loaded and assigned dicts into `MutableDict` instances. | Edit | Plain `JSON` | `MutableDict.as_mutable(JSON)` | |---|---|---| | `sub.billing = {...}` | written | written (the new dict is coerced) | | `sub.billing["tier"] = "pro"` | lost | written | | `del sub.billing["tier"]`, `sub.billing.update(...)` | lost | written | | `sub.billing["renewal"]["auto"] = False` | lost | **lost** | The class documentation is explicit: `MutableDict` does not apply mutable tracking to the values inside the dictionary, so it is not enough to track deep changes to a recursive structure such as a JSON document. ## Fixes 1. **Assign a new value.** `sub.billing = {**sub.billing, "renewal": {**sub.billing["renewal"], "auto": False}}`. Always seen, no extension needed. 2. **Flag the attribute.** Edit in place, then call `flag_modified(sub, "billing")` from `sqlalchemy.orm.attributes`. This records an unconditional change, so the flush writes the current value. 3. **Track deeper.** Subclass `MutableDict` and override `coerce()` so nested dicts and lists are wrapped in mutable types that propagate `changed()` to their parent structure. In every case the UPDATE sends the **whole serialised document**, for example `UPDATE subscription SET billing=? WHERE subscription.id = ?`. ## A worked sequence The code example below walks through the whole behaviour on one `Subscription`: 1. The object is created with `billing={"tier": "basic", "renewal": {"auto": True}}` and committed; the dict is coerced into a `MutableDict` on assignment and again on every load. 2. `sub.billing["tier"] = "pro"` calls `MutableDict.__setitem__`, the object appears in `session.dirty`, and the commit writes the whole document. 3. `sub.billing["renewal"]["auto"] = False` edits the inner plain `dict`; `session.dirty` stays empty. 4. `flag_modified(sub, "billing")` records the change by force, the object is dirty again, and the commit writes `{"tier": "pro", "renewal": {"auto": false}}`. ## Choosing a fix | Fix | Code at each edit | Nested edits saved | Risk | |---|---|---|---| | Reassign a new structure | build a copy | yes, by replacement | verbose for deep edits | | `flag_modified(sub, "billing")` | one call after the edit | yes, by force | a forgotten call loses the edit silently | | Deep-coercing `MutableDict` subclass | none | yes, through events | more code to own and test | One workable arrangement: `MutableDict.as_mutable(JSON)` for top-level keys, plus a rule that nested structures are always replaced through a small helper that copies, edits and reassigns. ## Related details - `MutableList` and `MutableSet` cover JSON arrays and set-valued types the same way, one level deep. - `MutableDict.associate_with(SomeType)` applies tracking to every future mapping of that type instead of per column. - `flag_modified()` needs the attribute loaded; on an expired one it raises `InvalidRequestError: Can't flag attribute 'billing' modified; it's not present in the object state`. - `flag_dirty(sub)` is different: it puts the object into the flush for event handlers, but with no attribute change recorded no SQL is emitted. - `JSON(none_as_null=False)`, the default, persists Python `None` as JSON `null`, not SQL NULL. ## Diagnosing it - Check `sub in session.dirty` or `session.is_modified(sub)` right after the edit. - With `echo=True`, a commit that shows no UPDATE for the column confirms the edit was never recorded. - Search the code for item assignment on JSON attributes at a second level; each one needs one of the fixes above.

  • What does flag_modified() require, and how is flag_dirty() different?
    `flag_modified(sub, 'billing')` needs the attribute loaded; on an expired attribute it raises `InvalidRequestError`. `flag_dirty(sub)` only puts the object into the flush so that `before_flush` handlers can see it. With no attribute change recorded, no UPDATE is emitted, so it does not rescue a missed JSON edit.
  • How do MutableDict.as_mutable() and MutableDict.associate_with() differ?
    `MutableDict.as_mutable(JSON)` returns a type you place on specific columns, so only those attributes are tracked. `MutableDict.associate_with(SomeType)` applies tracking to every future mapping that uses that type, without declaring it per column. Per-column use is the narrower, more explicit choice.
  • How does SQLAlchemy's JSON type store a Python None by default?
    With `none_as_null=False`, the default, `None` is written as the JSON value `null`, not SQL NULL, so an `IS NULL` filter does not match it. Pass `none_as_null=True`, or use `sqlalchemy.null()` as the value, to store SQL NULL.

  • MutableDict tracks changes at any depth of a JSON document
  • The Session compares JSON values at flush, so any in-place edit is saved
  • flag_modified only works on columns wrapped in MutableDict
  • Assigning a plain dict to a MutableDict column switches tracking off
  • flag_dirty(sub) is enough to save a nested JSON edit