Use a SQL function in the FROM clause with the Django ORM

Viewed 33

I need to convert the following SQL into a Django ORM query but cannot find a way to pull it off. It involves using a SQL function in the FROM clause. In this case, it's a JSONB function that will put the key/value pairs of the json field into separate columns.

survey.answers is a JSONB field. The underlying database is postgres.

SELECT s.id, jsonb_each_text.key, jsonb_each_text.value 
FROM survey as s, jsonb_each_text(s.answers);

What Have I Tried?

There is this 3rd party plugin https://github.com/BezBartek/django-dynamic-from-clause that attempts to solve the problem, as you seemingly cannot generate tables from functions within the ORM. However, after many hours,

  1. I cannot properly pass in the correct parameter 's.answers' from the other table.
  2. I don't understand how to get both tables in the result

The generated query below just gets a syntax error for the function parameter.

class JsonbEachText(models.Func):
    function = 'jsonb_each_text'
    template = "%(function)s('%(expressions)s')"


class JsonbTable(DynamicFromClauseBaseModel):
    EXPRESSION_CLASS = JsonbEachText

    key = models.SlugField(primary_key=True)
    value = models.TextField()
    survey = models.ForeignKey(
        Survey, related_name='answers_json', on_delete=models.DO_NOTHING,
    )


result = JsonbTable.objects.fill_expression_with_parameters(
   "response__answers")
)

In [18]: print(result.query)
SELECT "_jsonbtable"."key", "_jsonbtable"."value", "_jsonbtable"."survey_id" FROM jsonb_each_text('response_answers') AS "_jsonbtable"
0 Answers
Related