MySQL 5.7.8 JSON merge new data

Question

I'm trying to make a notes / comments system for an admin area with the new MySQL JSON support. Comments need to be editable and I wanted to add support for other things in the future, maybe file attachments (would store the filepath in JSON only not the file itself!).

    {
  "comments": [
    {
      "comment": "This is a comment",
      "user_id": 5,
      "datecreated": "2016-03-19"
    },
    {
      "comment": "This is a comment",
      "user_id": 1,
      "datecreated": "2016-03-19"
      "comments": [
        {
          "comment": "This is a sub-comment",
          "user_id": 4,
          "datecreated": "2016-03-19"
        },
        {
          "comment": "This is a sub-comment",
          "user_id": 4,
          "datecreated": "2016-03-19"
        }
      ]
    }
  ]
}

I thought there would be a way to merge in new data similar to array_merge() without needing to target a particular key every time.

This query works but it targets only one thing, the comment's text content. If I wanted to add/edit tags, image or file attachments etc. then I'd need a very long query or several queries.

UPDATE shared_notes SET json = JSON_REPLACE(json, "$.comments[1].comment", "This is a test comment") WHERE note_id = :note_id

I tried using JSON_REPLACE and JSON_SET functions with JSON_OBJECT but it overwrites all keys that aren't specified, which means user_id, datecreated and any sub comments get overwritten.

UPDATE shared_notes SET json = JSON_REPLACE(json, "$.comments[1]", JSON_OBJECT("comment", "This is a test comment") ) WHERE note_id = :note_id

This frankenstein of a query almost works but it actually concatenates the updated comment onto the end of the old one:

UPDATE shared_notes SET json = JSON_SET(json, "$.comments[1]", JSON_MERGE(JSON_EXTRACT(json, "$.comments[1]"), CAST('{"comment":"Test"}' AS JSON) ) ) WHERE note_id = :note_id

So is there a better way to easily / dynamically update the JSON using MySQL or is targeting $.comments[1].comment, $.comments[1][0].user_id etc. the only way?

Answer 1

For future reference json_merge has been deprecated since 5.7.22 in favour of json_merge_preserve or json_merge_patch

so adding to @Adam Owczarczyk's answer:

{...}
select json_merge_preserve('{"comments" : {"comment" : "This is a test comment" }}', comments)
from sampl_test;