Opened 6 months ago

Last modified 6 weeks ago

#29157 new New feature

Allow querying for distinct values in JSONField lists

Reported by: Hrishikesh Barman Owned by:
Component: contrib.postgres Version: 2.0
Severity: Normal Keywords:
Cc: Joey Wilhelm Triage Stage: Accepted
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 (5)

comment:1 Changed 6 months ago by Tim Graham

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 Changed 6 months ago by Tim Graham

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.

comment:3 in reply to:  2 Changed 6 months ago by 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.

comment:4 Changed 5 months ago by 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}]
Last edited 5 months ago by Dmitry Dygalo (previous) (diff)

comment:5 Changed 6 weeks ago by Joey Wilhelm

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.
Note: See TracTickets for help on using tickets.
Back to Top