Issue filtering queryset by comparing jsonfield value and aggregate value

Using Django Using the ORM
1

Hi everyone,
I’m having an issue with a query in Django ORM.
I have to read an integer from a string in a json and compare it with the result of a Count. Simplified, my code looks like

MyModel.objects.annotate(
    a=Cast(
        Right(Left(F("jsonfield__key"),19),2),
        output_field=IntegerField()
    ),
    b=Count("another_model__id")
).filter(
    a__gt=F("b")
).values(
    "id"
)

produces the query

SELECT `myapp_mymodel`.`id`
FROM `myapp_mymodel`
LEFT OUTER JOIN `myapp_myothermodel` ON (`myapp_mymodel`.`id` = `myapp_myothermodel`.`mymodel_id`)
GROUP BY  `myapp_mymodel`.`id`, CAST(RIGHT(LEFT(JSON_EXTRACT(`myapp_mymodel`.`jsonfield`, $."key"), 19), 2) AS signed integer)
HAVING CAST(RIGHT(LEFT(JSON_EXTRACT(`myapp_mymodel`.`jsonfield`, $."key"), 19), 2) AS signed integer) > (COUNT(`myapp_myothermodel`.`id`)) ORDER BY NULL

and gives the error

django.db.utils.OperationalError: (1054, "Unknown column 'myapp_mymodel.jsonfield' in 'having clause'")

as result.

I’m no MySQL expert but I found that substituting the GROUP BY part with

GROUP BY  `myapp_mymodel`.`jsonfield`

works…

So, I was wondering if anyone can reproduce the error and, eventually, explain why Django writes the query like that.
And finally: is there a way to avoid the OperationalError?
I know I could fix it using a bunch of Charfields in lieu of the Jsonfield, but what if one wants to stay with the Jsonfield…?

Thanks for reading this far!

2

MySQL is pretty weird when dealing with grouping and selecting as this should be allowed per the spec.

Did you try including a in your selected fields as well?

MyModel.objects.annotate(
    a=Cast(
        Right(Left(F("jsonfield__key"),19),2),
        output_field=IntegerField()
    ),
    b=Count("another_model__id")
).filter(
    a__gt=F("b")
).values(
    "id", "a",
)

You can then discard of "a" on the client side if you don’t care about it but this should force the annotation to be part of the select mask which apparently helps MySQL in some cases.

3

Yes, I tried that before. Indeed that was a selected field in my original query, of which the example in the post is a really simplified (yet still troubling) version…
Anyway, selecting the field seems to make no difference.