SqlAlchemy(Flask+Postgres) : How to update only a specific attribute of a json field?

Question

I have a table that has one column declared as a json and I need to update records by adding a key-value to the json value.

model

class User(db.Model):

    __tablename__ = 'users'

    loginId         = db.Column(db.String(128),  nullable=False, primary_key=True)
    _password       = db.Column(db.String(128),  nullable=True)
    views           = db.Column(JSON,         nullable=True)

controller

@mod_event.route('/view', methods=['POST'])
def view():
    try:
        params = request.json
        loginId = params['dream']['loginId']
        users.update().\
            where(users.c.loginId==loginId).\
            values(views=<query>))

Assume current value in views is {'1001' : 1} What should be the query if views has to be updated to -

{'1001' : 2}
{'1001' : 1, '1002' : 1}

if i don't want to query the value first, change and update back.

I'm having a hard time figuring how to do this in a single query, please help, thanks!

Updating the entire json field worked for me. Retrieved the json, modified it and saved it back. — Duke79, Dec 09 '18 at 06:29
Just for my understanding. Can you explain how you create `users` ? — caliph, May 18 '20 at 17:49

score 6 · Answer 1 · answered Dec 06 '17 at 00:24

6

if you are using JSONB, you can use the jsonb_set function

(table
 .update()
 .values(views=func.jsonb_set(table.c.views,
                              '{%s}' % '1002',
                              1))
 .where(...))

if you are inserting from other column

(table
 .update()
 .values(views=func.jsonb_set(table.c.views,
                              '{%s}' % '1002',
                             other_table.c.other_column.cast(String).cast(JSONB)))
 .where(...))

answered Dec 06 '17 at 00:24

Deo Leung

848
9
9

Thanks for this helpful answer! I'm trying to update the jsonb with another column however the cast into JSONB as you described does not work properly for strings. I always get a `InvalidTextRepresentation` error. I guess because the need to be multiple quotes `'"jsonb-value"'`. Any idea how to cast the value correctly? Thanks! – MBT Dec 29 '21 at 12:14
`cast('"'+other_table.c.other_column+'"', JSONB)` did the trick for me. – MBT Dec 29 '21 at 12:22

score 1 · Answer 2 · edited May 23 '17 at 11:46

Refer to this answer for how to do it in SQL.

To do the same thing in Python (with PostgreSQL 9.5):

update().where(...)\
    .values(views=cast(cast(u.c.views, JSONB)
                       .concat(func.jsonb_build_object('1002', 1)), JSON)

For PostgreSQL 9.3+, you'll have to create the function in PostgreSQL first, then:

update().where(...)\
    .values(views=func.json_object_set_key(u.c.views, '1002', 1))

score 1 · Answer 3 · edited Mar 11 '22 at 15:24

1

This way we can update new key-value pair in the views field.

from sqlalchemy import cast,func, JSON
from sqlalchemy.dialects.postgresql import JSONB

users.query.filter(users.c.loginId==loginId).update({users.data: cast(
        cast(users.data, JSONB).concat(func.jsonb_build_object('1002', 1)), JSON)}, synchronize_session="fetch")

edited Mar 11 '22 at 15:24

andilabs

22,159
14
114
151

answered Feb 08 '17 at 09:17

Kanchan Srivastava

339
3
6

SqlAlchemy(Flask+Postgres) : How to update only a specific attribute of a json field?

3 Answers3

Linked