How to Django ORM update() multiple values nested inside a JSONField?

Question

I have a Django JSONField on PostgreSQL which contains a dictionary, and I would like to use the queryset.update() to bulk update several keys with values. I know how to do it for one value in a JSONField (and for multiple fields in general):

from django.db.models import Func, Value, CharField, FloatField, F, IntegerField

class JSONBSet(Func):
    """
    Update the value of a JSONField for a specific key.
    """
    function = 'JSONB_SET'
    arity = 4
    output_field = CharField()

    def __init__(self, field, path, value, create: bool = True):
        path = Value('{{{0}}}'.format(','.join(path)))
        create = Value(create)
        super().__init__(field, path, value, create)

This seems to work - with some gaps as per my other question - like this:

# This example sets the 'nestedkey' to numeric 199.
queryset.update(inputs=JSONBSet('inputs', ['nestedkey_1'], Value("199"), False))

But if I now wanted to update a second nestedkey_2 inside the same inputs, I obviously cannot use the inputs argument twice like this:

queryset.update(inputs=JSONBSet(...'nestedkey_1'...), inputs=JSONBSet(...'nestedkey_2'...)

Is there a way to do this?

Does this answer your question? [How to 'bulk update' with Django?](https://stackoverflow.com/questions/12661253/how-to-bulk-update-with-django) — Ankit Tiwari, Aug 02 '21 at 11:48
@AnkitTiwari No. Normally, update() is called with keyword arguments where each keyword names a separate field to be updated. Here, the problem here is that the same argument name must be used. — Shaheed Haque, Aug 02 '21 at 11:50

Shaheed Haque · Answer 1 · 2021-08-04T23:33:20.350

2

I finally figured it out. Postgres has the jsonb operator "||" which can be used with Django like this:

from django.db.models import Func
from django.db.models.functions import JSONObject

class JSONBUpdate(Func):
    def __init__(self, field, update):
        super().__init__(update)
        self.template = "{} || %(expressions)s".format(field)
#
# Form a dict with the key-value pair we want to overwrite.
#
tmp = JSONObject(**{'inputs-0-value': Value("199999"), 'inputs-1-value': Value("123456")})
#
# Overwrite 'inputs-0-value' and 'inputs-1-value' using the dict.
#
queryset.update(inputs=JSONBUpdate('inputs', tmp))

Simples!

FWIW, it seems that other databases have a more-or-less standardised function with a name like jsonb_update which I suspect does something similar to the "||".

edited Aug 04 '21 at 23:33

answered Aug 04 '21 at 23:16

Shaheed Haque

644
5
14

I added a rough draft a bit ago, it may support multiple database backends, i only tested sqlite, and postgres, it can be found here : https://github.com/django/django/pull/15422 just adding that here in case anyone else might find it useful – jmunsch Jun 30 '22 at 19:32
1

@jmunsch Indeed, I referenced https://code.djangoproject.com/ticket/32519 when I was researching the problem. – Shaheed Haque Jul 04 '22 at 11:13

How to Django ORM update() multiple values nested inside a JSONField?

1 Answers1