1

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?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Miguel Stevens
  • 8,631
  • 18
  • 66
  • 125

1 Answers1

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
JensV
  • 3,997
  • 2
  • 19
  • 43
  • The ? is the data that I want to overwrite, but in your example the first parameter is also that data. Should I omit the final '?' ? – Miguel Stevens Jan 27 '22 at 15:45
  • @MiguelStevens `?` seems to be your payload. The first parameter will get overridden by anything in `settings` which will get overwritten by `?`. In theory, what you want to achieve would also be possible by supplying `...PATCH(?, settings, ?)` where both `?` are your payload. – JensV Jan 27 '22 at 19:46
  • Thanks for your comment, using the method above, however, still removes all items that have a value of null in my payload – Miguel Stevens Jan 28 '22 at 08:44
  • @MiguelStevens you're right, I misinterpreted the docs. If the right hand side contains any null value for a key, that key will be removed. I not able to find any mysql function which have the behavior you desire... It may be possible with some custom made function but at that point, you might as well do it in JS... – JensV Jan 28 '22 at 09:25
  • 1
    @MiguelStevens added some alternate solutions which might solve the root problem – JensV Jan 28 '22 at 09:43
  • 1
    I came up with the exact same solution, using JSON_SET and handling each row separately. I'll set your answer as accepted! Thanks – Miguel Stevens Jan 28 '22 at 13:19