JSON 扩展属性

GINO provides additional support to leverage native JSON type in the database as flexible GINO model fields.

Quick Start

from gino import Gino
from sqlalchemy.dialects.postgresql import JSONB
db = Gino()
class User(db.Model):
    __tablename__ = "users"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    profile = db.Column(JSONB, nullable=False, server_default="{}")
    age = db.IntegerProperty()
    birthday = db.DateTimeProperty()

The age and birthday are JSON properties stored in the profile column. You may use them the same way as a normal GINO model field:

u = await User.create(name="daisy", age=18)
print(u.name, u.age)  # daisy 18

注解

profile is the default column name for all JSON properties in a model. If you need a different column name for some JSON properties, you’ll need to specify explicitly:

audit_profile = db.Column(JSON, nullable=False, server_default="{}")
access_log = db.ArrayProperty(prop_name="audit_profile")
abnormal_detected = db.BooleanProperty(prop_name="audit_profile")

Using JSON properties in queries is supported:

await User.query.where(User.age > 16).gino.all()

This is simply translated into a native JSON query like this:

SELECT users.id, users.name, users.profile
FROM users
WHERE CAST((users.profile ->> $1) AS INTEGER) > $2;  -- ('age', 16)

Datetime type is very much the same:

from datetime import datetime
await User.query.where(User.birthday > datetime(1990, 1, 1)).gino.all()

And the generated SQL:

SELECT users.id, users.name, users.profile
FROM users
WHERE CAST((users.profile ->> $1) AS TIMESTAMP WITHOUT TIME ZONE) > $2
-- ('birthday', datetime.datetime(1990, 1, 1, 0, 0))

Here’s a list of all the supported JSON properties:

Hooks

JSON property provides 2 instance-level hooks to customize the data:

class User(db.Model):
    __tablename__ = "users"
    id = db.Column(db.Integer, primary_key=True)
    profile = db.Column(JSONB, nullable=False, server_default="{}")
    age = db.IntegerProperty()
    @age.before_set
    def age(self, val):
        return val - 1
    @age.after_get
    def age(self, val):
        return val + 1
u = await User.create(name="daisy", age=18)
print(u.name, u.profile, u.age)  # daisy {'age': 17} 18

And 1 class-level hook to customize the SQLAlchemy expression of the property:

class User(db.Model):
    __tablename__ = "users"
    id = db.Column(db.Integer, primary_key=True)
    profile = db.Column(JSONB, nullable=False, server_default="{}")
    height = db.JSONProperty()
    @height.expression
    def height(cls, exp):
        return exp.cast(db.Float)  # CAST(profile -> 'height' AS FLOAT)

Create Index on JSON Properties

We’ll need to use declared_attr() to wait until the model class is initialized. The rest is very much the same as defining a usual index:

class User(db.Model):
    __tablename__ = "users"
    id = db.Column(db.Integer, primary_key=True)
    profile = db.Column(JSONB, nullable=False, server_default="{}")
    age = db.IntegerProperty()
    @db.declared_attr
    def age_idx(cls):
        return db.Index("age_idx", cls.age)

This will lead to the SQL below executed if you run db.gino.create_all():

CREATE INDEX age_idx ON users (CAST(profile ->> 'age' AS INTEGER));

警告

Alembic doesn’t support auto-generating revisions for functional indexes yet. You’ll need to manually edit the revision. Please follow this issue for updates.