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

Hi, are there any plans to support more advanced PostgreSQL function like json_to_recordset(json) / jsonb_to_recordset(jsonb)? If not, what approach would you suggest, to add such functions? Example dummy usage for such function would be to retrieve values from elements of specified type from following list [{“value”: 3, “type”: “first”}, {“value”: 5, “type”: “second”}, …] stored undef json field.

Hello @Adiorz, these two functions can be implemented today using Func in main

from django.db.models import Func, JSONField

class JSONToRecordset(Func):
    function = "json_to_recordset"
    set_returning = True

The problem you’ll likely face though is that the ORM has very limited support for set-returning functions at this point.

For example, they can be used in the SELECT clause using annotate and in the WHERE clause using filter but the ORM doesn’t provide a way to use them in the FROM clause yet.

You’d need to provide more details about what you are hoping to use them for to determine if the ORM can support your needs.

Hi, thanks for the reply. 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.

models:

from django.db import models
from django.contrib.postgres.fields import JSONField

class Project(models.Model):
    name = models.CharField(max_length=100)

class Mode(models.Model):
    name = models.CharField(max_length=50, unique=True)

class MachineSetup(models.Model):
    project = models.ForeignKey(Project, on_delete=models.CASCADE, related_name='setups')
    mode = models.ForeignKey(Mode, on_delete=models.PROTECT, related_name='setups')
    config = JSONField()

    class Meta:
        unique_together = ['project', 'mode']

    def __str__(self):
        return f"{self.project.name} - {self.operation_mode.name} - {self.machine_name}"

example config data:

[
    {
        "parameter": "OperationSpeed",
        "value": 80,
        "unit": "%",
        "exceeded": True,
        "details": {
            "adjustable": True,
            "requires_cooldown": False,
            "energy_intensive": False,
            "precision_level": "low"
        }
    },
    {
        "parameter": "IdleModePowerConsumption",
        "value": 0.5,
        "unit": "kW",
        "exceeded": False,
        "details": {
            "adjustable": False,
            "requires_cooldown": False,
            "energy_intensive": False,
            "precision_level": "medium"
        }
    },
    ...
]

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")),
    )
)