#29157: Allow querying for distinct values in JSONField lists
-----------------------------------+------------------------------------
Reporter: Hrishikesh Barman | Owner: (none)
Type: New feature | Status: new
Component: contrib.postgres | Version: 2.0
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-----------------------------------+------------------------------------
Comment (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) where value ->>
'b' = '11';
size
------
(0 rows)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29157#comment:4>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/067.1020f6e5843f0702447f92446ad98ffb%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
