Having to add a series of custom fields to a quite relational application, I decided to try the new JSON fields.
As of now you can:
– create json fields
– manipulate them with json_extract, json_unquote
– create generated fields from json entries
You can not:
– index json fields directly, create a generated field and index it
– retain the original json datatype (eg. string, int), as json_extract always returns strings.
Let’s start with a simple flask app:
# requirements.txt mysql-connector-python Flask-SQLAlchemy==2.0 SQLAlchemy>=1.1.3
Let’s create a simple flask app connected to a db.
import flask import flask_sqlalchemy from sqlalchemy.dialects.mysql import JSON # A simple flask app connected to a db app = flask.Flask('app') app.config['SQLALCHEMY_DATABASE_URI']='mysql+mysqlconnector://root:secret@localhost:3306/test' db = flask_sqlalchemy.SQLAlchemy(app)
Add a class to the playground and create it on the db. We need sqlalchemy>=1.1 to support the JSON type!
# The model class MyJson(db.Model): name = db.Column(db.String(16), primary_key=True) json = db.Column(JSON, nullable=True) def __init__(self, name, json=None): self.name = name self.json = json # Create table db.create_all()
Thanks to flask-sqlalchemy we can just db.session ;)
# Add an entry entry = MyJson('jon', {'do': 'it', 'now': 1}) db.session.add(entry) db.session.commit()
We can now verify using a raw select that the entry is now serialized on db
# Get entry in Standard SQL entries = db.engine.execute(db.select(columns=['*'], from_obj=MyJson)).fetchall() (name, json_as_string), = first_entry # unpack result (it's just one!) assert isinstance(json_as_string, basestring)
A raw select to extract json fields now:
entries = db.engine.execute(db.select(columns=[name, 'json_extract(json, "$.now")'], from_obj=MyJson)).fetchall() (name, json_now), = first_entry # unpack result (it's just one!) assert isinstance(json_now, basestring) assert json_now != entry.json['now'] # '1' != 1