I'm trying to update values inside a jsonb field in a postgres database using SQLAlchemy.

Have been trying to use func.jsonb_set but I can't quite work out how to implement it.

With a table (test) like below, I'd aimng for a generic way of adding / editing json data.

The following works in postgres for a simple update.

UPDATE "test" SET "data"=jsonb_set("data"::jsonb, '{age}', '45')
WHERE "data"::json->>'name'='dave';

I'm able to use update to update a single value like this:

testobj_res.update(
    {
        TestObj.data: cast(
            cast(TestObj.data, JSONB).concat(func.jsonb_build_object("age", 45)),
            JSON,
        )
    }
)

session.commit()

I'd like to be able to pass an update of multiple fields of e.g. {"name": "barry", "age": 45, "height": 150}.

I've tried using func.jsonb_set with the idea of adding a more complicated json structure instead of ('age', 45)

testobj_res.first().data = func.jsonb_set(
    TestObj.data.cast(JSONB),
    ("age", 45),
    cast(TestObj.data, JSONB))

session.commit()

but am getting:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) function jsonb_set(jsonb, record, jsonb) does not exist
LINE 1: UPDATE public.test SET data=jsonb_set(CAST(public.test.data ...
                                    ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Full example code:

import os
from sqlalchemy.dialects.postgresql import JSON, JSONB
from sqlalchemy import func, cast
import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import urllib
from dotenv import load_dotenv

load_dotenv()

user = urllib.parse.quote_plus(os.environ.get("DB_USER"))
passwd = urllib.parse.quote_plus(os.environ.get("DB_PW"))

DB_URL = "postgresql://{}:{}@{}:{}/{}".format(
    user,
    passwd,
    os.environ.get("DB_HOST"),
    os.environ.get("DB_PORT"),
    os.environ.get("DB_NAME"),
)

engine = sa.create_engine(DB_URL)
Session = sessionmaker(bind=engine, autoflush=True)

session = Session()

Base = declarative_base()


class TestObj(Base):
    __tablename__ = "test"
    __table_args__ = {"autoload_with": engine, "schema": "public"}


testobj_res = session.query(TestObj).filter(TestObj.name == "dave")

testobj_res.first().data = func.jsonb_set(
    TestObj.data.cast(JSONB),
    ("age", 45),
    cast(TestObj.data, JSONB))

session.commit()

CodePudding user response：

This code

testobj_res.first().data = func.jsonb_set(
    TestObj.data.cast(JSONB),
    ("age", 45),
    cast(TestObj.data, JSONB))

is passing a JSONB object, and record and another JSONB object to jsonb_set but the function expects a JSONB object, a JSONPath string and the new value for the path as JSONB^*.

Unpacking the ('age', 45) tuple and removing the final JSONB will produce the desired result.

with Session.begin() as s:
    testobj = s.scalar(sa.select(TestObj).limit(1))
    testobj.data = sa.func.jsonb_set(
        sa.cast(testobj.data, postgresql.JSONB),
        '{age}',
        sa.cast(42, postgresql.JSONB)
    )

^* There is also an optional boolean create-if-not-exists argument, which we can ignore.