SQLAlchemy adding / updating jsonb field in postgres

Question

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.

id	data	name
1	{"age": 44, "name": "barry", children": ["baz", "jim"]}	barry
2	{"age": 47, "name": "dave", "children": ["jeff", "jane"]}	dave

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()

snakecharmerb · Accepted Answer · 2023-01-28 17:40:30Z

1

This code

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

is passing a JSONB object, a 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() as s, s.begin():
    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.

edited Jan 28, 2023 at 17:40

answered Apr 23, 2022 at 9:12

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Konstantin Zavadsky · Accepted Answer · 2023-05-25 07:31:39Z

1

I had and issue with second parameter, it casted to varchar and didn't work. for sqlalchemy2.0 this code works

func.jsonb_set(
        Model.jsonb_field,
        text("'{%s}'" % key),
        cast("value", JSONB),
    )

answered May 25, 2023 at 7:31

Konstantin Zavadsky

111 silver badge4 bronze badges

Collectives™ on Stack Overflow

SQLAlchemy adding / updating jsonb field in postgres

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related