Opened 6 years ago

Closed 4 years ago

#29157 closed New feature (needsinfo)

Allow querying for distinct values in JSONField lists.

Reported by: Hrishikesh Barman Owned by:
Component: contrib.postgres Version: dev
Severity: Normal Keywords:
Cc: Joey Wilhelm, Fabian Köster Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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?

Change History (7)

comment:1 by Tim Graham, 6 years ago

Summary: Getting Distinct Values from List in a JSONFieldAllow querying for distinct values in JSONField lists

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

comment:2 by Tim Graham, 6 years ago

Triage Stage: UnreviewedAccepted

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

in reply to:  2 comment:3 by Hrishikesh Barman, 6 years ago

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.

comment:4 by Dmitry Dygalo, 6 years ago

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)
Version 1, edited 6 years ago by Dmitry Dygalo (previous) (next) (diff)

comment:5 by Joey Wilhelm, 6 years ago

Cc: Joey Wilhelm added

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.

comment:6 by Fabian Köster, 4 years ago

Cc: Fabian Köster added

comment:7 by Mariusz Felisiak, 4 years ago

Resolution: needsinfo
Status: newclosed
Summary: Allow querying for distinct values in JSONField listsAllow querying for distinct values in JSONField lists.
Triage Stage: AcceptedUnreviewed
Version: 2.0master

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.

Note: See TracTickets for help on using tickets.
Back to Top