Opened 3 months ago

Last modified 2 months 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: 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 (4)

comment:1 Changed 3 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 3 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 3 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 2 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 2 months ago by Dmitry Dygalo (previous) (diff)
Note: See TracTickets for help on using tickets.
Back to Top