jsonfield__key__isnull lookup crashes on PostgreSQL 9.4

[Luca Simone Sigfrido Percich]

Given this model:

class MyModel(models.Model):
    jsonbfield = JSONField(null=True, blank=True, default=dict)

The following filter will always result in a "ProgrammingError: operator does not exist jsonb -> boolean":

MyModel.objects.filter(jsonbfield__key__isnull=True)

That's because this is beng translated in the following SQL expression:

... where myapp_mymodel.jsonbfield -> 'key' is null

which does not yield the desired result because in postgres the ->, ->> or #> operators have no precedence over is.

Fix: enclose mapping in parenthesis

(myapp_mymodel.jsonbfield -> 'key') is null

Relevant code:

File: django/contrib/postgres/fields/jsonb.py

row 76 and 83:

return "{} #> %s".format(lhs), [key_transforms] + params
...
return "%s -> %s" % (lhs, lookup), params

Should become:

return "({} #> %s)".format(lhs), [key_transforms] + params
...
return "(%s -> %s)" % (lhs, lookup), params

[Tim Graham]

I agree that Django shouldn't crash. Is this expected to give the same semantics as has_key? Is there any advantage of using one form over the other that might be worth documenting? ​PR

[Luca Simone Sigfrido Percich]

You're right, I didn't notice:

(('{"key": null}' :: jsonb) -> 'key') = 'null'::jsonb

-- which is different from SQL null, thus:

(('{"key": null}' :: jsonb) -> 'key') is null = false

-- but:

(('{"key": null}' :: jsonb) ->> 'key') is null = true

so after the fix, I believe the isnull lookup would behave exactly as has_key.

I won't expect that. There would be no way to find instances having a {'key':null}.

On the other hand, on PG docs they say that jsonb 'null' and SQL NULL have different meaning. Should we introduce a new lookup is_null_json maybe?

[Tim Graham]

I'm not certain, but I guess it might be better to override the isnull lookup to work as you expect. I'll withdraw my patch.

[PREMANAND]

@sigfrid/@tim

Im unable to recreate this issue using the dev version of the Django code.

>>> MyModel.objects.filter(jsonbfield__key__isnull=True)
<QuerySet []>

and here is my model.

class MyModel(models.Model):
    jsonbfield = JSONField(null=True, blank=True, default=dict)

[Tim Graham]

Looks like the crash happens on PostgreSQL 9.4 but not 9.5. The test on ​my PR passes on 9.5 which could make it more difficult to change the behavior since it would change the behavior of possibly working code.

[PREMANAND]

ok,got it. I'm using 9.5 as expected. How do we proceed on these issues?
Replying to timgraham:

Looks like the crash happens on PostgreSQL 9.4 but not 9.5. The test on ​my PR passes on 9.5 which could make it more difficult to change the behavior since it would change the behavior of possibly working code.

[Tim Graham]

I guess we might proceed with ​my PR unless anyone feels like we should change the behavior of the isnull lookup. We could then open a new ticket to add a way to query for keys that have a 'null' value, if one doesn't exist.

[GitHub <noreply@…>]

In 2eb7d6e6:

Fixed #26908 -- Fixed crash with jsonfieldkeyisnull lookup.