3
class Blog:
   values = JSONField(blank=True, default=list)

[
  {
    "id": 1,
    "value": "31"
  },
  {
    "id": 2,
    "value": "Hello"
  },
  ...
]

I need to get all objects where the id is 1 and value of that field is greater than 31. I have tried q = queryset.filter(values__0__id=1, values__0__value_gte=31) but it works only for objects if an object that I need located only in first element.

yvesonline
  • 4,609
  • 2
  • 21
  • 32
Yelibay Nuptebek
  • 33
  • 4

2 Answers2

3

Apparently, nowadays Django has not built-in support for array elements comparison for JSONField. Fortunately, Django allows to make a lot of custom staff. For example, Django's raw SQL feature.

And if you use PostgreSQL as your main DB, you can use JSON Processing Functions. The jsonb_array_elements() from JSON processing functions is pretty nice choice.

Combining features above, we can make a little workaround for your case:

# Create method that uses raw SQL within your `objects` Model Manager.
# Create your own filters as you want. This example should be improved
# and handle exception cases of course.
def filter_blogs_json(json_field, sql_operator, value):
    return Blog.objects.raw(f"SELECT id, data FROM yourappname_blog CROSS JOIN jsonb_array_elements(values) AS data WHERE (data->'{json_field}')::numeric {sql_operator} {value};")

# You can get raw objects queryset 
raw_blogs_qs = filter_blogs_json('value', '>=', 31)

# Then you can process it anyway you want
filtered_blog_ids = [b.id for b in raw_blogs_qs]
queryset = Blog.objects.filter(...).filter(id__in=filtered_blog_ids)

Pretty easy, isn't it? :)

Moreover, I believe it is possible to make your own queryset Lookup's for JSONField, extend queries as you want and etc.

catscoolzhyk
  • 675
  • 10
  • 29
0

Hello and welcome to Stack Overflow!

Try looking at Django's Q objects. Some documentation is available here.

Case 1

As mentioned in this answer, try using the __contains filter:

Blog.objects.filter(values__contains=[{'id': 1}])

and then manually filter the results for the second field.

Case 2

Perhaps a better option would be to have a second table for the individual values, like the following models:

class Blog(models.Model):
    name = models.CharField(max_length=100)  # or something else


class Value(models.Model):
    blog = models.ForeignKey(Blog, on_delete=models.CASCADE)
    json = models.JSONField(blank=True, related_name="values")

and then perform the search like so:

Blog.objects.filter(Q(values__json__id=1) & Q(values__json__value__gte=31))
Amélie Krejčí
  • 449
  • 4
  • 12
  • Your case work if and only if JSONField(default=dict), but in my case is list of dict – Yelibay Nuptebek Mar 29 '21 at 13:06
  • @YelibayNuptebek in the first case it should look into the list. The second case is a cleaner way to accomplish the same thing, just breaking the JSON list into multiple DB rows only containing a JSON dict. – Amélie Krejčí Mar 29 '21 at 13:16
  • what is your first case and second case? describe @MichalKrejčí – Baktiyar Bekbergen Mar 29 '21 at 13:34
  • @MichalKrejčí it does not work for this case, `Blog.objects.filter(Q(values__json__id=1) & Q(values__json__value__gte=31))` work onlu of JsonField contain only json, but in this case is list of dict – Baktiyar Bekbergen Mar 29 '21 at 14:56