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?
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:
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