JSON_MERGE_PATCH with null values (in Javascript)

Question

As written in the docs, JSON_MERGE_PATCH will remove each value that is set to null, the following example will remove the header value from my settings json field

const data = JSON.stringify({header: null, otherdata: ...})
await connection.query(UPDATE shops SET JSON_MERGE_PATCH(settings, ?), data)

However what if I want to set the value to null, If I surround the header: 'null', with quotes, you can guess it: it enters 'null' as a string into my database.

Does anyone know if it's possible to have mysql update my json field with a null value?

Answer 1

As there doesn't seem to be a pure MySQL solution for this problem, you might be better off implementing this in JavaScript.

You'd implement this something like this:

1. Fetch all records you want to modify some ID
2. Use a solution like How can I merge properties of two JavaScript objects dynamically? to merge the objects
3. Update all records with the new value

An alternate approach could be to use JSON_SET for each object key you have:

UPDATE shops SET JSON_SET(settings, '$.header', null)
-- Then repeat for each json key you want to modify