Allow querying for distinct values in JSONField lists.

[Hrishikesh Barman]

We can access JSONField Data (​https://docs.djangoproject.com/en/2.0/ref/contrib/postgres/fields/#django.contrib.postgres.fields.JSONField)
like this Dog.objects.filter(data__owner__name='Bob')

But for JSONField consisting of a list this does not work.

[{'a':12,'b':33},{'a':44,'b':99}]

Filtering values like this works:

Frame.objects.filter( 
size__contains=[{'a': 12,'b': 33}]
)

But there is no way to get distinct values from a list in a JSONfield, for eg. the following does not work.

Frame.objects.values( 'size__a')

Should there be an implementation of the same?

[Tim Graham]

I'm not sure if it's feasible. Do you know if the query can be expressed in SQL?

[Tim Graham]

Accepting for investigation if someone is interested, although I'm not sure what change or if a change should be made here.

[Hrishikesh Barman]

Replying to Tim Graham:

Accepting for investigation if someone is interested, although I'm not sure what change or if a change should be made here.

I'll try working on this, but I won't tag myself on owned for now. But will be checking what can be done.

[Dmitry Dygalo]

The query could be expressed in this way:

# SELECT jsonb_array_elements('[{"a": 12, "b": 33}, {"a": 44, "b":99}]'::jsonb) ->> 'a' AS "size";
 size
------
 12
 44
(2 rows)

Also, jsonb_array_elements will fail if the value is not an array.

# SELECT jsonb_array_elements('{"x": [{"a": 12, "b": 33}, {"a": 44, "b":99}]}'::jsonb) ->> 'a' AS "size";
ERROR:  cannot extract elements from an object

Using it in the WHERE clause is a bit tricky:

# SELECT '[{"a": 12, "b": 33}, {"a": 12, "b":99}]'::JSONB AS "size" INTO TEMPORARY test;
SELECT 1
# SELECT test.* FROM test, jsonb_array_elements(test.size) where value ->> 'b' = '99';
                   size
------------------------------------------
 [{"a": 12, "b": 33}, {"a": 12, "b": 99}]
(1 row)
# SELECT test.* FROM test, jsonb_array_elements(test.size) elems where elems ->> 'b' = '11';
 size
------
(0 rows)

Or like this:

# SELECT * FROM test WHERE '33' = ANY(SELECT jsonb_array_elements(test.size) ->> 'b');
                   size
------------------------------------------
 [{"a": 12, "b": 33}, {"a": 12, "b": 99}]

[Joey Wilhelm]

I have a similar use case. Using the following model...

class Request(models.Model):
    request_data = JSONField()
    state = models.CharField(max_length=255)

And JSON data

{"target": {"pk": 1}}

I'm able to issue a SQL query like this:

SELECT DISTINCT(request_data -> 'target' -> 'pk') FROM myapp_request WHERE state = 'in_progress';

I would expect the equivalent ORM code to look like:

Request.objects.filter(state=Request.STATE_IN_PROGRESS).values('request_data__target__pk').distinct()

OR

Request.objects.filter(state=Request.STATE_IN_PROGRESS).annotate(target=F('request_data__target__pk')).values('target').distinct()

But both produce this error:

django.core.exceptions.FieldError: Cannot resolve keyword 'target' into field. Join on 'request_data' not permitted.

[Mariusz Felisiak]

Using key transforms in .values() and .distinct() was fixed in #24747. I'm not sure if it's feasible to have querying for distinct values in JSONField lists, e.g.
to get <QuerySet [{'value__a': 12}, {'value__a': 'b'}]> for

>>> JSONModel.objects.create(value=[{"a": 12, "b": 33}, {"a": 12, "b":99}])
>>> JSONModel.objects.create(value={'a': 'b', 'c': 14})

Closing as needsinfo. I'm happy to reopen if we will get a PoC.