Modify property in a JSON array of objects

Question

Let's say I have a JSON column comments in a table posts, where I store an array of objects with 2 properties : at with the datetime of creation and content as the comment's content.

I'd like to replace the content of every comments by a static string 'Lorem ipsum ...'.

I've tried many queries, such as

UPDATE `posts`
SET `comments` = JSON_SET(`comments`, '$."*".content', 'Lorem ipsum ...')

... without success. It is somethong possible with MySQL 5.7, in a single query ? Here is a DBFiddle https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=bc9c37083546350fa2de6fe00b463eda

I'd like to keep history of comments. My real use/case is not about posts and comments. I need to erase the content in an anonymization process. — AlterPHP, Jul 19 '21 at 14:46

score 1 · Answer 1 · answered Jul 19 '21 at 14:20

Let's try:

mysql> update posts set comments = json_set(comments, '$[*].content', 'Lorem ipsum');
ERROR 3149 (42000): In this situation, path expressions may not contain the * and ** tokens.

No, the error message is clear. You can't use a wildcard here. You must use a JSON path that identifies a single element.

If you want to set multiple elements, JSON_SET() allows multiple arguments.

update posts set comments = json_set(comments,     
     '$[0].content', 'Lorem ipsum',     
     '$[1].content', 'Lorem ipsum',     
     '$[2].content', 'Lorem ipsum',     
     '$[3].content', 'Lorem ipsum',     
     '$[4].content', 'Lorem ipsum',     
     '$[5].content', 'Lorem ipsum',     
     '$[6].content', 'Lorem ipsum');

Is there a way to do this in one path expression, or to automatically populate the arguments to the max number of elements in the array? No.

The truth is, you are using SQL inappropriately. You could do this operation easily if you did not use JSON, but instead stored comments in a normalized manner, one comment per row in a second table, referencing the post.

CREATE TABLE comments (
  comment_id INT AUTO_INCREMENT PRIMARY KEY,
  post_id INT NOT NULL,
  content TEXT NOT NULL
);

INSERT INTO `comments` (`post_id`, `content`) VALUES
(1, 'content'),
(1, 'content'),
(1, 'content'),
(1, 'content'),
(1, 'content'),
(1, 'content'),
(2, 'content'),
(2, 'content'),
(2, 'content'),
(2, 'content');

Now it's easy to use SQL to make the update you described to all comments:

UPDATE comments SET content = 'Lorem ipsum';

JSON may seem like an attractive feature of MySQL 5.7 & 8.0, but in practice it makes most queries much harder.

Modify property in a JSON array of objects

1 Answers1