CODEWITHSUNDEEP
mysqlsqlmariadb
Anon
Anon

Reputation: 35

Removal from JSON array in MySQL

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?

Upvotes: 0

Views: 238

Answers (1)

b.s
b.s

Reputation: 2755

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;

Upvotes: 1

Related Questions