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: stackoverflow.com/questions/54744169/… — dirkgroten
– dirkgroten, Commented Feb 18, 2019 at 11:13

mfrackowiak · Accepted Answer · 2019-02-18 11:16:07Z

3

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)

edited Feb 18, 2019 at 11:16

answered Feb 18, 2019 at 11:02

$mfrackowiak's user avatar$

mfrackowiak

1,30410 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Abdul Rehman Over a year ago

can we achieve this with sqlite3 because it's the requirements to use sqlite3?

mfrackowiak Over a year ago

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.

Abdul Rehman Over a year ago

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()?

Andrei Berenda · Accepted Answer · 2019-02-18 11:02:28Z

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, 2019 at 11:02

Andrei Berenda

1,9862 gold badges15 silver badges30 bronze badges

2 Comments

Abdul Rehman Over a year ago

now it returns sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type. for sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

Andrei Berenda Over a year ago

sqlite3 is not so good for production, this solution works if you use postgresql as a database and do not work for other databases

Collectives™ on Stack Overflow

Django Rest Framework filter on the base of JSONField nested objects

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related