A few notes real quick:

I chose to use an enum rather than a simple boolean keyword argument to mirror the SQL

I put the enum on the JsonArray class, because I didn't think it had any use outside of JSON_ARRAY. I actually am seeing that's not true. It looks like JsonObject has a similar clause: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_OBJECT.html I could expand this pull request to add the functionality to JsonObject as well. I don't think it would be particularly difficult.

Docs were a pretty rough copy/paste/tweak job.

I added a new DB feature. I originally just did some basic checks inside the class, but it made it basically impossible to test cleanly.

It looks like the biggest issue with adding an on_null clause to JsonObject is that it already accepts variadic **kwargs, so in the case that you want to make a json object that has an on_null key, you wouldn't be able to do it.

The closest parallel I can think of in the existing database is with get_or_create where if you want to do a lookup on a field called defaults, you must specify defaults__exact

As an aside, my version of JSONObject I made long ago didn't take **kwargs, but rather you were supposed to pass everything as arguments

So instead of

JSONObject(
    key="value"
)

You would do

JSONObject(
    Value("key"), F("value")
)

Which is obviously more verbose, but it let you create an object where one of the keys was actually dynamic from another column.

It would also resolve the ambiguity of providing an absent_on_null flag, because it didn't take any keyword arguments.

I'm not exactly sure how that would resolve the issue with current django, though.

Another thought would be to have an alternate constructor as a classmethod

JSONObject.absent_on_null(**kwargs)

Or having to manually set a prop.

obj = JSONObject(...)
obj.absent_on_null = True

But the prop is error-prone and requires you to create a temporary variable.

I guess another solution is to just make it so you can't build objects with absent_on_null as a key and passing anything other than a bool raise an error, and then just document it. "Sorry, why would you even do this?"

I'd focus this PR on JSONArray and see what can be done for JSONObject in a distinct ticket/PR?

A potential solution could be to require that fields are provided as a list[str, Resolvable] when nulls exclusion is requested so allowed signatures would be of the form

@overload
def __init__(self, fields: list[str, Resolvable], *, exclude_nulls: bool = False): ...

@overload
def __init__(self, **fields: Resolvable): ...

def __init__(self, *args, **kwargs):
    if len(args) > 1:
        raise TypeError
    elif args:
        fields = dict(args)
        exclude_nulls = kwargs.pop("exclude_nulls", False)
        if kwargs:
            raise TypeError
    else:
        fields = kwargs

It also seems like a less useful feature for JSONObject than it is for JSONArray or even JSONArrayAgg IMO.

I'd focus this PR on JSONArray and see what can be done for JSONObject in a distinct ticket/PR?

A potential solution could be to require that fields are provided as a list[str, Resolvable] when nulls exclusion is requested so allowed signatures would be of the form

@overload
def __init__(self, fields: list[str, Resolvable], *, exclude_nulls: bool = False): ...

@overload
def __init__(self, **fields: Resolvable): ...

def __init__(self, *args, **kwargs):
    if len(args) > 1:
        raise TypeError
    elif args:
        fields = dict(args)
        exclude_nulls = kwargs.pop("exclude_nulls", False)
        if kwargs:
            raise TypeError
    else:
        fields = kwargs

It also seems like a less useful feature for JSONObject than it is for JSONArray or even JSONArrayAgg IMO.

Ack, I wish the type annotations were just in django. That's so clear.

It's funny, I was hacking on pretty much exactly this solution , minus type annotations.

I removed array from the DB feature and added a comment that it's supposed to indicate support for the clause in both array and object contexts.

I think that the ability to provide key-value pairs to JSONObject explicitly is a very esoteric thing. I only did it in my application because I just generally make the functions look as close to the SQL as possible, ergonomics be damned.

As an example esoteric usage, if you're trying to get the database to omit valid objects for the Elasticsearch DSL, you would almost certainly need to be able to build objects with non-static keys.

For example https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-term-query.html

The key "user.id" refers to a field in elastic.

I think if you proposed this to me as a reasonable use of Django, I would think you're crazy, but that's one use off the top of my head.

I can create a feature issue if desired.

Looks like MySQL tests are failing. I think it's because it doesn't use the "as_native" definition where the code is.

I'll take a closer look.

Right, because MySQL doesn't support it. Duh. Toggled the value on the backend. Need to update the docs.

I am wondering if a workaround for the non-native backends would be worth it. I know a lot of bending over backwards is done for some functions and backends.

Alright, thought about it for a bit and decided the workaround wasn't as nasty as I thought it might be, at least for MySQL and Postgres 15.

I have absolutely no idea how to do it on SQLite. I believe it would require a subquery and an aggregate, or something really, really weird. Probably not safe to do it automatically.

If we go with this workaround version, I think we can just get rid of the supports_json_absent_on_null field completely, and just move it to be a check for SQLite.

Alright, last suite of changes:

• Removed DB feature, because all the database backends except SQLite now have similar behavior
• Reworked tests for If/Unless to check for SQLite, instead of DB feature
• Updated docs to remove note that Postgres 15 doesn't work

Polishing:

• There was some imports inside a method to avoid a circular import, but they were a 'shortcut' import from a parent module. Explicitly importing from where classes were defined removed the need for that.
• There was a small duplication of a code in as_postgres and as_mysql to handle the case where there are no expressions. I moved it into the workaround method to keep it DRY, but it does result in some complicated super() invocations that might be difficult to reckon with. I believe there needs to be more test coverage for when expressions == [] and absent_on_null == True

Few extra tests added, tests reorganized a bit:

• Test absent_on_null with an empty array
• Test absent_on_null with two elements, one null and one non-null
• Test absent_on_null with one element, both the case of a null element and non-null element

I think that's should be sufficient to exercise all of the branches in the workaround.

Additional changes:

• Restored the feature flag that an earlier version had. 3rd party backends will need to reach out if there's a backend that only has partial support for ABSENT ON NULL across the various functions.
• Merged the two test classes again. I realized the class I created didn't have any setup, so I just squashed them back together
• applied isort. I'll try to be more fastidious with this. I'm just used to my commit hooks and ruff
• Squashed/merged

Note: I am still missing a release note. What version of Django would this be targeting? Should be able to make it into 5.2, I would expect.

@john-parton 5.2 is feature frozen at this point so that would be 6.0.

Added a release note. Tweaked the docs to have a versionadded note.

I'm not the best at getting docs formatted exactly right, so some help there would be appreciated.

I should probably stop iterating, but I'm a bit obsessed at the moment, apparently.

The workaround that I had was pretty nasty in that mysql and postgres had to pass specific arguments to the method in order so that it could do the right concatenation operation.

I just pulled out the concatenation logic into its own function, and then there's actually no database-specific logic in the main workaround now.

I prepended an underscore to the function to indicate that it's private. If it's used incorrectly, it could cause pain. In particular, there's no attempt made to make sure that the expressions will actually resolve to arrays, and if you pass objects, the behavior is backend dependent.

Pulling the concatenation into its own function does let me write tests directly against that, which I have done.

Made the requested changes, rebased, force-pushed.

I also updated the "Admonition" section of the docs to properly reflect that usage of absent_on_null=True is not supported on MySQL, MariaDB and PostgreSQL versions prior to 16. Let me know if any changes are required there.

It seems like a pretty bad hack to use json loads/dumps in the SQLite function. Bad code smell. Let me know if there's a more straightforward or better way of handling that.

I ran tests locally against SQLite and Postgres.

Rebased, force-pushed. Summary of changes:

• Changed commit message to be entirely in the past tense.
• Replaced the technique of defining a json_array_absent_on_null SQLite function with a json_strip_nulls function which wraps the standard json_array function. Implemented only the case where NULL or a JSON Array is passed to the function.
• Changed my annotation. I annotated by new tests with json_array. Changed it to arr to match existing tests.
• Added a new test with nested arrays.

Does anyone know why tests failed? I'm unable to understand the error message.

Can we retrigger tests?

It's issues with the CI infra, force-pushing an amended commit should do assuming the problem was transient.

Amended the commit and force-pushed. Checks are going now.

We're getting django.db.utils.OperationalError: user-defined function raised exception on Windows SQLite, but not Linux. That's interesting. Maybe it has to do with the internal encoding of json objects? Perhaps they are bytestrings on windows instead of normal strings?

Side-note: I really wish an exception here could provide a traceback or some more context. I don't have a Windows machine, so this makes it really difficult for me to continue development.

I looked again, and there's not a dedicated Linux/SQLite entry in the test matrix. It was just SQLite failing due to a mistake on my part. I've fixed it, ran the test suite locally again to 100% be sure we're good to go, and pushed again.

Please be patient while I un-break my fork. I foolishly decided to try to do something on my phone.