Using Postgres JSONB Fields in Django (2023)

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 (jsonYjsonb) 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?

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 (jsonYjsonb), it is helpful to understand which is the correct option for your applicationjsonthe 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 thatjsonThe data was stored as a special typeTextfield, so the query was slow.

postgresinsertedjsonbin 9.4to combat this problem. not asjsonFelder,jsonbThe fields are stored in abinary structureinstead of strings. While this means that writes are a bit slower, queries fromjsonbFields is significantly faster. It also allows you to indexjsonbfields. what he doesjsonbthe 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:

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 rowsMiguelYHarrietI'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.9jsonbYvarious other Postgres-specific fields. Django's native support means that buildingjsonbFields, 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.jsonbwith 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 JSONclass 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 thosejsonbcolumn 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_profileIn 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 namedGerberwho receives oursdaily_mailYweekly_mail, but notSMSAdverts.

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 theConsultationProperty 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_mailstillboolesch), 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 themSMSoption, you might want to find it and tell users about the new option. You can use the...is zeroField search in theSMSIntroduce 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 mentioningjsonbFields have some drawbacks. I already mentioned that writing data takes a bit longer.jsonbfields likejsonsince the JSON string needs to be converted to binary, but there are other reasons to avoid itjsonbFelder.

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 calledjsonschemaI 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 forjsonbyou 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 Postgresjsonbtest 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.