websites & programming

Using Postgres and JSONB with Python

This week I was the presenter at the Tampa Bay Python Meetup. The subject of my presentation was using JSONB fields with Python libraries such as Django and SQLAlchemy. The JSONB (binary JSON) field type became available in version 9.4 of the PostgreSQL database management system. Many people have been interested in JSONB because it offers many of the same benefits that document databases such as MongoDB have.

I had not worked much with JSON or document databases in the past, so I was happy to discover that creating an object with a JSONB field and querying against it was very straightforward in both Django and SQLAlchemy. For example, when using SQLAlchemy, you can just import JSONB from dialects.postgresql in the library:

from sqlalchemy.dialects.postgresql.json import JSONB

You can then include a JSONB field in a table in the same manner as the other field types that are typically found in relational databases. Having a mix of JSON and non-JSON fields is no problem, e.g.,:

class Bargain(Base):
    __tablename__ = 'bargains'
    id = Column(Integer, primary_key=True)
    sku = Column(String(20), nullable=False)
    price = Column(Numeric, nullable=False)
    supplier_id = Column(Integer, ForeignKey(''), nullable=False))
    info = Column(JSONB)

There are various operators available in Postgres for querying JSONB, such as the indexing and containment operators. To take an example, assume that the bargains table above has a record with the following JSON data in the info field:

    "description": "Super Poodle",
    "sale_price": 179.99,
    "animal_info": {
        "weight": 25,
        "fur_color": "white",
        "eye_color": "green"

If one wanted to query for records where the bargain is an animal with green eyes, then the SQLAlchemy query might look like this when using the indexing operator:

bargains = DBSession.query(Bargain).filter([('animal_info', 'eye_color')].astext == 'green'

I found using a contains query to be more natural, however. Here is the same query using the containment operator. Note how the syntax for the value being queried is identical to the syntax for the JSON data stored in the info field:

bargains = DBSession.query(Bargain).filter({"animal_info": {"eye_color": "green"}})

Overall I’ve enjoyed learning more about JSON and JSONB and find them pretty easy to work with. While I was creating the sample application for the presentation, I did realize that there is still quite a bit more to learn on the subject. Indexes in particular were a bit difficult to find good examples for. You really cannot create indexes for JSONB fields in the same way as you would for transactional data, for instance (at least you can’t if you want the index to work well, that is!). But that is a topic for a later date. The code for my sample application is available on GitHub for anyone who is interested. There is both a Pyramid / SQLAlchemy and a Django version of the application.