SqlAlchemy(Flask + Postgres):如何仅更新json字段的特定属性?

本文介绍了SqlAlchemy(Flask + Postgres):如何仅更新json字段的特定属性?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个表，其中的一列声明为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(...))