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!

Answer 1

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

Answer 2

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

Answer 3

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