abelcastro.dev

I learnt today that this is not possible with the Django ORM to call update in a queryset json field when the key that you want to update is not present. For example:

queryset.update(json_data__key="something")

This will return FieldDoesNotExist: ModelClass has no field named 'json_data__key' because some objects does not have the key “key”.

I found in this stackoverflow post a great solution for this.

from django.db.models.expressions import Func

class JsonSetValue(Func):
    function = "jsonb_set"
    template = "%(function)s(%(expressions)s, '{\"%(keyname)s\"}','\"%(new_value)s\"', %(create_missing)s)"
    arity = 1

    def __init__(
        self,
        expression: str,
        keyname: str,
        new_value: str,
        create_missing: bool = False,
        **extra,
    ):
        super().__init__(
            expression,
            keyname=keyname,
            new_value=new_value,
            create_missing="true" if create_missing else "false",
            **extra,
        )

With help of JsonSetValue you will be able to do the following:

queryset.update(
    json_data=JsonSetValue(
        "json_data",
        keyname="key",
        new_value="something",
        create_missing=True,
    )
)

The goal:

Sync some model fields between two different models.

Example scenario:

class PlayerData(models.Model):
	goals = models.IntegerField()
	assists = models.IntegerField()
	tackles = models.IntegerField()
	# 10 fields more with player data 


class PlayerMostRelevantData(models.Model):
	player_data_id = models.ForeignKey(PlayerData, on_delete=models.CASCADE)
	goals = models.IntegerField()
	assists = models.IntegerField()

We want to keep the data of PlayerData and PlayerMostRelevantData on sync by every create, update and object deletion.

Possible solutions:

Of course for this task we could use django signals but a more reliable solution would be to make the data synchronisation at database level using Postgres triggers.

Following SQL-Snippet explain what we want to achieve. If you run the statements you will see that inserting a new row in the table called "original" will create a new row with the same content in the table "original_copy".

drop table if exists original;
create table original(id int, name text);
drop table if exists original_copy;
create table original_copy(original_id int PRIMARY KEY, name text);


create or replace function trigger_on_example()
returns trigger language plpgsql as $$
begin
    insert into original_copy (original_id, name)
    values(new.id, new.name)
    ON CONFLICT (original_id)
    DO
        UPDATE SET name = new.name;
	return new;
end
$$;

create trigger trigger_on_example
before insert or update on original
for each row execute procedure trigger_on_example();

insert into original (id, name)
values (1, 'John');

select *
from original;

select *
from original_copy;

The solution

For our example with the PlayerData and PlayerMostRelevantData we can write a django migration like this that creates the Postgres triggers for us in the database.


from django.db import connection, migrations


def create_triggers(apps, schema_editor):
    with connection.cursor() as cursor:
        # player data create or update trigger
        cursor.execute(
            """
            create or replace function trigger_on_player_data_create_or_update()
            returns trigger language plpgsql as $$
            begin
               insert into players_playerdata(player_data_id, goals, assists, tackles)
               values(new.id, new.goals, new.assists, new.tackles)
               ON CONFLICT (player_data_id)
               DO
                   UPDATE SET player_data_id = new.id, goals = new.goals,
                   assists = new.assists;
               return new;
            end
            $$;
            create trigger trigger_on_player_data_create_or_update
            before insert or update on players_playerdata
            for each row execute procedure trigger_on_player_data_create_or_update();
            """
        )

        # player data delete trigger
        cursor.execute(
            """
            create or replace function trigger_on_player_data_delete()
            returns trigger language plpgsql as $$
            begin
               delete from players_playermostrelevantdata where player_data_id=old.id;
               return NULL;
            end
            $$;
            create trigger trigger_on_player_data_delete
            before delete on players_playerdata
            for each row execute procedure trigger_on_player_data_delete();
            """
        )


class Migration(migrations.Migration):

    dependencies = [
        ("players", "0001_initial"),
    ]

    operations = [migrations.RunPython(create_triggers, reverse_code=migrations.RunPython.noop)]

Sources and inspiration:

https://www.postgresqltutorial.com/postgresql-upsert/
https://stackoverflow.com/a/40901037
https://objectpartners.com/2019/01/24/creating-raw-sql-migrations-in-django/

abelcastro.dev

Django ORM - call queryset update() in json field when the key to update is not present

Sync model fields using Postgres triggers

My self made and hosted google analytics alternative