Postgres对jsonb字段执行regex查询

Question

我的Postgres数据库中有一列存储jsonb类型值。其中一些值是原始字符串(而不是列表或字典(。我希望能够在此列上执行正则表达式搜索，例如

select * from database where jsonb_column::text ~ regex_expression.

问题是，对于已经是字符串的值，从jsonb转换为文本会在值的开头和结尾添加额外的转义双引号。我不希望这些包含在regex查询中。我理解Postgres为什么这么做，但如果假设存储在jsonb字段中的所有值都是jsonb字符串，那么有解决办法吗？我知道你可以使用->gt；从jsonb字典中获取值，但无法单独为jsonb字符串找到解决方案。

一旦我弄清楚如何在普通的Postgres中进行这个查询，我想把它翻译成Peewee。然而，即使只是最初的查询，任何和所有的帮助都将不胜感激！

Answer 1

只需将json转换为文本。这里有一个例子：

class Reg(Model):
key = CharField()
data = BinaryJSONField()
class Meta:
database = db
for i in range(10):
Reg.create(key='k%s' % i, data={'k%s' % i: 'v%s' % i})
# Find the row that contains the json string "k1": "v1".
expr = Reg.data.cast('text').regexp('"k1": "v1"')
query = Reg.select().where(expr)
for row in query:
print(row.key, row.data)

打印

k1 {'k1': 'v1'}

Answer 2

要从JSON值(json或jsonb(中提取纯字符串(不带关键字名称的字符串基元(，可以提取；空路径"；类似：

SELECT jsonb '"my string"' #>> '{}';

这也适用于我(使用jsonb，但不使用json(，但它更像是一个破解：

SELECT jsonb '"my string"' ->> 0

因此：

SELECT * FROM tbl WHERE (jsonb_column #>> '{}') ~ 'my regex here';