Opened 3 months ago

Last modified 2 weeks 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: Sage Abdullah Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by john-parton)

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 as django.db.models.functions.JSONObject
  • jsonb_build_array
  • jsonb_array_elements
  • jsonb_extract_path Functionally equivalent to the #> operator
  • jsonb_extract_path_text Functionally equivalent to the #>> operator
  • jsonb_array_length

There also a few operators that I implemented as django functions

jsonb
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 (14)

comment:1 by john-parton, 3 months ago

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.

comment:2 by john-parton, 3 months ago

Has patch: set
Needs documentation: set
Needs tests: set
Patch needs improvement: set

comment:3 by john-parton, 3 months ago

Owner: set to john-parton
Status: newassigned

comment:4 by Simon Charette, 3 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 john-parton, 3 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 john-parton, 3 months ago

Description: modified (diff)

comment:7 by john-parton, 3 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 Sarah Boyce, 3 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 john-parton, 3 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 john-parton, 3 months ago

Description: modified (diff)
Summary: Some useful postgres functions for JSON are missing from contribjson_array missing from django.db.models.functions

comment:11 by john-parton, 3 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 Sarah Boyce, 2 months ago

Component: contrib.postgresDatabase layer (models, ORM)
Summary: json_array missing from django.db.models.functionsAdd JSONArray Func
Triage Stage: UnreviewedAccepted

Thank you

comment:13 by john-parton, 2 weeks ago

Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset

I'm pretty happy with the current state of this patch. Would love any constructive feedback, but I think it's good to go.

comment:14 by Sarah Boyce, 2 weeks ago

Cc: Sage Abdullah added
Note: See TracTickets for help on using tickets.
Back to Top