Update operation with Mysql JSON data

Since the initial value of '$.categories' would be a empty string due to a application bug, we have to remove it before appending any item into the categories array.

UPDATE subscriber
SET attributes = JSON_REMOVE(attributes, '$.categories') 
WHERE  mail_address = 'test_user@mail.com' AND attributes->'$.categories' = '' ;

Using JSON_MERGE because JSON_ARRAY_APPEND couldn't work with a non-existing field.

UPDATE subscriber
SET attributes = JSON_MERGE(attributes, '{"categories": ["mem", "boy"]}')
WHERE mail_address = 'test_user@mail.com'
AND attributes->'$.categories' is NULL

UPDATE subscriber
SET attributes = JSON_ARRAY_APPEND(attributes, '$.categories', 'women')
WHERE mail_address = 'test_user@mail.com'
AND attributes->'$.categories' <> '' ;

https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html