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

Question

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 🙏

Pankaj · Accepted Answer · 2022-08-23 23:10:12Z

1

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.

answered Aug 23, 2022 at 23:10

Pankaj

2,7762 gold badges9 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

R.G.Krish · Accepted Answer · 2022-08-23 22:23:13Z

1

Try this,

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

edited Aug 23, 2022 at 22:23

answered Aug 23, 2022 at 22:16

R.G.Krish

5075 silver badges23 bronze badges

1 Comment

Royston Lau Over a year ago

Thanks RGKrish183. I forgot to mentioned that it need to search the array for the value that I want. I need to remove the element that has a value 1 for example.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related