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 ==
> '[email protected]').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 [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected].
To post to this group, send email to [email protected].
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.
