我的核心问题是我有一个jsonb数据库列,其中的数据格式如下:
{"ops": [
{"insert": "yaaaah "},
{"insert": {"atmention": {"id": "183"}}},
{"insert": " "},
{"insert": {"hashtag": "potato"}},
{"insert": " \n"}
]}
这是一个前端quill字段的输出内容,我不想更改其结构。当前,我的 API 会注意到该字段有一个hashtag
条目,并通过提取哈希标签(在本例中为“potato”),手动刷新数据库中的标签列并更新存储标签的 text[] 列。然后我可以通过执行@> tags
来查询匹配项,或者通过select distinct unnest(tags) from documents
获取所有标签的列表。
这种方法虽然功能上可行,但在标签数据方面存在一些不令人满意的问题 - rich_text 列是权威值,但需要计算和更新标签列以供查询使用。
我考虑的解决方案是创建一个文档-标记对的物化视图,例如:
create materialized view hashtags
with taglist as (
select documents.id,
jsonb_array_elements(rich_text->'ops') as ops from documents
)
select
taglist.id,
ops->'insert'->'hashtag'
from taglist
where ops->'insert'->'hashtag' is not null;
这个方法是可行的,但是每次更新、删除或插入文档时都需要刷新物化视图,我认为这样很难扩展,因为它需要基本上顺序扫描每个文档。
我想知道的是是否有一种方法可以说,“嘿,通过删除id = 1的元素来刷新物化视图,然后重新运行查询,其中documents.id = 1”。我会知道视图中哪些条目可能会更改,并且只想更改它们。
欢迎其他建议,比如有效地为此列建立索引(该列是具有单个顶级键“ops”的jsonb元素,其值始终是具有“insert”作为顶级键的对象数组,该键可能具有文本或进一步的对象值,并且也可能具有与“insert”并列的“attributes”顶级键兄弟)。
我的目标是将“这个文档中有哪些hashtag”,“一般有哪些hashtag”,以及“被标记为potato的文档有哪些”等计算移到SQL中,而不是使用我当前使用的非规范技术(再次强调:函数,但是这意味着我必须维护这个额外的列,而且数据可能进入未定义状态,例如如果直接编辑标签列,或者在不重新计算标签列的情况下更新了rich_text字段)。
delete where id+insert select your query where id
- 在某种意义上它是相同的部分材料化。 - Vao Tsun