id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
35568	Searching nested JSON values	Vasu Nagendra	nobody	"As requested in the documentation I am filing the use case for extra. The data in my database looks similar to the data here https://www.postgresql.org/docs/16/functions-json.html#FUNCTIONS-SQLJSON-PATH. It is in a column called `data` in my database in a table called `alerts` (model called Alert). If I have a key of `track.segments.*.HR` (as shown in the example), I can't quite use a `Q` operator. If the data was in the upper level, Q operators work just fine -- but nested arrays it doesn't work. 

The way to do this is really just using a QuerySet extra keyword so using the example they've provided here that would translate to 

{{{
Alert.objects.extra(where=[r""""""data @? '$.track.segments[*].HR ? (@ > 130)'])
}}}

It is quite scary in this case if 130 OR path track.segments[*].HR was obtained from the user because there is no way to send those in a params tuple. Whenever params is used with the `where` clause, it adds a quote which JSONPath doesn't like. 

I looked at KeyTransform as documented in django.db.models.fields.json. This can _somewhat_ be managed if there was only one nesting by messing with the key and using `__contains`. But when there are multiple levels of nesting like this one for example https://learn.microsoft.com/en-us/graph/api/security-alert-get?view=graph-rest-1.0&tabs=http#response-1 and I am trying to find `'$.evidence[*].roles[*] ? (@ == ""compromised"")'` there is no choice but to use `extra`. 

I am happy to take this on if anyone has any ideas on how best to approach this. I have plenty of data to test and am familiar with the general organization of the Django project (have not contributed before). "	Uncategorized	closed	Uncategorized	5.0	Normal	invalid	QuerySet.extra	Vasu Nagendra	Unreviewed	0	0	0	0	0	0