Filter list dict in JsonField Postgres

[Martin Peveri]

Hi, I have this model:

class ItemCampaign(models.Model):

   campaign = models.ForeignKey(

       Campaign, related_name="itemscampaign", verbose_name="Item campaña"

   )

   data = JSONField(default=dict)

   def __str__(self):

       return self.campaign.name 

With a record with this data: 

[{'number': '1160188479', 'id': 0, 'content': 'hello', 'processed': True}, {'number': '1160188479', 'id': 1, 'content': 'hello', 'processed': False}, {'number': '1160188479', 'id': 2,'content': 'hello', 'processed': False}, {'number': '1162341721', 'id': 3, 'content': 'hello', 'processed': False}, {'number': '1162341721', 'id': 4, 'content': 'hello', 'processed':False}, {'number': '1162341721', 'id': 5, 'content': 'hello', 'processed': False}]

I want filter this list dict, like this:

c.itemscampaign.filter(data__processed=True)

But not work. That does not give me back anything.

The content type of data is:

>>> type(c.itemscampaign.all()[0].data)
<class 'list'>

I understand that it has to be a dict, but how can I insert many dictionaries in the field, as I have in my data if it is not in a list?

Thanks!

[Camilo Torres]

Hi,
What is JSONField? Is it part of Django or from a third party package?

[Camilo Torres]

Hi.
I have been thinking more about this.
I think you cannot filter the list in the query with the JSONField from django. At most, you can get the object and filter the field in the resulting list.

Example with this simple model:

from django.db import models
from django.contrib.postgres.fields import JSONField
class DataModel(models.Model):
    data_field = JSONField(default=dict)

You can do:

DataModel.objects.create(data_field=[{'number': '1160188479', 'id': 0, 'content': 'hello', 'processed': True}, {'number': '1160188479', 'id': 1, 'content': 'hello', 'processed': False}, {'number': '1160188479', 'id': 2 ,'content': 'hello', 'processed': False}, {'number': '1162341721', 'id': 3, 'content': 'hello', 'processed': False}, {'number': '1162341721', 'id': 4, 'content': 'hello', 'processed':False}, {'number': '1162341721', 'id': 5, 'con tent': 'hello', 'processed': False}])

Then you can query the database to get that record:

o = DataModel.objects.get()
processed = [x for x in o.data_field if x['processed']]

Variable 'processed' will have the processed data.


If you want to query your models by having the 'processed' field to true, I found you can do it if you change the way you save your data, one option may be:

DataModel.objects.create(data_field={'processed': [{'number': '1160188479', 'id': 0, 'content': 'hello', 'processed': True}], 'not_processed': [{'number': '1160188479', 'id': 1, 'content': 'hello', 'processed': False}, {'number': '1160188479', 'id': 2 ,'content': 'hello', 'processed': False}, {'number': '1162341721', 'id': 3, 'content': 'hello', 'processed': False}, {'number': '1162341721', 'id': 4, 'content': 'hello', 'processed':False}, {'number': '1162341721', 'id': 5, 'con tent': 'hello', 'processed': False}]})

Notice I saved the data separeting the processed and the not_processed. The you can query objects with any of these keys:

queryset = DataModel.objects.filter(data_field__has_key='processed')
Then you can iterate over the queryset and the the data from every dict.

Anyway, to do advanced queries over that data you may be forced to save it in a different way. You can even save the "processed" and "not processed" data in different list in different JSONFields on the same model, and query like this:

queryset = DataModel.objects.filter(processed__isnull=False)

On Monday, April 3, 2017 at 10:34:18 AM UTC-4, Martin Peveri wrote: