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
– Duke79, Commented Dec 9, 2018 at 6:29
Just for my understanding. Can you explain how you create users ? — caliph
– caliph, Commented May 18, 2020 at 17:49

Deo Leung · Accepted Answer · 2017-12-06 00:24:23Z

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 6, 2017 at 0:24

Deo Leung

9389 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MBT Over a year ago

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 Over a year ago

cast('"'+other_table.c.other_column+'"', JSONB) did the trick for me.

andilabs · Accepted Answer · 2022-03-11 15:24:00Z

2

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, 2022 at 15:24

andilabs

23.5k14 gold badges126 silver badges159 bronze badges

answered Feb 8, 2017 at 9:17

Kanchan Srivastava

3993 silver badges7 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 11:46:47Z

1

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))

edited May 23, 2017 at 11:46

CommunityBot

11 silver badge

answered Mar 9, 2016 at 20:48

univerio

20.7k3 gold badges73 silver badges73 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related