DjangoRestFramework: search and sort a element inside a JsonField

Question

Is it possible somehow to search/sort a field inside a JsonField by creating a custom method using django_rest_framework?

----------------------------------------------------------------
| name      |               my_json_field                      |
---------------------------------------------------------------- 
| record_1  | {"field1": "A1", "field2": "A2", "field3": "A3"} | 
----------------------------------------------------------------
| record_2  | {"field1": "B2", "field2": "B1" }                |
----------------------------------------------------------------
| record_3  | {"field1": "C3", "field2": "C2", "field3": "C1"} |
----------------------------------------------------------------

So that:

if filter by my_json_field -> field1 = "A1" I get:

----------------------------------------------------------------
| name      |               my_json_field                      |
---------------------------------------------------------------- 
| record_1  | {"field1": "A1", "field2": "A2", "field3": "A3"} | 
----------------------------------------------------------------

if sort DESC by my_json_field -> field3 I get:

----------------------------------------------------------------
| name      |               my_json_field                      |
----------------------------------------------------------------
| record_3  | {"field1": "C3", "field2": "C2", "field3": "C1"} |
---------------------------------------------------------------- 
| record_1  | {"field1": "A1", "field2": "A2", "field3": "A3"} | 
----------------------------------------------------------------
| record_2  | {"field1": "B2", "field2": "B1" }                |
----------------------------------------------------------------

score 4 · Accepted Answer · answered May 09 '19 at 01:12

I haven't tested this, but hopefully should point you in the right direction.

The first question is what Django can do - can Django filter and sort by arbitrary key transforms on JSONFields? For filtering, absolutely. For sorting, not directly. Generally, you can't sort by transforms, however you can annotate those transforms to the queryset, then filter on the annotations. This answer provides an example of how to do this with a JSONField.

The second question is what can DRF do. Looking through the code, the SearchFilter does not heavily rely on the model meta, so using a key transform in search_fields should be fine. If not, you might try the annotation instead. Similar to Django, the OrderingFilter doesn't support transforms, but it does support annotations. Putting this together, something like the following should work:

from rest_framework import viewsets, filters
from django.contrib.postgres.fields.jsonb import KeyTextTransform
from my_app.models import MyModel

class MyModelViewSet(viewsets.ModelViewSet):
    queryset = MyModel.objects \
        .annotate(field1=KeyTextTransform('my_json_field', 'field1') \
        .annotate(field2=KeyTextTransform('my_json_field', 'field2') \
        .annotate(field3=KeyTextTransform('my_json_field', 'field3')

    filter_backends = [filters.SearchFilter, filters.OrderingFilter]
    search_fields = ['field1', 'field2', 'field3']
    ordering_fields = ['field1', 'field2', 'field3']

Note that you can't explicitly search by just a single field, all search fields are searched. To do that, you'd need to use something like django-filter.

Thank you very much I was able to do what I wanted! I didn't know annotation before today — Francesco Meli, May 09 '19 at 03:16
Since Django 3 use django.db.models.fields.json.KeyTextTransform — ScotchAndSoda, Mar 08 '21 at 11:04

DjangoRestFramework: search and sort a element inside a JsonField

1 Answers1