Updating PostgreSQL JSON fields via SQLAlchemy

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.

***

6 Responses to “Updating PostgreSQL JSON fields via SQLAlchemy”

  • John writes:

    Howdy just wanted to give you a quick heads up and let
    you know a few of the pictures aren’t loading properly.
    I’m not sure why but I think its a linking issue. I’ve tried it in two different internet browsers and both show the
    same outcome.

  • Benjamin writes:

    This isn’t helpful. Which pictures? The post you’ve commented on has no images.

    If it’s an older post, you’ll need to link to it.

  • opyate writes:

    Somewhat related: I whipped up an example showcasing JSONB equality checking with SQLAlchemy and PostgresQL 9.4: https://github.com/opyate/sqlalchemy-jsonb-pg94

  • Daz writes:

    This saved me alot of time, thank you!

  • Mikko Ohtamaa writes:

    This is not issue with PostgreSQL and JSON, but a general issue that the default Python dictionaries do not propagate changes to their parent objects.

    For example, ZODB solved this issue back in 2000 by using a PersistentDict class.

    For PostgreSQL a mutable dictionary recipe exists: http://variable-scope.com/posts/mutation-tracking-in-nested-json-structures-using-sqlalchemy

  • Benjamin writes:

    I edited this post some time after writing it to clarify the examples, so I may not have made it clear that my intentions were to come up with a means of partially updating JSON fields. So while you’re right on the Python end (the problem, in the context of SQLAlchemy, is certainly with change propagation), I disagree about Postgres: It certainly is an issue with JSON and Postgres.

    To illustrate, I would like to challenge you to incrementally update JSON fields in any language with Postgres bindings (which was the original problem statement that brought me to writing this post) but do so without passing the entire data structure back to Postgres.

    You can’t do this. Why? Because as of Postgres 9.4, there’s no way to selectively update JSON fields. You have to update the entire column. That was the source of my initial complaint, because there’s presently no means to do this. Even if you are detecting changes in a given data structure, the only way to push that to the server is to update the field in its entirety. According to this question, and the accepted answer on Stack Overflow, PostgreSQL 9.5 should be providing some framework (borrowing some HSTORE behaviors) to do at least a partial incremental update.

    I seem to remember a comment on the SQLAlchemy mailing list at some point a couple years ago that they had no intention to provide this behavior out of the box until Postgres supported partial JSON updates. Although I doubt that will happen, because it’s not an SQLAlchemy issue (or responsibility) as far as I’m concerned.

    So yes, absolutely this does apply to Postgres and JSON–and Python.

    I guess I’m going to have to re-edit this post to make it clear what problem I was intending to resolve.

Leave a comment

Valid tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>