Django filter objects which JSONField value contains in another model's JSONField values LIST of same "key"

I'm not sure if the title of this post made you understand the problem but let me explain:

I have models:

class ItemCategory(models.Model):
    name = CharField()

class Item(models.Model):
    brand = CharField()
    model = CharField()
    category = ForeignKey(ItemCategory)
    attributes = JSONField(default=dict) # e.g {"size": 1000, "power": 250}

class StockItem(models.Model):
    item = ForeignKey(Item)
    stock = ForeignKey(Stock)
    quantity = PositiveIntegerField()

This models represent some articles on stock. Now I want to implement functionality to allow user create "abstract container" where he can input data and monitor how many of "abstract containers" may be produced based on stock remainings.

Example:

class Container(models.Model):
    name = CharField()

class ContainerItem(models.Model):
    container = models.ForeignKey(Container)
    item_category = models.ForeignKey(ItemCategory)
    attributes = models.JSONField(default=dict)
    quantity = models.PositiveIntegerField()

To handle aggregation I build a view:

class ContainerListView(ListView):
    model = models.Container

    def get_queryset(self):
        items_quantity_sq = models.Item.objects.filter(
            item_category=OuterRef('item_category'),
            attributes__contains=OuterRef('attributes'),
        ).values('item_category').annotate(
            total_quantity=Sum('stock_items__quantity')
        ).values('total_quantity')

        min_available_sq = models.ContainerItem.objects.filter(
            container_id=OuterRef('pk')
        ).annotate(
            available=Coalesce(Subquery(items_quantity_sq), Value(0))
        ).order_by('available').values('available')[:1]

        base_qs = super().get_queryset().annotate(
            # Attach the minimum available quantity across all items
            potential=Subquery(min_available_sq)
        ).prefetch_related(
            Prefetch(
                "items",
                queryset=models.ContainerItem.objects.all()
                    .annotate(available=Subquery(items_quantity_sq))
                    .order_by("available"),
            )
        )

        return base_qs.order_by("potential")

It works until ContainerItem attributes field contains a single value: {"size": 1000} but I want to allow user to input multiple values ("size": [1000, 800]) to find all Item objects which attributes field (JSONField) contains "size": 1000 or "size": 800.

There might be a solution using RawSQL. But I wondering if there is a right design at all for performing such aggregation in a future? Maybe in this case EAV may be a better solution?

Вернуться на верх