[QUESTION] Update json field

[Master-Y0da]

I have a json field structured like this:

[{"fecha": "31-10-2020 16:00", "doctor": "5d2a1c29-21b9-4a02-846c-171ba65ba0ea", "paciente": {"code": "a4723719-fbd4-4d16-9470-97ea93c3c5dd", "conv": "sebastian muñoz"}}]

Is there a way of update a single attribute of the json field ?...I mean without making a query and extracting the attributes.

return await some_pydantic.from_queryset_single(SomeModel.get(id=id)) <---Not this

something like this..obviously field and attr are examples
return await some_pydantic.from_queryset_single(SomeModel.update(id=id).field(jsonField).attr(fecha='New Date'))

[long2ice]

see https://tortoise-orm.readthedocs.io/en/develop/functions.html#custom-functions

[Master-Y0da]

I'm getting this error...using postgres 12.2 (I modify the JsonSet function described on docs, for get instead update)

Traceback (most recent call last): File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\uvicorn\protocols\http\h11_impl.py", line 388, in run_asgi result = await app(self.scope, self.receive, self.send) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\uvicorn\middleware\proxy_headers.py", line 45, in __call__ return await self.app(scope, receive, send) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\fastapi\applications.py", line 179, in __call__ await super().__call__(scope, receive, send) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\starlette\applications.py", line 111, in __call__ await self.middleware_stack(scope, receive, send) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\starlette\middleware\errors.py", line 181, in __call__ raise exc from None File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\starlette\middleware\errors.py", line 159, in __call__ await self.app(scope, receive, _send) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\starlette\middleware\cors.py", line 86, in __call__ await self.simple_response(scope, receive, send, request_headers=headers) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\starlette\middleware\cors.py", line 142, in simple_response await self.app(scope, receive, send) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\starlette\exceptions.py", line 82, in __call__ raise exc from None File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\starlette\exceptions.py", line 71, in __call__ await self.app(scope, receive, sender) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\starlette\routing.py", line 566, in __call__ await route.handle(scope, receive, send) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\starlette\routing.py", line 227, in handle await self.app(scope, receive, send) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\starlette\routing.py", line 41, in app response = await func(request) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\fastapi\routing.py", line 182, in app raw_response = await run_endpoint_function( File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\fastapi\routing.py", line 133, in run_endpoint_function return await dependant.call(**values) File "C:\Users\el_sh\OneDrive\Escritorio\codes\admin_final\admin\app\routes\routes.py", line 127, in get_agenda await Agenda.get(fechas_agendadas=JsonSet(F("fechas_agendadas"), f"$.{año}")) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\tortoise\queryset.py", line 803, in _execute instance_list = await self._db.executor_class( File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\tortoise\backends\base\executor.py", line 123, in execute_select _, raw_results = await self.db.execute_query(query.get_sql()) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\tortoise\backends\asyncpg\client.py", line 38, in translate_exceptions_ raise OperationalError(exc) tortoise.exceptions.OperationalError: no existe la función json_set(jsonb, unknown) HINT: Ninguna función coincide en el nombre y tipos de argumentos. Puede ser necesario agregar conversión explícita de tipos. ERROR:uvicorn.error:Exception in ASGI application Traceback (most recent call last): File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\uvicorn\protocols\http\h11_impl.py", line 388, in run_asgi result = await app(self.scope, self.receive, self.send) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\uvicorn\middleware\proxy_headers.py", line 45, in __call__ return await self.app(scope, receive, send) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\fastapi\applications.py", line 179, in __call__ await super().__call__(scope, receive, send) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\starlette\applications.py", line 111, in __call__ await self.middleware_stack(scope, receive, send) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\starlette\middleware\errors.py", line 181, in __call__ raise exc from None File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\starlette\middleware\errors.py", line 159, in __call__ await self.app(scope, receive, _send) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\starlette\middleware\cors.py", line 86, in __call__ await self.simple_response(scope, receive, send, request_headers=headers) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\starlette\middleware\cors.py", line 142, in simple_response await self.app(scope, receive, send) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\starlette\exceptions.py", line 82, in __call__ raise exc from None File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\starlette\exceptions.py", line 71, in __call__ await self.app(scope, receive, sender) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\starlette\routing.py", line 566, in __call__ await route.handle(scope, receive, send) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\starlette\routing.py", line 227, in handle await self.app(scope, receive, send) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\starlette\routing.py", line 41, in app response = await func(request) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\fastapi\routing.py", line 182, in app raw_response = await run_endpoint_function( File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\fastapi\routing.py", line 133, in run_endpoint_function return await dependant.call(**values) File "C:\Users\el_sh\OneDrive\Escritorio\codes\admin_final\admin\app\routes\routes.py", line 127, in get_agenda await Agenda.get(fechas_agendadas=JsonSet(F("fechas_agendadas"), f"$.{año}")) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\tortoise\queryset.py", line 803, in _execute instance_list = await self._db.executor_class( File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\tortoise\backends\base\executor.py", line 123, in execute_select _, raw_results = await self.db.execute_query(query.get_sql()) File "C:\Users\el_sh\AppData\Local\pypoetry\Cache\virtualenvs\app-uwxeHXiN-py3.8\lib\site-packages\tortoise\backends\asyncpg\client.py", line 38, in translate_exceptions_ raise OperationalError(exc) tortoise.exceptions.OperationalError: function json_set(jsonb, unknown) does not exist

[long2ice]

postgres has not json_set but jsonb_set, and usage maybe different, please see official docs

[Master-Y0da]

Ok...I get it.
But still is really confusing implement this in tortoise...Could you please add an example ?
https://www.postgresql.org/docs/12/functions-json.html

Thank you for your time and predisposition.

[long2ice]

OK, this is postgres example:

from tortoise.expressions import F
from pypika.terms import Function

class JsonbSet(Function):
    def __init__(self, field: F, path: str, value: Any, create_if_missing: bool = False):
        super().__init__("jsonb_set", field, path, value, create_if_missing)

json = await JSONFields.create(data_default={"a": 1})
json.data_default = JsonbSet(F("data_default"), "{a}", '3') # in fact '3' is integer 
await json.save()

And the sql is

UPDATE "jsonfields" SET "data_default"=jsonb_set("data_default", '{a}', '3', false) WHERE "id" = 1