#35568: Searching nested JSON values
-----------------------------------------+--------------------------------
Reporter: Vasu Nagendra | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 5.0
Severity: Normal | Keywords: QuerySet.extra
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-----------------------------------------+--------------------------------
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).
--
Ticket URL: <https://code.djangoproject.com/ticket/35568>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107019065b7b7a5-585acf15-dcb2-4628-812e-ef6451cf0721-000000%40eu-central-1.amazonses.com.
