7

I am having a Json field in my models as - 

class Product(models.Model):
    ...
    detailed_stock           = JSONField(load_kwargs={'object_pairs_hook': collections.OrderedDict},default=dict)

I am having values in my database like - 

{
  "total":0,
  "5[1]":0
}

I am trying to filter objects with total = 0, for that I tried -

Product.objects.filter(detailed_stock__total = 0) but it throws error -

Unsupported lookup 'total' for JSONField or join on the field not permitted.

as per the documentation the following code is permitted.

this is full traceback- 

Traceback (most recent call last):
  File "C:\Users\lenovo\AppData\Local\conda\conda\envs\myDjangoEnv\lib\site-packages\django\core\handlers\exception.py", line 35, in inner
    response = get_response(request)
  File "C:\Users\lenovo\AppData\Local\conda\conda\envs\myDjangoEnv\lib\site-packages\django\core\handlers\base.py", line 128, in _get_response
    response = self.process_exception_by_middleware(e, request)
  File "C:\Users\lenovo\AppData\Local\conda\conda\envs\myDjangoEnv\lib\site-packages\django\core\handlers\base.py", line 126, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "C:\Users\lenovo\AppData\Local\conda\conda\envs\myDjangoEnv\lib\site-packages\django\views\generic\base.py", line 69, in view
    return self.dispatch(request, *args, **kwargs)
  File "C:\Users\lenovo\AppData\Local\conda\conda\envs\myDjangoEnv\lib\site-packages\braces\views\_access.py", line 102, in dispatch
    request, *args, **kwargs)
  File "C:\Users\lenovo\AppData\Local\conda\conda\envs\myDjangoEnv\lib\site-packages\django\views\generic\base.py", line 89, in dispatch
    return handler(request, *args, **kwargs)
  File "C:\Users\lenovo\AppData\Local\conda\conda\envs\myDjangoEnv\lib\site-packages\django\views\generic\list.py", line 142, in get
    self.object_list = self.get_queryset()
  File "c:\Users\lenovo\Desktop\My_Django_Stuff\bekaim\accounts\views.py", line 142, in get_queryset
    queryset = Product.objects.filter(detailed_stock__total = 0)
  File "C:\Users\lenovo\AppData\Local\conda\conda\envs\myDjangoEnv\lib\site-packages\django\db\models\query.py", line 836, in filter
    return self._filter_or_exclude(False, *args, **kwargs)
  File "C:\Users\lenovo\AppData\Local\conda\conda\envs\myDjangoEnv\lib\site-packages\django\db\models\query.py", line 854, in _filter_or_exclude
    clone.query.add_q(Q(*args, **kwargs))
  File "C:\Users\lenovo\AppData\Local\conda\conda\envs\myDjangoEnv\lib\site-packages\django\db\models\sql\query.py", line 1253, in add_q
    clause, _ = self._add_q(q_object, self.used_aliases)
  File "C:\Users\lenovo\AppData\Local\conda\conda\envs\myDjangoEnv\lib\site-packages\django\db\models\sql\query.py", line 1271, in _add_q
    current_negated, allow_joins, split_subq)
  File "C:\Users\lenovo\AppData\Local\conda\conda\envs\myDjangoEnv\lib\site-packages\django\db\models\sql\query.py", line 1277, in _add_q
    split_subq=split_subq,
  File "C:\Users\lenovo\AppData\Local\conda\conda\envs\myDjangoEnv\lib\site-packages\django\db\models\sql\query.py", line 1215, in build_filter
    condition = self.build_lookup(lookups, col, value)
  File "C:\Users\lenovo\AppData\Local\conda\conda\envs\myDjangoEnv\lib\site-packages\django\db\models\sql\query.py", line 1069, in build_lookup
    lhs = self.try_transform(lhs, name)
  File "C:\Users\lenovo\AppData\Local\conda\conda\envs\myDjangoEnv\lib\site-packages\django\db\models\sql\query.py", line 1115, in try_transform
    (name, lhs.output_field.__class__.__name__))
django.core.exceptions.FieldError: Unsupported lookup 'total' for JSONField or join on the field not permitted.
[31/Dec/2018 16:13:37] "GET /accounts/product-list/?clean=outofstock HTTP/1.1" 500 150927

I searched on internet but unable to find the solution, please help.

Pankaj Sharma
  • 2,185
  • 2
  • 24
  • 50

2 Answers2

12

I think you are using django-jsonfield as indicated by load_kwargs={'object_pairs_hook': collections.OrderedDict} instead of django.contrib.postgres.fields.JSONField.

django-jsonfield is for databases which don't offer a native dict type and is based on a simple TextField. When you access the field value using product.detail_stock the internally saved str is converted to dict using json.loads() by the field itself. Hence you can only use operations like icontains and contains for querying that field.

If you are using postgres as a database, you are able to take full advantage of django.contrib.postgres.fields.JSONField as the documentation states. But you have to import the correct JSONfield by using django.contrib.postgres.fields import JSONField.

There is a solution for mysql (package django-mysql) too.

escaped
  • 734
  • 4
  • 10
  • ok, there is problem, so if now I change the field will it migrate without any problem ? – Pankaj Sharma Dec 31 '18 at 11:19
  • I doubt that since both fields have a different internal structure. But you could give it a try and report if it works. – escaped Dec 31 '18 at 11:25
  • Otherwise you have to migrate your data by 1. add a new JSONField (postgres) with a different name, 2. Use a [data migration](https://docs.djangoproject.com/en/2.1/topics/migrations/#data-migrations) to copy the data to the new field, 3. remove the original field, 4. rename the new field to `detailed_stock`. This can be done in a single migration. If you need any help, feel free to ask. – escaped Dec 31 '18 at 11:26
  • Is it possible to use contains for django-jsonfield module? I tried, but it's not working. – jack Oct 14 '19 at 14:48
  • 1
    `django-jsonfield` uses a `CharField` internally. If you use `__contains` to query that field, you have to be very precise in terms of spaces and quotes, eg. `__contains="'foo':3"` would not match against `{"foo": 3}`. – escaped Oct 17 '19 at 12:54
0

If you are using Django and have used:

from json_field import JSONField

Then you can not benefit the SQL lookup. As mentioned above, JSONField overrides the TextField only. It validates the JSONformat and dumps as string.

class JSONField(models.TextField):
    """ Stores and loads valid JSON objects. """

Instead use

from django.db import models
data = models.JSONField(null=True)
incarnadine
  • 658
  • 7
  • 19
Abhishek
  • 1
  • 1