Mysql update json if-else exist

Question

I want to update my raw with if-else condition. Here is my sql code;

update posts
    set like = 
      if(json_contains(like,2,'$'),
          json_remove(like,replace(json_search(like,'one',2),'"','')),
          json_array_append(like,'$',2)
        )
     where id = 3

My table;

id	like
2	[]
3	[2]

After this sql code id= 3 should be like id = 2.
To sum up,: if json contain, remove from json; if json not contain, push data to array

Answer 1

The problem is that JSON_SEARCH() doesn't work with integers. You must use strings.

mysql> set @like = '["2"]';

mysql> select if(json_contains(@like,'["2"]','$'),
  json_remove(@like,json_unquote(json_search(@like,'one','2'))),
  json_array_append(@like,'$','2')) as `like`;
+------+
| like |
+------+
| []   |
+------+

mysql> set @like = '[]';

mysql> select if(json_contains(@like,'["2"]','$'),
  json_remove(@like,json_unquote(json_search(@like,'one','2'))),
  json_array_append(@like,'$','2')) as `like`;
+-------+
| like  |
+-------+
| ["2"] |
+-------+

Using JSON in MySQL makes everything more difficult. I recommend instead you store your likes in a second table. Then just INSERT a row to add a like, or DELETE a row to remove a like.