MySQL 5.7.8 JSON merges new data

Question

I am trying to create a comment / comment system for the admin area with new MySQL JSON support. Comments should be editable, and I would like to add support for other things in the future, possibly attachment files (only the file itself will not store the file path in JSON!).

{ "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 it would be possible to combine new data similar to array_merge () without having to configure a specific key each time.

This request works, but it targets only one, textual comment content. If I would like to add / edit tags, attachments of images or files, etc., Then I will need a very long request or several requests.

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

I tried using the JSON_REPLACE and JSON_SET functions with JSON_OBJECT, but it overwrites all the keys that are not set, which means that user_id, datecreated and any sub-comments are 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 request frankenstein almost works, but it actually concatenates the updated comment to 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 JSON using MySQL or to target $.comments[1].comment , $.comments[1][0].user_id , etc. the only way?

Answer 1

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;