Can I use a jsonpath predicate in a filter?

Question

I have a model with a JSONField and need to select just the objects that have a certain value somewhere nested in the jsonb.

From the Django documentation I understand you can use contains but the Postgres @> operator for containment does not recurse into the structure. I'd like to use @? '$.** ? (@.mykey == "myValue")' to filter. myKey and myValue are literals in my code, they are not user input.

Answer 1

Well you can always drop down to writing raw SQL.

raw_queryset = MyModel.objects.raw("""
    SELECT * FROM myapp_mymodel
    WHERE jsonfield_name @? '$.** ? (@.mykey == "myValue")'
""")

But there are a bunch of caveats to using these.