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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAC35HNkv18rsuzUd4p%3DXXY%3DW01PtG6egjCnLJgXrtacRCk9WuQ%40mail.gmail.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/CAC35HNkv18rsuzUd4p%3DXXY%3DW01PtG6egjCnLJgXrtacRCk9WuQ%40mail.gmail.com?utm_medium=email&utm_source=footer>.
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/5af09e37-eef7-4703-bba5-ee2a671a8cad%40www.fastmail.com.
