look up the value in jsonfield of django

Question

The model contains a jsonfield:

class MyModel(Model):
    test_result = JSONField()

the data to be handled is dynamic, {'test1':100,'test2':95,'test9':80,...} , { 'test2':60, 'test3':80,'test6':70,... } ...
I want to find all the test results of 'test2' and save them into a list.

all_score_of_test2 =[x.test_result['test2'] for x in MyModel.objects.filter(test_result__has_keys=['test2'])]

it works but the performance is not good. Is there any faster way to do the task? I am using postgresql13.1

score 0 · Answer 1 · answered May 16 '21 at 08:46

I'd suggest you to create a M2M relationship instead of using JSONField since it can give you a better performance.

class Test(models.Model):
    name = models.CharField(...)  # such as test1, test2


class TestResult(models.Model):
    test = models.ForeignKey(Test, ...)
    person = models.ForeignKey(Person, ...)
    score = models.DecimalField(...)


class MyModel(models.Model):
    test_results = models.ManyToManyField(Test, through=TestResult, ...)

Then you can get scores of test2 by querying:

all_score_of_test2 = TestResult.objects.filter(test__name='test2').values_list('score', flat=True)

look up the value in jsonfield of django

1 Answers1