Django with MySQL: 'Subquery returns more than 1 row'

Question

Using django with a MySQL DB and given these models:

ModelB   ---FK--->   ModelA
    - ref_type
    - ref_id
 
ModelC

I want to get all the ModelC for each ModelA via an annotation.

I tried many options looking at existing solutions but could not make it work. The following code works when there is just one ModelC for each ModelA but as soon as there is more than one, I get the Subquery returns more than 1 row error and I don't know how to get a list of the ModelC models instead. Ideally, I'd like to build a list of JSON objects of the ModelC.

qs = ModelA.objects.all()

c_ids = (
    ModelB.objects \
        .filter(modela_id=OuterRef(OuterRef('id')), ref_type='c') \
        .values('ref_id')
)
all_c = (
    ModelC.objects \
        .filter(id__in=Subquery(c_ids)) \
        .values('id')
)

qs1 = qs.annotate(all_c=Subquery(all_c ))
for p in qs1:
    print(p, p.all_c)

Answer 1

The following should do

from django.db.models import JSONField
from django.db.models.aggregates import Aggregate

class JSONArrayAgg(Aggregate):
   function = "JSON_ARRAYAGG"
   output_field = JSONField()
   
ModelA.objects.annotate(
    all_c=Subquery(
        ModelB.objects.filter(
            ref_type="c",
            modela_id=OuterRef("id"),
        ).values(
            "modela_id"
        ).values_list(
            JSONArrayAgg("ref_id")
        )
    )
)

which translates to

SELECT
    model_a.*,
    (SELECT JSON_ARRAYAGG(model_b.ref_id)
     FROM model_b
     WHERE model_b.ref_type = "c" AND model_b.modela_id = model_a.id
     GROUP BY model_b.modela_id
    ) all_c
FROM model_a

But it would be much easier if you provided your exact model definition as it's likely only a matter of doing something along the lines of (JSONArrayAgg.filter cannot be used due to a MySQL bug.

ModelA.objects.filter(
   modelb_set__ref_type="c",
).annotate(
   all_c=JSONArrayAgg("modelb_set__ref_id")
)

which translate to

SELECT
    model_a.*,
    JSON_ARRAYAGG(model_b.ref_id)
FROM model_a
INNER JOIN model_b ON (model_b.modela_id = model_a.id)
WHERE model_b.ref_type = "c"
GROUP BY model_a.id

You could also use FilteredRelation if you want the condition to be pushed to the JOIN instead.

ModelA.objects.annotate(
   all_c_rel=FilteredRelation(
      "modelb_set", Q(modelb_set__ref_type="c")
   ),
   all_c=JSONArrayAgg("all_c_rel__ref_id")
)

Which results in

SELECT
    model_a.*,
    JSON_ARRAYAGG(model_b.ref_id)
FROM model_a
LEFT OUTER JOIN model_b ON (
    model_b.modela_id = model_a.id
    AND model_b.ref_type = "c"
)
GROUP BY model_a.id

But the LEFT OUTER JOIN might re-surface the issue you have with MySQL's handling of NULL in JSON_ARRAYAGG.