it looks like Postgresql 9.5+ has a new function jsonb_set that does this, this can be used with func. If you aren't on pg9.5 you might have to update the whole value.
full POC below from sqlalchemy import Column from sqlalchemy import create_engine from sqlalchemy import func from sqlalchemy import Integer from sqlalchemy import type_coerce from sqlalchemy.dialects.postgresql import JSONB from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import Session Base = declarative_base() class A(Base): __tablename__ = "a" id = Column(Integer, primary_key=True) data = Column(JSONB) e = create_engine("postgresql://scott:tiger@localhost/test", echo=True) Base.metadata.drop_all(e) Base.metadata.create_all(e) s = Session(e) data = { "preference": { "android": {"software_update": "true", "system_maintenance": "true"}, "ios": {"software_update": "true", "system_maintenance": "true"}, } } a1 = A(data=data) s.add(a1) s.commit() s.query(A).update( { A.data: func.jsonb_set( A.data, "{preference,android}", type_coerce( {"software_update": "false", "system_maintenance": "false"}, JSONB, ), ) }, synchronize_session="fetch", ) assert a1.data["preference"]["android"] == { "software_update": "false", "system_maintenance": "false", } On Thu, Jul 4, 2019, at 7:44 AM, NanthaKumar Loganathan wrote: > Hi , I have below jsonb blob which i wanted to update subset value. > > { > "preference": { > "android": { > "software_update": "true", > "system_maintenance": "true" > }, > "ios": { > "software_update": "true", > "system_maintenance": "true" > } > } > } > > how to i update only "android" blob which is inside "preference" > can someone help here with sqlalchemy query? > the following snippet doesnt worked for me > > app_name = 'android' > pref = {"software_update": "false", "system_maintenance": "false"} > qu = session.query(SystemSubscription).filter(SystemSubscription.username == > 't...@gmail.com').update( > {SystemSubscription.preference: cast( > cast(SystemSubscription.preference[app_name], > JSONB).concat(func.jsonb_build_object(app_name, json.dumps(pref))), > JSON)}, synchronize_session="fetch") > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/53f0dc96-7884-42b1-890b-26e20193708a%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/53f0dc96-7884-42b1-890b-26e20193708a%40googlegroups.com?utm_medium=email&utm_source=footer>. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/4c442799-ad48-4195-9b0c-53fe72ce3d05%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.