json_array missing from django.db.models.functions

[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.

[john-parton]

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.

[Simon Charette]

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

[john-parton]

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.

[john-parton]

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.

[Sarah Boyce]

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

[john-parton]

Thanks for the input. I sometimes forget the forum exists.

I think the most compelling and obvious one is definitely JSONArray.