sql_select, sql_explain fails with json contains queries

[minusf]

Consider this simple query (data is a JSONField):

ApiUser.objects.filter(data__contains={"is_member": True})

The sql panel will show this query correctly:

SELECT ... FROM "api_apiuser" WHERE "api_apiuser"."data" @> '{"is_member": true}' LIMIT 21

But Sel and Expl fail with an exception:

2019-01-23 16:13:40,664 DEBUG: utils.py:111: (0.003) SELECT ... FROM "api_apiuser" WHERE "api_apiuser"."data" @> '''{"is_member": true}'''  LIMIT 21; args=['\'{"is_member": true}\'']
django.db.utils.DataError: invalid input syntax for type json
LINE 1: ... FROM "api_apiuser" WHERE "api_apiuser"."data" @> '''{"is_me...
                                                             ^
DETAIL:  Token "'" is invalid.
CONTEXT:  JSON data, line 1: '...

cursor.execute() fails because of the triple quotation marks(?).

(Pdb++) sql
'SELECT ... FROM "api_apiuser" WHERE "api_apiuser"."data" @> %s  LIMIT 21'
(Pdb++) params
['\'{"is_member": true}\'']

[tim-schilling]

Was this with postgres?

[minusf]

yes

[tim-schilling]

What's happening here is that when we decode a JsonAdapter param, the adapter returns the param as a sql quoted string. I think what we want to do is prevent that from happening and simply have it return the JSON string.

Running the following:

Model.objects.filter(json_field__contains={'foo': 'bar'})

With panels.sql.tracking.NormalCursorWrapper._decode below

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

def _decode(self, param):
    if isinstance(param, JsonAdapter):
        print([param.dumps(param.adapted), str(param)])
        return param.dumps(param.adapted)

Prints the following:

['{"foo": "bar"}', '\'{"foo": "bar"}\'']

The str call is effectively what we return today from _decode for this parameter. The problem is that the rendered form that's used to request the Select or Explain data now has a string that contains the sql wrapping ' characters. Instead we just want a string of '{"foo": "bar"}' to be the parameter.