[Python] Django filter objects which JSONField value contains in another model's JSONField...

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?

Continue reading...