I'm managing a followers list through a JSON array. I'm using JSON_ARRAY_APPEND to add a follower:
UPDATE users
SET follows = JSON_ARRAY_APPEND(follows, '$', "followerToBeAdded")
WHERE username = "user"
However, I'm unable to remove a follower from this list. If I try JSON REMOVE, it just erases the whole JSON array instead of just removing a particular follower from the list.
Any idea of a possible query for it?
As you've added the MySQL tag in the question so the following response is inaccordance with MySQL.
Let's consider the users table with the two columns username and json column follows. Assume we've a username x and one follower a to begin with.
Let's add 2nd and 3rd follower
UPDATE users SET follows = JSON_ARRAY_APPEND(follows, '$', 'b') where username ='x';
UPDATE users SET follows = JSON_ARRAY_APPEND(follows, '$', 'c') where username ='x';
Removal: Let's say we want to remove the follower b
To check the follower to be removed
SELECT JSON_SEARCH(follows, 'one', 'b') FROM users;
Above will give path to the object to be deleted in the array.
UPDATE users SET follows = JSON_REMOVE(follows, JSON_UNQUOTE(JSON_SEARCH(follows, 'one', 'b'))) WHERE JSON_SEARCH(follows, 'one', 'b') IS NOT NULL;
