Django / PostgresQL jsonb (JSONField) - convert select and update into one query

Question

Versions: Django 1.10 and Postgres 9.6

I'm trying to modify a nested JSONField's key in place without a roundtrip to Python. Reason is to avoid race conditions and multiple queries overwriting the same field with different update.

I tried to chain the methods in the hope that Django would make a single query but it's being logged as two:

Original field value (demo only, real data is more complex):

from exampleapp.models import AdhocTask

record = AdhocTask.objects.get(id=1)
print(record.log)
> {'demo_key': 'original'}

Query:

from django.db.models import F
from django.db.models.expressions import RawSQL

(AdhocTask.objects.filter(id=25)
                  .annotate(temp=RawSQL(
                      # `jsonb_set` gets current json value of `log` field,
                      # take a the nominated key ("demo key" in this example)
                      # and replaces the value with the json provided ("new value")
                      # Raw sql is wrapped in triple quotes to avoid escaping each quote                           
                      """jsonb_set(log, '{"demo_key"}','"new value"', false)""",[]))
                  # Finally, get the temp field and overwrite the original JSONField
                  .update(log=F('temp’))
)

Query history (shows this as two separate queries):

from django.db import connection
print(connection.queries)

> {'sql': 'SELECT "exampleapp_adhoctask"."id", "exampleapp_adhoctask"."description", "exampleapp_adhoctask"."log" FROM "exampleapp_adhoctask" WHERE "exampleapp_adhoctask"."id" = 1', 'time': '0.001'},
> {'sql': 'UPDATE "exampleapp_adhoctask" SET "log" = (jsonb_set(log, \'{"demo_key"}\',\'"new value"\', false)) WHERE "exampleapp_adhoctask"."id" = 1', 'time': '0.001'}]

Answer 1

It would be much nicer without RawSQL.

Here's how to do it:

from django.db.models.expressions import Func


class ReplaceValue(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,
        )


AdhocTask.objects.filter(id=25) \
    .update(log=ReplaceValue(
        'log',
        keyname='demo_key',
        new_value='another value',
        create_missing=False,
    )

ReplaceValue.template is the same as your raw SQL statement, just parametrized.

(jsonb_set(log, \'{"demo_key"}\',\'"another value"\', false)) from your query is now jsonb_set("exampleapp.adhoctask"."log", \'{"demo_key"}\',\'"another value"\', false). The parentheses are gone (you can get them back by adding it to the template) and log is referenced in a different way.

Anyone interested in more details regarding jsonb_set should have a look at table 9-45 in postgres' documentation: https://www.postgresql.org/docs/9.6/static/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE