SqlAlchemy(Flask + Postgres):如何仅更新json字段的特定属性? [英] SqlAlchemy(Flask+Postgres) : How to update only a specific attribute of a json field?

问题描述

我有一个表，其中的一列声明为json，我需要通过将键值添加到json值来更新记录.

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.

模型

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)

控制器

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

假定views中的当前值为{'1001':1} 如果必须将views更新为-

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!

推荐答案

如果使用的是JSONB，则可以使用jsonb_set函数

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

如果您要从其他列插入

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

这篇关于SqlAlchemy(Flask + Postgres):如何仅更新json字段的特定属性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！