I remember the first time I incorporated user settings into an application. Initially, users only needed to be able to opt out or opt out of receiving our weekly emails. "Not a big deal" I thought, "I'll just add a new field in the users table." That was fine for a while. A few weeks later, my boss asked me if we could allow users to opt out of receiving push notifications. Well, that's just another column in the database. It can't hurt, right?
You probably see where this is going.
Within a few months, my user table had 40 columns, and that was it.Postgres can handle it, it becomes quite difficult for new developers to keep up with all of them. As you can imagine, it looked quite similar to this Quora settings screen.
Fortunately, Postgres has extensive support forJSON-Felder, which can be very useful in situations like mine. Both JSON data types (json
Yjsonb
) can store entire objects or lists directly in your database. This means that you can store as many user settings as you want in one column.
Why two types of JSON fields?
Query JSONB data in Postgres
(Video) Using the JSONField in DjangoDjango support for JSONB
- Creating JSONB fields using migrations
- Aggregate Data to JSONB Fields
- Querying JSONB fields
Limitations of JSONB fields
Diploma
About the Author
Why two types of JSON fields?
JSON support in Postgre gives you the flexibility of a document storage database likeMongowith the speed and structure of a relational database. JSON support is powerful, but since it comes in two types (json
Yjsonb
), it is helpful to understand which is the correct option for your applicationjson
the data type wasadded in Postgres 9.2and improved in 9.3. This new data type allowed him to saveJSONstore and even query directly in your database. the problem was thatjson
The data was stored as a special typeText
field, so the query was slow.
postgresinsertedjsonb
in 9.4to combat this problem. not asjson
Felder,jsonb
The fields are stored in abinary structureinstead of strings. While this means that writes are a bit slower, queries fromjsonb
Fields is significantly faster. It also allows you to indexjsonb
fields. what he doesjsonb
the preferred format for most JSON data stored in Postgres and the typical choice for Django applications.
Query JSONB data in Postgres
The query syntax for accessing JSON in Postgres is not typical SQL. You must use the specificJSON operators and functions, so queries on JSON data look different than other Postgres queries.
For example, if you have the following data stored in a Postgres table calledProfile
:
ID | Name | preferences |
---|---|---|
1 | Miguel | {"sms": false, "daily_email": true, "weekly_email": true} |
2 | lucia | {"sms": true, "daily_email": false, "weekly_email": false} |
3 | Harriet | {"sms": true, "daily_email": true, "weekly_email": true} |
And you wanted to consult all the users who voted for yoursdaily_mail
, you would write a query like this:
choose * outsideProfileWo (preferences->>'daily_mail')::boolesch = TRUE;
that would return the rowsMiguel
YHarriet
I'm pretty good with SQL, but using JSON operators always slows me down. Fortunately, Django has support for JSONB fields, so he doesn't need to become an expert at querying JSON in Postgres.
Django support for JSONB
The popular Python framework has been supported since Django 1.9jsonb
Yvarious other Postgres-specific fields. Django's native support means that buildingjsonb
Fields, using them in your models, inserting data into them, and querying them is all possible with the Django ORM. Let's take a look at how to start using it.jsonb
with Django.
Creating JSONB fields using migrations
Django's Postgres module includes several field classes that you can import and add to your models. If you want to use a JSON field, import thecampo JSON
class and use it for your model property. In this example we call the fieldpreferences
:
outsideDjango.databasemattermodelsoutsideDjango.Contribution.postgraduate.Feldermattercampo JSONClass Profile(models.Model):Name=models.CharField(maximum length=200)preferences=campo JSON() definitely __str__(to be): go backto be.Name
Django only supports thosejsonb
column type So when you run your migrations, Django creates a table definition like this:
create Tischapplication_profile(IDserial no Null Obligationapp_profile_pkeyprimary Gusto,Namevarchar(200) no Null,jsonb tweaksno Null);
Aggregate Data to JSONB Fields
Because JSON fields don't enforce any particular schema, Django converts any valid Python data type (dictionary, list, string, number, boolean) to the corresponding JSON. For example, if you want to add a new lineapplication_profile
In the table created above, you can do the following in your Django application:
outsideapplication.modelsmatterProfile# Create a profile with settingsPAG=Profile(Name="Gerber",preferences={'SMS': INCORRECT, 'daily_mail': TRUE, 'email_weekly': TRUE})PAG.save to computer()
This will create a new user namedGerber
who receives oursdaily_mail
Yweekly_mail
, but notSMS
Adverts.
Querying JSONB fields
Django uses the double underscore patternfield searchesto query for JSON object keys. For example, if you want to obtain all the profiles of the users who have opted for oursdaily_mail
, I would use the following code:
Results=Profile.objects.Filter(Configuration__daily_email=TRUE)
If you want to check the SQL query executed by Django, you can use theConsultation
Property for the result object:
press(Results.Consultation)# Salida:CHOOSE"application_profile"."ID", "application_profile"."Name", "application_profile"."Preferences"OUTSIDE"application_profile"WO("application_profile"."Preferences" ->daily_mail) = 'TRUE'
As you can see, the query is slightly different from the one I manually wrote above (I randaily_mail
stillboolesch
), but achieves the same thing. You can also filter records based on the keys they contain. For example, if some user accounts were created before adding themSMS
option, you might want to find it and tell users about the new option. You can use the...is zero
Field search in theSMS
Introduce tus datos JSON:
Results=Profile.objects.Filter(Setting__SMS__isNull=TRUE)
There are many other ways to filter queries using JSON fields, so pay attention.For more information, see the official Django docs..
Limitations of JSONB fields
worth mentioningjsonb
Fields have some drawbacks. I already mentioned that writing data takes a bit longer.jsonb
fields likejson
since the JSON string needs to be converted to binary, but there are other reasons to avoid itjsonb
Felder.
First, if your data needs to apply a strict schema, JSON might not be an ideal choice. While youyou can use check constraintsEnforcing the use of certain fields is not natively supported in Django, so you'll need to write your own migrations to achieve this.
A better way to address this deficiency is to write Django validation rules to enforce the desired structure. If you don't want to write the validation rules yourself, there is a popular package calledjsonschema
I would recommend that.
Another disadvantage of using JSON fields is dealing with changes in the shape of your data. If you want to add a new column to a database table in Postgres with Django, just update your model and do a migration. If you want to add a new field to a JSON column, it's not that simple.
One pattern I've used before is to create a custom migration that iterates through the affected records and updates each one individually. This naive method works for relatively small data sets, but might not be a good idea when you need to update 1 million profiles in a production database. In that case, it might be better to write your code to handle the presence or absence of the key, or to run aBatch update for JSON object.
Diploma
Although JSON data types have some drawbacks, they are useful in situations where you need more flexibility in your data structure. Thanks to Django's native support forjsonb
you can start using JSON data in your web applications without learning all the native Postgres query operators.
The next time you need more flexibility in your data model and want to take advantage of the power of Postgresjsonb
test fields.
Share this article:If you liked this article, you might want totweet it to your colleagues.
About the Author
Karl Hughes is a technology team leader and software engineer. He is currently the founder ofscribbler, where he helps create technical content for engineering blogs.
Sign up for the pganalyze newsletter
Receive occasional emails about interesting Postgres content on the web, new pganalyze feature releases, and new pganalyze eBooks. No spam, I promise.