here is my sample jsonb field:
{
"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", # I need to lookup at this level (or key)
"agents": [
{
"email": "ak@xxx.com",
"user_name": "Akhild",
"review_status": 0
}
],
"req_id": "XXXXXXXX",
"status": "Requested"
},
{
"title": "onboarding", # I need to lookup at this level (or key)
"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"
}
In this JSON i need to do case insensitive match for structure->section->request(array)-> title inside each object of request array I have tried this query filter
(Q(requests__structure__sections__contains=[{'requests':[{"title": query}]}]))
but it becomes case sensitive. Also i have tried
self.get_queryset().annotate(search=SearchVector(Cast('requests__structure__sections', TextField()))
which does gives case insensitive result but also lookup among the keys other than title.
also i tried raw sql where i cannot go beyond the request array.
Im expecting any other method or any other approach in django orm that can be used to achieve the require result.