How to update jsonb blob subset values using sqlalchemy orm ?

[NanthaKumar Loganathan]

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').update(
    {SystemSubscription.preference: cast(
        cast(SystemSubscription.preference[app_name], JSONB).concat(func.jsonb_build_object(app_name, json.dumps(pref))),
        JSON)}, synchronize_session="fetch")

[Mike Bayer]

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",

--

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+...@googlegroups.com.

To post to this group, send email to sqlal...@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.

For more options, visit https://groups.google.com/d/optout.

[Cameron Simpson]

On 04Jul2019 10:12, Mike Bayer <mik...@zzzcomputing.com> 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

[... nice code which works with modern PostgreSQL ...]

And if you don't have direct SQL support and instead need to modify the
JSON blob yourself ...

I should point out first that SQLAlchemy has an "index value as tuple"
mode which can dig into JSON columns along the lines of:

row.the_json_column_name[('a','b','field1')]

Or alternatively you could consider my cs.sqlalchemy_utils PyPI package,
which has a small facility for this:

An @json_column decorator function and some associated get_json_field()
and set_json_field() functions for working against JSON blobs.

The functions take a JSON blob and a notional field_name like "a.b.c"
where that would reference:

{"a": {"b": {"c": 9, "d": 10}}}

The @json_column decorator decorates a declariative class with a
ficitious column associated with a "field" within an existing JSON
column. Eg:

@json_column("field1", "a.b.field1", "the_json_column_name")
class SomethingRow(Base):
...
the_json_column_name = Column(JSON, comment='JSON encoded addtional data')
...

where Base is your ORM declarative base class and SomethingRow is your
table row class. This makes a ".field1" property on each instance which
fetches from the json column or modifies the json column when assigned.
It doesn't make any SQL support - it just modifies the session entity
and you let the ORM update the database on transaction commit etc.

So that you can go:

# fetch and print the value from inside row.the_json_column_name
print(row.field1)

# modify row.the_json_column_name["a"]["b"]["field1"]
row.field1 = 12

It isn't heavily tested yet, but it's been working for me.

Cheers,
Cameron Simpson <c...@cskk.id.au>

[NanthaKumar Loganathan]

thanks it worked for me

To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.