On 04Jul2019 10:12, Mike Bayer <mike...@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>
--
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/20190705003416.GA9490%40cskk.homeip.net.
For more options, visit https://groups.google.com/d/optout.
