[Django] #35718: Some useful postgres functions for JSON are missing from contrib

[Django]

#35718: Some useful postgres functions for JSON are missing from contrib
-----------------------------+--------------------------------------------
Reporter: john-parton | Type: New feature
Status: new | Component: contrib.postgres
Version: dev | Severity: Normal
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-----------------------------+--------------------------------------------
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
* jsonb_build_array
* jsonb_array_elements
* jsonb_extract_path
* jsonb_extract_path_text
* jsonb_array_length

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.
--
Ticket URL: <https://code.djangoproject.com/ticket/35718>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#35718: Some useful postgres functions for JSON are missing from contrib

----------------------------------+--------------------------------------
Reporter: john-parton | Owner: (none)

Type: New feature | Status: new
Component: contrib.postgres | Version: dev

Severity: Normal | Resolution:

Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

----------------------------------+--------------------------------------
Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/35718#comment:1>

[Django]

#35718: Some useful postgres functions for JSON are missing from contrib
----------------------------------+--------------------------------------
Reporter: john-parton | Owner: (none)
Type: New feature | Status: new
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed

Has patch: 1 | Needs documentation: 1
Needs tests: 1 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
----------------------------------+--------------------------------------

Changes (by john-parton):

* has_patch: 0 => 1
* needs_better_patch: 0 => 1
* needs_docs: 0 => 1
* needs_tests: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/35718#comment:2>

[Django]

#35718: Some useful postgres functions for JSON are missing from contrib

----------------------------------+---------------------------------------
Reporter: john-parton | Owner: john-parton
Type: New feature | Status: assigned

Component: contrib.postgres | Version: dev
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 1
Needs tests: 1 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0

----------------------------------+---------------------------------------
Changes (by john-parton):

* owner: (none) => john-parton
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/35718#comment:3>

[Django]

#35718: Some useful postgres functions for JSON are missing from contrib
----------------------------------+---------------------------------------
Reporter: john-parton | Owner: john-parton
Type: New feature | Status: assigned
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 1
Needs tests: 1 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
----------------------------------+---------------------------------------

Comment (by 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`
[https://docs.djangoproject.com/en/5.1/topics/db/queries/#module-
django.db.models.fields.json 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
--
Ticket URL: <https://code.djangoproject.com/ticket/35718#comment:4>

[Django]

#35718: Some useful postgres functions for JSON are missing from contrib
----------------------------------+---------------------------------------
Reporter: john-parton | Owner: john-parton
Type: New feature | Status: assigned
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 1
Needs tests: 1 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
----------------------------------+---------------------------------------

Comment (by 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`.
--
Ticket URL: <https://code.djangoproject.com/ticket/35718#comment:5>

[Django]

#35718: Some useful postgres functions for JSON are missing from contrib
----------------------------------+---------------------------------------
Reporter: john-parton | Owner: john-parton
Type: New feature | Status: assigned
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 1
Needs tests: 1 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
----------------------------------+---------------------------------------

Description changed by john-parton:

Old description:

> 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
> * jsonb_build_array
> * jsonb_array_elements
> * jsonb_extract_path
> * jsonb_extract_path_text
> * jsonb_array_length
>
> 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.

New description:

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.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/35718#comment:6>

[Django]

#35718: Some useful postgres functions for JSON are missing from contrib
----------------------------------+---------------------------------------
Reporter: john-parton | Owner: john-parton
Type: New feature | Status: assigned
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 1
Needs tests: 1 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
----------------------------------+---------------------------------------

Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/35718#comment:7>

[Django]

#35718: Some useful postgres functions for JSON are missing from contrib
----------------------------------+---------------------------------------
Reporter: john-parton | Owner: john-parton
Type: New feature | Status: assigned
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 1
Needs tests: 1 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
----------------------------------+---------------------------------------

Comment (by 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
[https://forum.djangoproject.com/c/internals/5 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:
[https://dev.mysql.com/doc/refman/8.4/en/json-creation-functions.html
#function_json-array MySQL JSON_ARRAY],
[https://www.sqlite.org/json1.html#jarray SQLite json_array],
[https://docs.oracle.com/en/database/oracle/oracle-
database/23/sqlrf/JSON_ARRAY.html 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
--
Ticket URL: <https://code.djangoproject.com/ticket/35718#comment:8>

[Django]

#35718: Some useful postgres functions for JSON are missing from contrib
----------------------------------+---------------------------------------
Reporter: john-parton | Owner: john-parton
Type: New feature | Status: assigned
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 1
Needs tests: 1 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
----------------------------------+---------------------------------------

Comment (by john-parton):

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

I think the most compelling and obvious one is definitely `JSONArray`.
--
Ticket URL: <https://code.djangoproject.com/ticket/35718#comment:9>