如何优化PostgreSQL JSONB字段中的模糊查询？

Question

在pg数据库中 a表里有一个jsonb类型的字段 用来存储json数组类似格式

---

[{"quantity": 1, "union_id": "zEkEIVr6Coki", "part_name": "颅脑平扫", "part_label": "brain", "part_category": "头部", "parent_union_id": "_CQ2wPm860z3"}]

---

现在有个业务需求需要从改数组中模糊查询所有满足 part_name=key的操作 类似如下

SELECT consultation_parts
FROM "public"."DiagnosisReqs" 
WHERE consultation_parts::text LIKE '%颅脑平扫%';

现在担心如果json数据过大 且数据库该表数据递增到1000000，怎么优化查询效率 众所周知like效率很低 而全表转换为text 在进行like模糊匹配就更慢了 求大神指导， 或者有什么替代思路可以解决需求？

咨询ai 给出加索引

-- 创建 tsvector 类型的表达式索引
CREATE INDEX idx_diagnosisreqs_consultation_parts_tsvector ON "public"."DiagnosisReqs"
USING gin(to_tsvector('english', consultation_parts::text));

-- 使用全文搜索进行查询
SELECT *
FROM "public"."DiagnosisReqs"
WHERE to_tsvector('english', consultation_parts::text) @@ to_tsquery('english', '颅脑平扫');

-- 创建部分索引
CREATE INDEX idx_diagnosisreqs_consultation_parts_partial ON "public"."DiagnosisReqs" (consultation_parts::text)
WHERE consultation_parts::text LIKE '%颅脑平扫%';

-- 如果 consultation_parts 不是文本类型，创建一个函数索引
CREATE INDEX idx_diagnosisreqs_consultation_parts_text ON "public"."DiagnosisReqs" (consultation_parts::text);

-- 分页查询，每页 100 条记录
SELECT *
FROM "public"."DiagnosisReqs"
WHERE consultation_parts::text LIKE '%颅脑平扫%'
LIMIT 100 OFFSET 0;    

Answer 1

对于你的这个问题

我翻阅了无数的网站

得出一个比较靠谱的结论

那就是

还是自己研究研究多努力

有手就行哈哈