Opened 2 months ago
Last modified 2 months ago
#35718 assigned New feature
Add JSONArray Func
Reported by: | john-parton | Owned by: | john-parton |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Normal | Keywords: | |
Cc: | Triage Stage: | Accepted | |
Has patch: | yes | Needs documentation: | yes |
Needs tests: | yes | Patch needs improvement: | yes |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
This ticket was originally opened with the intent for me to provide implementations for a bunch of postgres functions, but it became clear from discussion that the most important bit was to implement a non-postgres-specific JSONArray function.
Original post:
There's a large number of postgres-specific functions for manipulating json: https://www.postgresql.org/docs/current/functions-json.html
In my projects for work, I have created several:
jsonb_build_object-- Actually already implemented asdjango.db.models.functions.JSONObject
- jsonb_build_array
- jsonb_array_elements
jsonb_extract_pathFunctionally equivalent to the#>
operatorjsonb_extract_path_textFunctionally equivalent to the#>>
operator- jsonb_array_length
There also a few operators that I implemented as django functions
jsonb -> jsonb - Concatenates two jsonb values |
- jsonb - text -> jsonb - Deletes a key (and its value) from a JSON object
It would be nice if these were included in contrib.postgres.functions
, ideally in a submodule like contrib.postgres.function.json
to allow room to expand.
I know postgres's json support is a bit of test-bed for more fleshed out json support in general, but I would recommend just adding things as postgres-specific to limit the scope of the changes.
I can open a basic pull request.
Change History (12)
comment:1 by , 2 months ago
comment:2 by , 2 months ago
Has patch: | set |
---|---|
Needs documentation: | set |
Needs tests: | set |
Patch needs improvement: | set |
comment:3 by , 2 months ago
Owner: | set to |
---|---|
Status: | new → assigned |
comment:4 by , 2 months ago
If we are going to add these functions they should support SQLite, MySQL, and Oracle as well otherwise they are trivial to implement using Func
solely for Postgres and they don't belong in core in my opinion. They should live in django.db.models.functions.json
.
jsonb_build_object
jsonb_build_array
jsonb_array_elements
Seems like good candidate.
jsonb_extract_path
jsonb_extract_path_text
Per the Postgres docs you linked
This is functionally equivalent to the #> operator, but writing the path out as a variadic list can be more convenient in some cases.
Already achievable using the __
syntax and KT
objects so not worth adding IMO. You only have to join your array as string.
jsonb_array_length
Also not convinced this is warranted given
comment:5 by , 2 months ago
My use of these functions predate the JSONField being moved out of contrib, so I'm certainly not saying that's the best way to do it.
My experience with Oracle is effectively zero, and my experience with SQLite and MySQL is very limited, so it might be challenging for me to complete that without some assistance from another contributor.
It turns out that jsonb_build_object
is already implemented as django.db.models.functions.JSONObject
. So that's actually done. That must have been added at some point and I didn't notice.
Another thing I've implemented is removing keys and concatenating objects:
class JSONConcat(Func): template = "%(expressions)s" output_field = JSONField() arg_joiner = " || " class JSONDeleteKey(Func): template = "%(expressions)s" output_field = JSONField() arg_joiner = " - "
Perhaps there is a better way to do this that I'm not seeing?
With respect to "we shouldn't include functions that are trivial to implement using Func", there are already two such functions in django.contrib.postgres.functions
, and arguably the functions CumeDist
, DenseRank
, FirstValue
, LastValue
, PercentRank
, Rank
, RowNumber
all from django.db.models.functions.window
.
comment:6 by , 2 months ago
Description: | modified (diff) |
---|
comment:7 by , 2 months ago
I'm pretty sure you can't use F
objects with KeyTransform
or KT
at this time.
If you use the functional jsonb_extract_path
, it's trivial to use an F object.
That might be a somewhat esoteric use, but I'm pretty sure that's why I have it as its own function in my codebase.
comment:8 by , 2 months ago
Thank you both for the discussion
When requesting new feature for Django, we recommend to first propose and discuss the idea with the community and gain consensus on the Django Forum.
There is a broader audience active on the forum (or the developers mailing list) and that helps to receive additional feedback and agree on the direction.
It would be nice for us to close out this discussion here, and migrate any open points to the forum, and any agreed items to perhaps new tickets (referring to this one).
If we're in agreement, I think we can create a ticket to add django.db.models.functions.JSONArray
, similar to django.db.models.functions.JSONObject
(#32179).
I think these might be equivalent: MySQL JSON_ARRAY, SQLite json_array, Oracle JSON_ARRAY.
The rest I think needs more investigation/discussion.
I thought json_array_elements
might be equivalent to JSON_EXTRACT
/ json_extract
for MySQL and SQLite respectively, but for Oracle I'm not sure (JSON_VALUE
maybe). I'm likely to be wrong and think it needs more looking into
comment:9 by , 2 months ago
Thanks for the input. I sometimes forget the forum exists.
I think the most compelling and obvious one is definitely JSONArray
.
comment:10 by , 2 months ago
Description: | modified (diff) |
---|---|
Summary: | Some useful postgres functions for JSON are missing from contrib → json_array missing from django.db.models.functions |
comment:11 by , 2 months ago
I closed out my previous pull request and opened a new one adding only the JSONArray function: https://github.com/django/django/pull/18541
comment:12 by , 2 months ago
Component: | contrib.postgres → Database layer (models, ORM) |
---|---|
Summary: | json_array missing from django.db.models.functions → Add JSONArray Func |
Triage Stage: | Unreviewed → Accepted |
Thank you
Pull request here: https://github.com/django/django/pull/18525
I think some additional discussion is warranted, but I'd be happy to flesh it out.