If you’ve found this article, you may have discovered that as of PostgreSQL 9.3, there’s no immediately obvious way to easily (for some value of “easy”) update JSON columns (and their fields) in place like you sort of can with HSTORE when using SQLAlchemy. Supposedly, PostgreSQL 9.4 may be adding this feature, which means we’ll have to wait some time thereafter for support to appear in psycopg2 and, by extension, SQLAlchemy.

Update December 20th: PostgreSQL 9.5 will be adding support for incremental updates but you must use JSONB column types. The reason for this is explained in the manual but essentially boils down to this: JSON is stored as text, JSONB is pre-processed into a binary format and stored internally. Besides, you really ought to be using JSONB instead!

Anyway, as it turns out, updating JSON isn’t a big deal, and SQLAlchemy has some really great support for JSON columns. The only thing you need to do is update your JSON field as you would on any other instance if you’re using the ORM, but you still can’t update a specific subset of the JSON column (for that, it needs DBMS support). It’s that easy.

Here’s a contrived example using the Python shell:

# Fetch our object. Pretend there are no errors and it's guaranteed to exist.
>>> config = session.query(Config).filter(Config.id==1).one()
 
# Our object in this case is a configuration instance with some JSON data.
>>> config.values
{"do_something": true}
 
# Change it.
>>> config.values["do_something"] = false
>>> from sqlalchemy.orm.attributes import flag_modified
 
# flag_modified is necessary in this case, especially for complicated JSON structures.
# If you don't use it, you might discover that updated contents will never persist
# to the database because SQLAlchemy isn't aware that the field was changed.
>>> flag_modified(config, "values")
 
# Commit your changes.
>>> session.commit()

Or use the session directly

 
# Here's what we want to save:
>>> values
{"do_something": true}
 
# Change it.
>>> value["do_something"] = false
 
# Update it in place, for instance using the ORM (or you could the same thing using
# the expression language). You won't need flag_modified in this case, because
# you're overwriting the whole field.
>>> session.query(Config).filter(Config.id == 1).update({"values": values})
>>> session.commit()

The maddening bit about this was how long it took me to figure it out! But hey, I like to share. Hopefully this will save you some time.

Update October 16th, 2015: Included flag_modified usage for updating instances directly.