Phil Sheard Phil Sheard - 3 years ago 167
JSON Question

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

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 Source

Rubber duck debugging at its best - in writing the question, I've realised the solution. Leaving the answer here in hope of helping someone in future:

Looking at the queries, I realised that the RawSQL was actually being deferred until query two, so all I was doing was storing the RawSQL as a subquery for later execution.

Solution:

Skip the annotate step altogether and use the RawSQL expression straight into the .update() call. Allows you to dynamically update PostgresQL jsonb sub-keys on the database server without overwriting the whole field:

(AdhocTask.objects.filter(id=25)
    .update(log=RawSQL(
                """jsonb_set(log, '{"demo_key"}','"another value"', false)""",[])
                )
)
> 1  # Success

print(connection.queries)
> {'sql': 'UPDATE "exampleapp_adhoctask" SET "log" = (jsonb_set(log, \'{"demo_key"}\',\'"another value"\', false)) WHERE "exampleapp_adhoctask"."id" = 1', 'time': '0.001'}]

print(AdhocTask.objects.get(id=1).log)
> {'demo_key': 'another value'}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download