Django/PostgresQL jsonb(JSONField)-将选择和更新转换为一个查询 [英] Django / PostgresQL jsonb (JSONField) - convert select and update into one query

问题描述

版本:Django 1.10和Postgres 9.6

Versions: Django 1.10 and Postgres 9.6

我试图在不修改Python的情况下就地修改嵌套JSONField的键.原因是为了避免竞争条件和多个查询使用不同的更新覆盖相同的字段.

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.

我试图将方法链接起来，希望Django可以进行单个查询，但它会被记录为两个:

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'}

查询:

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'}]

推荐答案

橡胶鸭子调试处于最佳状态-在编写问题时，我已经实现了解决方案.在这里留下答案，希望将来能帮助到某人:

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:

看着查询，我意识到RawSQL实际上被推迟到查询两个，所以我要做的就是将RawSQL作为子查询存储，以便以后执行.

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.

解决方案:

完全跳过annotate步骤，并直接在.update()调用中使用RawSQL表达式.允许您动态更新数据库服务器上的PostgresQL jsonb子项，而不会覆盖整个字段:

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'}

这篇关于Django/PostgresQL jsonb(JSONField)-将选择和更新转换为一个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！