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?
answer
- change events, not value diffs
- plain JSON sees only reassignment
- MutableDict tracks one level
- flag_modified forces the write
- coerce nested values yourself
basics
~20 sThe 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 sSQLAlchemy'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 linesfrom 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
Recall that editing a JSON dict in place is not saved by default, and that reassigning the attribute always is.
Explain attribute events, what MutableDict.as_mutable() adds, and how flag_modified() forces an UPDATE.
Diagnose a lost nested edit from a commit with no UPDATE, and choose between reassignment, flag_modified() and deep coercion.
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