Some useful postgres functions for JSON are missing from contrib

[john-parton]

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.