SQL panel: psycopg2.DataError: invalid input syntax for type json

[blueyed]

I was getting an error when trying to select an SQL query that gets generated for a jsonb list_filter field in Django 1.9 from the SQL panel (through the "Sel" form / button):

Internal Server Error: /__debug__/sql_select/
Traceback (most recent call last):
  File "…/django/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
psycopg2.DataError: invalid input syntax for type json
LINE 1: ...r"."id") WHERE "app_model1"."firmware_versions" = '''"{''''x...
                                                             ^
DETAIL:  Token "'" is invalid.
CONTEXT:  JSON data, line 1: '...

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "…/django/django/core/handlers/base.py", line 149, in get_response
    response = self.process_exception_by_middleware(e, request)
  File "…/django/django/core/handlers/base.py", line 147, in get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/usr/lib64/python3.5/contextlib.py", line 30, in inner
    return func(*args, **kwds)
  File "…/django/django/views/decorators/csrf.py", line 58, in wrapped_view
    return view_func(*args, **kwargs)
  File "…/django-debug-toolbar/debug_toolbar/panels/sql/views.py", line 19, in sql_select
    cursor.execute(sql, params)
  File "…/django/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "…/django/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "…/django/django/db/utils.py", line 95, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "…/django/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "…/django/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
django.db.utils.DataError: invalid input syntax for type json
LINE 1: ...r"."id") WHERE "app_model1"."foo_versions" = '''"{''''x...
                                                             ^
DETAIL:  Token "'" is invalid.
CONTEXT:  JSON data, line 1: '...

The WHERE clause in the "SQL queries" page is:

WHERE "lock8_lock"."firmware_versions" = '"{''xmega'': ''c293b87'', ''nordic'': ''c293b87''}"'

There is no SQL error from Django itself, so I guess that there is some double escaping / quoting involved.

[selimt]

I ran into this problem today and solved it by modifying the _decode method in panels/sql/tracking.py as follows:

from django.contrib.postgres.fields.jsonb import JsonAdapter

    def _decode(self, param):
        try:
            if isinstance(param, list):
                return map(self._decode, param)
            elif isinstance(param, JsonAdapter):
                return param.dumps(param.adapted)
            else:
                return force_text(param, strings_only=True)
        except UnicodeDecodeError:
            return '(encoded string)'

Not sure if it is the right approach but it works for me so far.

[Ravindrakumara]

return self.cursor.execute(sql, params) django.db.utils.DataError: invalid input syntax for type double precision: "no"