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,
- I cannot properly pass in the correct parameter 's.answers' from the other table.
- 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"