如何用Django ORM实现多层JSONB字段的大小写不敏感查询

需求场景 #

现有如下JSONB字段结构：

{
  "name": "XXXXX",
  "duedate": "Wed Aug 31 2022 17:23:13 GMT+0530",
  "structure": {
    "sections": [
      {
        "id": "0",
        "temp_id": 9,
        "expanded": true,
        "requests": [
          {
            "title": "entity onboarding form",
            "agents": [
              {
                "email": "ak@xxx.com",
                "user_name": "Akhild",
                "review_status": 0
              }
            ],
            "req_id": "XXXXXXXX",
            "status": "Requested"
          },
          {
            "title": "onboarding",
            "agents": [
              {
                "email": "adak@xxx.com",
                "user_name": "gaajj",
                "review_status": 0
              }
            ],
            "req_id": "XXXXXXXX",
            "status": "Requested"
          }
        ],
        "labelText": "Pan Card"
      }
    ]
  },
  "Agentnames": "",
  "clientowners": "Admin",
  "collectionname": "Bank_duplicate"
}

需要对structure->sections->requests数组内每个对象的title值进行大小写不敏感的精准/模糊匹配。

已尝试方案及问题 #

• 使用Q(requests__structure__sections__contains=[{'requests':[{"title": query}]})：查询为大小写敏感，不符合需求
• 使用SearchVector结合Cast：能实现大小写不敏感，但会匹配title以外的其他键值，出现误匹配
• 原生SQL：无法正确遍历到requests数组的深层内容

可行解决方案 #

方案1：利用PostgreSQL JSONB路径查询（推荐）

直接调用PostgreSQL的jsonb_path_exists函数，通过JSON路径语法实现精准的大小写不敏感匹配，无需展开数组，效率较高。

精准匹配示例

from django.db.models import Func, Value

query = "ONBOARDING"  # 输入的查询词
query_lower = query.lower()

# 构造JSON路径：匹配所有sections下的requests数组中的title值，忽略大小写
path = Value(f'$.structure.sections[*].requests[*].title ? (@ == "{query_lower}"::text)')
qs = self.get_queryset().filter(
    Func('structure', path, function='jsonb_path_exists')
)

模糊匹配示例（支持通配符）

如果需要模糊查询，可使用正则匹配并指定i（忽略大小写）标志：

from django.db.models import Func, Value

query = "onboard"  # 输入的模糊查询词

# 构造JSON路径：匹配title中包含指定内容的条目，忽略大小写
path = Value(f'$.structure.sections[*].requests[*].title ? (@ like_regex "{query}" flag "i")')
qs = self.get_queryset().filter(
    Func('structure', path, function='jsonb_path_exists')
)

方案2：逐层提取+展开数组后匹配

通过Django的KeyTextTransform和PostgreSQL的unnest函数，逐层提取JSONB字段并展开数组，最终提取title字段转小写后匹配。

from django.db.models import Func, Lower, Q
from django.contrib.postgres.fields.jsonb import KeyTextTransform

query_lower = query.lower()
qs = self.get_queryset().annotate(
    # 提取structure下的sections数组
    sections=KeyTextTransform('sections', 'structure'),
    # 展开sections数组为单个元素
    section=Func('sections', function='unnest'),
    # 提取每个section下的requests数组
    requests=KeyTextTransform('requests', 'section'),
    # 展开requests数组为单个元素
    request=Func('requests', function='unnest'),
    # 提取每个request下的title字段
    title=KeyTextTransform('title', 'request'),
).filter(Lower('title') == query_lower).distinct()

注意：使用unnest会将每个request对象拆分为单独的查询行，需添加.distinct()确保原对象不重复返回。

方案3：借助第三方库简化查询

如果项目允许引入第三方库，django-postgres-extra提供了更简洁的JSONB查询API：

1. 先安装库：pip install django-postgres-extra
2. 配置INSTALLED_APPS添加django_postgres_extra
3. 查询示例：

from django_postgres_extra.models import QuerySet

qs = self.get_queryset().filter(
    structure__jsonb_path_exists=(
        '$.structure.sections[*].requests[*].title',
        f'@ like_regex "{query}" flag "i"'
    )
)

内容的提问来源于stack exchange，提问作者Sreeraj mp