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.