Removing an element in an MySQL's JSON array in a column

user_16739808 0
Viewed 46

I have a "table" named users with a column name "group_in" which stores an array like [1,2,3].

I am trying to remove an element with a value (eg. 1) for a specified row (eg id:1).

Before:

id| group_in  
1 | **[1,2,3]**  
2 | [1,3]

After:

id |  group_in  
1  |  **[2,3]**  
2  |  [1,3]

I have tried the following:-

Update users 
SET group_in = JSON_REMOVE(group_in,JSON_UNQUOTE(JSON_search(group_in, 'one', 1)))
where id = 1

but I got back is

id |  group_in  
1  |  null           
2  |  [1,3]

Screenshot of my table and query result for your reference.
My table
Result gotten

Please help me if you know how to solve it

Thank you

mysql
2 Answers

Try this,

UPDATE `channels` SET `group_in` = JSON_REMOVE(`group_in`, '$[2]') WHERE `id` = 1 ;

Click Here To See Output Snapshots

user_83002490

I believe JSON_SEARCH works on strings, not sure, if it was extended to integer searches as well.

One way is to flatten the array and then recombine it while excluding the value as needed.

Query -

update users set group_in = (select new_grp from (
 select id,json_arrayagg(grp) new_grp from users, 
 JSON_TABLE(group_in, "$[*]" COLUMNS(grp INT PATH '$')) as grp_id
 where id=1 
  and grp<>1 
  group by id
 )X
)
where id=1;

Refer fiddle here.

user_139153130
Related