Django Rest Framework filter on the base of JSONField nested objects

Question

I'm working on a Project using Python(3), Django(1.11) and DRF in which I have to filter the data on the base of a json object field which is saved as JSONFIELD in db model.

Here's what I have tried:

# model.py

from django.db import models
import jsonfield

class MyModel(models.Model):
    id = models.CharField(primary_key=True, max_length=255)
    type = models.CharField(max_length=255)
    props = jsonfield.JSONField()
    repo = jsonfield.JSONField()
    created_at = models.DateTimeField()

# serializers.py

class MyModelSerializer(serializers.ModelSerializer):
    props = serializers.JSONField()
    repo = serializers.JSONField()

    class Meta:
        model = EventModel
        fields = "__all__"

# JSON object
{
  "id":4633249595,
  "type":"PushEvent",
  "props":{
    "id":4276597,
    "login":"iholloway",
    "avatar_url":"https://avatars.com/4276597"
  },
  "repo":{
    "id":269910,
    "name":"iholloway/aperiam-consectetur",
    "url":"https://github.com/iholloway/aperiam-consectetur"
  },
  "created_at":"2016-04-18 00:13:31"
}

# views.py

class PropsEvents(generics.RetrieveAPIView):
    serializer_class = MyModelSerializer

    def get_object(self):
        print(self.request.parser_context['kwargs']['id'])
        queryset = MyModel.objects.filter(data__props__id=self.request.parser_context['kwargs']['id'])
        obj = get_object_or_404(queryset)
       return obj

It should return the MyModel records by props ID and should be able to return the JSON array of all the MyModel objects where the props ID by the GET request at /mymodel/props/<ID>. If the requested props does not exist then HTTP response code should be 404, otherwise, the response code should be 200. The JSON array should be sorted in ascending order by MyModel ID.

When I sent a request to this view, it returns an error:

> django.core.exceptions.FieldError: Unsupported lookup 'id' for JSONField or join on the field not permitted.
> [18/Feb/2019 10:37:39] "GET /events/actors/2790311/ HTTP/1.1" 500 16210

So, how can I filter the objects based on the id of props?

Help me, please! Thanks in advance!

I'm voting to close this question as off-topic because you already asked the very same question here: https://stackoverflow.com/questions/54744169/django-rest-framework-retrieve-nested-jsonfield-with-filters?noredirect=1#comment96273442_54744169 — dirkgroten, Feb 18 '19 at 11:13

mfrackowiak · Accepted Answer · 2019-02-18T11:16:07.263

The feature your are looking for is possible, unfortunately it is not that straightforward. As far as I know it is not supported by the jsonfield package, but instead you would have to use Postgres as your database backend and use its internal JSONField. I think you can choose one of the following:

switch to django.contrib.postgres.fields.JSONField and use Postgres as your db backend in all enviroments (and then support such lookups)
make the data follow certain schema and change the JSONField to a separate model and table
use a hybrid storage solution with a dedicated solution for JSON documents
extract the fields you need to query against to your model - enabling the querying, but keeping the unstructured data in the JSONField.

class MyModel(models.Model):
    id = models.CharField(primary_key=True, max_length=255)
    type = models.CharField(max_length=255)
    props = jsonfield.JSONField()
    props_id = models.IntegerField(null=True)
    repo = jsonfield.JSONField()
    repo_id = models.IntegerField(null=True)
    created_at = models.DateTimeField()

And then set the id values manually or in the save() of the model:

def save(self, *args, **kwargs):
    self.repo_id = self.repo.get("id")
    self.props_id = self.props.get("id")
    return super().save(*args, **kwargs)

can we achieve this with `sqlite3` because it's the requirements to use `sqlite3`? — Abdul Rehman, Feb 18 '19 at 11:07
I'm afraid not, this is natively supported only by postgres, hence why it is not in main django fields. To stay with sqlite you will need to try one of the others solutions, or e.g. extract the fields you need to query against to a separate model field. I'll update the answer with this idea. — mfrackowiak, Feb 18 '19 at 11:11
Hi @mfrackowiak, on creating new object the `porps_id` is not adding automatically from `self.props.get("id")`, and when I remove the ` null=True` the it's required `props_id` from input object. I can't change the input object structure, so how can I force it to add `props_id` from `save()`? — Abdul Rehman, Feb 18 '19 at 12:31

score 0 · Answer 2 · answered Feb 18 '19 at 11:02

0

You should use

from django.contrib.postgres.fields import JSONField

Instead of

import jsonfield

And after that I think everything should work correctly

answered Feb 18 '19 at 11:02

Andrei Berenda

1,946
2
13
27

now it returns `sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.` for `sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.` – Abdul Rehman Feb 18 '19 at 11:05
sqlite3 is not so good for production, this solution works if you use postgresql as a database and do not work for other databases – Andrei Berenda Feb 18 '19 at 13:43

Django Rest Framework filter on the base of JSONField nested objects

2 Answers2