Query in all members of a list JSONField

Question

In django 4.2 I have a model with an attribute specialties.

it is a JSONField and it is a list like this:

Practitioner.objects.create(
     name="Aldridge, Elizabeth Jane",
     specialties = [
    {
        "type": "CFPC Specialist",
        "issued_on": "Effective:18 May 2009",
        "specialty": "Family Medicine"
    },
    {
        "type": "RCPSC Specialist",
        "issued_on": "Effective:30 Jun 2019",
        "specialty": "Anesthesiology"
    }
]

it may have 0 member or more than 2 members.

this is my query:

practitioners = Practitioner.objects.filter(specialties__0__specialty = "Anesthesiology")

i want to search in all members of specialties not in 0th one.

I have tested this incorrect queries:

practitioners = Practitioner.objects.filter(specialties__each__specialty = "Anesthesiology")

practitioners = Practitioner.objects.filter(specialties__any__specialty = "Anesthesiology")

practitioners = Practitioner.objects.filter(specialties__all__specialty = "Anesthesiology")

i did not find any solution in django documentations.

Answer 1

I would advise not to use a JSONField ^[Django-doc]. In relational databases, JSONFields nearly always break first normal form ^[wiki] and the case that you want to search over all records actually proves that it breaks first normal form: all columns should be treated as atomic values in the sense that you don't want to look at substructures. But here you clearly do that.

Especially since the data is structured, there is no reason to use a JSONField, use an extra model with a ForeignKey ^[Django-doc]:

class Practitioner(models.Model):
    name = models.CharField(max_length=128, unique=True)


class Specialty(models.Model):
    practicioner = models.ForeignKey(
        Practitioner, on_delete=models.PROTECT, related_name='specialties'
    )
    issued_on = models.DateField()
    type = models.CharField(max_length=128)
    specialty = models.CharField(max_length=128)

then we query with:

Practitioner.objects.filter(specialties__specialty='Anesthesiology')

Since the values like type and specialty seem to be categorical, you might even want to make extra models for these, and add ForeignKeys from Specialty to these extra models. This will also reduce the size of the database, make querying more efficient, but perhaps the main reason why normal forms were introduced in the first place: if you need to update the name of a specialty, that is quite easy.

Answer 2

I found my solution:

practitioners = Practitioner.objects.filter(specialties__contains = [{"specialty":"Anesthesiology"}])