Join the Stack Overflow Community
Stack Overflow is a community of 6.7 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up

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!

share|improve this question

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))
share|improve this answer

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

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")
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.