0

I have a model which mostly stores data in a JSON field. Its kinda set up as follows:

class Record(models.Model):
    data = JSONField()

Usually, the data field has the following format:

data = {
    "name": "Chad",
    "age": 23
}

Now, in a view, I want to get the first 20 records, but ordered using the format ['-age', 'name']. What are my options for doing this? Right now, the only solution I can think of is getting all the records using Record.objects.all(), and iterating the whole thing while ordering using the data field json. Is there a faster way to do this? Or is this my only solution? Moreover, if I am to solve it this way, how would I go about ordering the records, and would it be a huge performance issue on my database? Thanks.

Edit: I cannot make everything into separate fields. It has to be a JSONField using PostgreSQL Database.

darkhorse
  • 8,192
  • 21
  • 72
  • 148
  • 2
    Is this PostgreSQL? Because in most databases, this is just a `CharField` (with some extra data wrapping), hence sorting on inner values is then *not* possible*. If however the keys are fixed, it would be better to make separate fields: `name = models.CharField(max_length=100)` and `age = models.IntegerField()` for example. – Willem Van Onsem Sep 23 '18 at 17:42
  • @WillemVanOnsem Yes it is, and no, it absolutely has to be a JSONField. – darkhorse Sep 23 '18 at 17:43
  • What if you `.annotate(..)` the queryset like `Record.objects.annotate(name=F('data__name'), age=F('data__age')).order_by('-name', '-age')`? – Willem Van Onsem Sep 23 '18 at 18:30

0 Answers0