Batch-remove (UPDATE) keys in JSONB column

[Anders Steinlein]

Hi,

In PostgreSQL, I have a JSONB column named custom with for instance this data: {"firmanavn": "Eliksir", "value": 123}. I can do a query such as this to remove the "firmanavn" key from the column:

UPDATE contacts SET custom = custom - 'firmanavn' WHERE custom ? 'firmanavn';

I have not found a way to batch-update JSON columns such as this in SQLAlchemy. The column is defined as: custom = Column(MutableDict.as_mutable(JSONB))

Is there a way? Looping over filtered models and deleting the keys per model obviously doesn't scale very well...

Thanks,

Anders Steinlein

Gründer av MailMojo hos Eliksir AS

mob: +47 926 13 069

tlf: 21 42 30 30

web: mailmojo.no | e5r.no

[Mike Bayer]

On Mon, Jan 25, 2021, at 4:29 PM, Anders Steinlein wrote:

Hi,

In PostgreSQL, I have a JSONB column named custom with for instance this data: {"firmanavn": "Eliksir", "value": 123}. I can do a query such as this to remove the "firmanavn" key from the column:

UPDATE contacts SET custom = custom - 'firmanavn' WHERE custom ? 'firmanavn';

The minus operator "-" will render if you use the Python minus symbol on the column.  The Postgresql JSONB has_key() operator will do the ? part (mentioned at https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSONB).   To do an UPDATE against JSON means you want to render a SQL expression in the SET portion of the UPDATE statement.  Depending on if you're using Core or ORM the docs you'd be finding that illustrate this for SQLAlchemy 1.3 would be in the Core Tutorial under "updates" or in the ORM the first example at https://docs.sqlalchemy.org/en/13/orm/query.html?highlight=query%20update#sqlalchemy.orm.query.Query.update .

in this case an ORM update would look like:

s.query(A).filter(A.custom.has_key("firmanavn")).update(

    {A.custom: A.custom - "firmanavn"}, synchronize_session=False

)

A Core update like:

stmt = table.update().where(table.c.custom.has_key("firmavn")).values({table.c.custom: table.c.custom - "firmavn"})

hope this helps!

I have not found a way to batch-update JSON columns such as this in SQLAlchemy. The column is defined as: custom = Column(MutableDict.as_mutable(JSONB))

Is there a way? Looping over filtered models and deleting the keys per model obviously doesn't scale very well...

Thanks,

Anders Steinlein

Gründer av MailMojo hos Eliksir AS

mob: +47 926 13 069

tlf: 21 42 30 30

web: mailmojo.no | e5r.no

--

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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAC35HNkv18rsuzUd4p%3DXXY%3DW01PtG6egjCnLJgXrtacRCk9WuQ%40mail.gmail.com.