4

I have a Django JSONField on PostgreSQL which contains a dictionary, and I would like to use the queryset.update() to bulk update one (eventually, several) keys with a numeric (eventually, computed) values. I see there is discussion about adding better support for this and an old extension, but for now it seems I need a DIY approach. Relying on those references, this is what I came up with:

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 fine for non-computed "numeric string" values like this:

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

and for carefully quoted strings:

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

But it does not work for a number:

queryset.update(inputs=JSONBSet('inputs', ['nestedkey'], Value(1), False))

{ProgrammingError}function jsonb_set(jsonb, unknown, integer, boolean) does not exist
LINE 1: UPDATE "paiyroll_payitem" SET "inputs" = JSONB_SET("paiyroll...
                                                 ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

As per the HINT, I tried an explicit cast Value(1, IntegerField()). I'm not sure where I am going wrong.

Shaheed Haque
  • 644
  • 5
  • 14

0 Answers0