CREATE INDEX ON my_data using gin(my_json_field jsonb_path_ops) Now that we have a JSON field, we can create an index on it so that we can perform complex queries using the data stored there. Now we can query an object normally, and access the JSON field as a dictionary: data = _by(id="my_id").first() my_dict = data.my_json_field Indexing by JSON columns However, during the rest of this post I’ll refer to our data type as JSON. We use the JSONB type instead of the plain JSON type because it allows way more functionalities that are not possible with the other, older type. Even though at many points we resorted to SQL queries to extract the full potential of PostgreSQL, you can do the basic things like inserting and retrieving data without that.įor example, creating a model with a JSON field in Python using SQLAlchemy is as simple as this: from sqlalchemy import SQLAlchemy from sqlalchemy.dialects import postgresql db = SQLAlchemy() class Data(db.Model): _tablename_ = "my_data" id = db.Column(db.String, primary_key=True) my_json_field = db.Column(postgresql.JSONB) Support on SQLAlchemyįirst of all, you can use JSON on top of an ORM like SQLAlchemy. In this post I will explain some of the more interesting things that can be done with JSON fields on PostgreSQL, finishing with a real case which has some complexity even after reading the documentation or some tutorials. At Worldsensing we have used JSON on PostgreSQL a lot in some of our micro-services.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |