如何使用 sqlalchemy 查询和更新 postgres jsonb 类型的字段

发表于 Disqus：

最近跟朋友在搞一个微信小程序，数据库选了 postgresql，用 jsonb 字段存了一些数据。在查询和更新 jsonb 字段的时候，因为与 mysql 还是有很大的不同，直接按之前用 mysql 的方式来写报了一堆的错。。查了一些资料，这里整理一下。

按自己尝试的过程一步步写了很长，只想了解最终结果的同学，可以直接跳到文章最后。

准备工作。需要事先安装 sqlalchemy 和 pg8000：

新建一个 test 表，如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
import json
from sqlalchemy import Column, Integer, String, create_engine, cast, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.dialects import postgresql
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.orm.attributes import flag_modified

DB_URI = "postgresql+pg8000://user:passwd@127.0.0.1:5432/dbname"

engine = create_engine(DB_URI, echo=False)
Base = declarative_base()
Session = sessionmaker(bind=engine)


class Test(Base):
    __tablename__ = 'test'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String)
    data = Column(JSONB)


Base.metadata.create_all(bind=engine)

然后插入一条测试数据：

1
2
3
4
5
6
7
session = Session()

data = {'a': {'b': {'c': 1, 'd': 2}, 'e': 3}}
obj = Test(name="a", data=data)

session.add(obj)
session.commit()

psql 中 select 一下，看下结果：

1
select * from test;
id name data
1 a {“a”: {“b”: {“c”: 1, “d”: 2}, “e”: 3}}

现在正题开始了，我们的目标有两个：

  1. 根据匹配 data 字段中 data['a']['b']['c'] 的值，查询 data['a']['b']['d'] 的值
  2. 根据匹配 data 字段中 data['a']['b']['c'] 的值，更新 data['e'] 的值

为了简便显示生成的 sql，我们先定义一个函数，以便打印 sql：

1
2
3
4
def show_sql(query):
     print(query.statement.compile(
         compile_kwargs={"literal_binds": True},
         dialect=postgresql.dialect(paramstyle="named")))

尝试阶段

先看查询：

一般我们在用 sqlalchemy 查询 mysql 的时候，会这样来写：

1
res = session.query(Test).filter(Test.id == 1)

那在 postgres 时也这样写会怎样呢：

1
2
3
res = session.query(Test).filter(Test.id == 1)
show_sql(res)
print(res.first())

输出：

1
2
3
4
5
SELECT test.id, test.name, test.data
FROM test
WHERE test.id = 1

<test.Test object at 0x10bf172b0>

可以看到是能够正常取到结果的，那获取整个 jsonb 字段呢：

1
2
3
res = session.query(Test.data).filter(Test.id == 1)
show_sql(res)
print(res.first())

输出：

1
2
3
4
5
SELECT test.data
FROM test
WHERE test.id = 1

({'a': {'b': {'c': 1, 'd': 2}, 'e': 3}},)

也是 OK 的。

那查询 data 字段里 c 的值呢？想当然地：

1
2
3
res = session.query(Test.data['a']['b']['c']).filter(Test.id == 1)
show_sql(res)
print(res.first())

输出：

1
2
3
4
5
SELECT ((test.data -> 'a') -> 'b') -> 'c' AS anon_1
FROM test
WHERE test.id = 1

(1,)

postgres 也没问题！

目标1

好了，现在尝试目标1：

1
2
3
res = session.query(Test.data['a']['b']['d']).filter(Test.data['a']['b']['c'] == 1)
show_sql(res)
print(res.first())

报错了！？？

1
2
3
4
5
SELECT ((test.data -> 'a') -> 'b') -> 'd' AS anon_1
FROM test
WHERE (((test.data -> 'a') -> 'b') -> 'c') = 1

'operator does not exist: jsonb = smallint, No operator matches the given name and argument type(s). You might need to add explicit type casts.'

说 jsonb 和 smallint 不能直接比较，需要明确指定类型转换。

查了一下，说需要把最后的数字 1，改为字符串 '1'。

再试：

1
2
3
res = session.query(Test.data['a']['b']['d']).filter(Test.data['a']['b']['c'] == '1')
show_sql(res)
print(res.first())
1
2
3
4
5
SELECT ((test.data -> 'a') -> 'b') -> 'd' AS anon_1
FROM test
WHERE (((test.data -> 'a') -> 'b') -> 'c') = '1'

(2,)

OK了。。为什么换成字符串就可以了呢？先不管，先功能实现了再说，看目标2。

目标2

先看 mysql 里边我们会怎么写：

1
res = session.query(Test).filter(Test.id == 1).update({Test.name: 'b'}, synchronize_session=False)

非 jsonb 字段类比着跑：

1
UPDATE test SET name='b' WHERE test.id = 1

没问题，更新成功了。

jsonb 呢？

我们类比着上边查询的来写个：

1
res = session.query(Test).filter(Test.data['a']['b']['c'] == '1').update({Test.data['a']['b']['e']: '4'}, synchronize_session=False)

不出意料地报错了。。生成的 sql：

1
UPDATE test SET ((data -> '"4"') -> 'a') -> 'b'='e' WHERE (((test.data -> 'a') -> 'b') -> 'c') = '1'

后边的 where 子名还蛮正常的，跟上边查询的一样，但 data -> '"4"' 是什么鬼。。。也正是这里报的错，语法错误。

stackoverflow 怼起来。

然后翻到了 这个 ，里边小哥有和我一样的疑问，高票回答说了要用 jsonb_set，然后查了下 jsonb_set 怎么用，写出了下边最终的 update 语句：

1
session.query(Test).filter(Test.data['a']['b']['c'] == json.dumps(1)).update({Test.data: func.jsonb_set(Test.data, "{e}", json.dumps(4))}, synchronize_session='fetch')

生成的 sql：

1
UPDATE test SET data=jsonb_set(test.data, '{e}', '4') WHERE (((test.data -> 'a') -> 'b') -> 'c') = '1'

执行没有报错。

1
2
res = session.query(Test.data).filter(Test.id == 1).first()
print(res)
1
({'a': {'b': {'c': 1, 'd': 2}, 'e': 3}, 'e': 4})

但这时候在 psql 里查询是还未更改的状态，需要 commit 一下。

1
session.commit()

然后就可以查询出来了。

最终结论

操作 postgresql json/jsonb 相关字段的，需要使用 postgresql 提供的各种 json/jsonb 操作方法 ，相比较的对象也需要转成 json/jsonb 。

附上这次我们查询和更新的最终例子：

查询：

1
res = session.query(Test.data['a']['b']['d']).filter(Test.data['a']['b']['c'] == json.dumps(1)).first()

更新，记得最后要 commit ：

1
session.query(Test).filter(Test.data['a']['b']['c'] == json.dumps(1)).update({Test.data: func.jsonb_set(Test.data, "{e}", json.dumps(4))}, synchronize_session='fetch')