Add new key with value = NULL to existing JSON object

I have the following simple JSON object:

{
    "application": {
        "status": "XXX"
    }
}

I’d like to add a new key under application called “approve_date” and would like the value to be NULL. Is there a way to do this with JSON_MODIFY. I tried using ‘append’ in the path but this added an array, not a key (example below).

JSON_MODIFY(data, 'append $.application.approve_date', null)

What is the better way to (A) add a new key to the JSON and (B) make it a NULL value?

Answer

The behavior of NULL varies with mode, so, one trick is to use JSON_MODIFY with a combination of lax (to add a key) and strict mode (to set the vale to null).

SELECT
    JSON_MODIFY(
        JSON_MODIFY(
            '{"application":{"status":"XXX"}}',
            '$.application.approve_date',
            ''
        ), -- {"application":{"status":"XXX","approve_date":""}}
        'strict $.application.approve_date',
        NULL
    ) -- {"application":{"status":"XXX","approve_date":null}}