thanks it worked for me
On Thursday, July 4, 2019 at 7:42:45 PM UTC+5:30, Mike Bayer wrote:
>
> 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 ==
> 'te...@gmail.com <javascript:>').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 sqlal...@googlegroups.com <javascript:>.
> To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> 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/744aab0a-5112-4cf1-92a5-945e76457e49%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
