Advanced PostgreSQL functions like json_to_recordset(json) / jsonb_to_recordset(jsonb)

Django Internals ORM
4

Hello @Adiorz, I don’t think you need to use json_to_recordset for that.

The use case would be to check if there are any parameters exceeded for setups related to given project (I just made it up). The filtering criteria would be to take into account only adjustable=True and energy_intensive=False.

This can be expressed with the following JSON query (you can test it up here)

$[?(@.exceeded==true && @.details.adjustable==true && @.details.energy_intensive=false)]

And you can use the @? operator to determine if there are any matches for that (assuming you are on Postgres) otherwise the same can be achieved using JSON_EXISTS on other backends (and Postgres 17+).

For this particular case you could create a custom lookup

from django.db.models import JSONField, Lookup

@JSONField.register_lookup
class JSONExists(Lookup):
    lookup_name = "jexists"

    def as_sql(self, compiler, connection):
        lhs, lhs_params = self.process_lhs(compiler, connection)
        rhs, rhs_params = self.process_rhs(compiler, connection)
        params = (*lhs_params, *rhs_params)
        return "%s @? %s" % (lhs, rhs), params

And then from there you could simply do

query = (
    "$[?("
    "@.exceeded==true && "
    "@.details.adjustable==true && "
    "@.details.energy_intensive=false"
    ")]"
)

problematic_setups = MachineSetup.objects.filter(
    config__jexists=query
)

Project.objects.filter(
    Exists(
        problematic_setups.filter(project_id=OuterRef("pk")),
    )
)