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?

Oh man, I just can feel your pain. I can not understand how such a functions is not part of the provided json functions. It's such a basic need! — EscapeNetscape, Aug 24 '17 at 19:39

score 1 · Answer 1 · answered Nov 29 '19 at 14:35

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;

score 0 · Answer 2 · answered Dec 15 '17 at 13:47

This is a very late answer, but still - you can do it like this:

create table sampl_test(id int, comments json);
insert into sampl_test values(1,
'{
    "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"
                }
            ]
        }
    ]
}
')
;

select json_merge('{"comments" : {"comment" : "This is a test comment" }}', comments)

from sampl_test;

MySQL 5.7.8 JSON merge new data

2 Answers2