Mysql update json if-else exist

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply