MySQL JSON_REMOVE json array

Question

I have channels table:

+----+-------------------+---------+
| id | sort              | bouquet |
+----+-------------------+---------+
|  1 | ["1","2","3","4"] | ["1"]   |
|  2 | ["4"]             | ["4"]   |
+----+-------------------+---------+

And need to remove "2" value from id 1 so i need to get this:

+----+-------------------+---------+
| id | sort              | bouquet |
+----+-------------------+---------+
|  1 | ["1","3","4"]     | ["1"]   |
+----+-------------------+---------+

I try using this query:

SELECT id, sort, bouquet, JSON_REMOVE(sort, '$."2"') FROM channels WHERE id=1;

But value is not removed if i use '$[2]' then value is removed but i need to remove by value not index...does anyone knows how to remove from json array specific value?

One option is to use 14.5 Prepared SQL Statement Syntax. See db-fiddle. — wchiquito
– wchiquito, Commented May 13, 2017 at 19:25
thanks it works...but i use above solution because it is more user friendly to me...but your works as expected — John
– John, Commented May 13, 2017 at 20:00

wchiquito · Accepted Answer · 2017-05-13 19:49:08Z

8

Try:

SELECT
  `id`,
  `sort`,
  `bouquet`,
  JSON_REMOVE(`sort`,
              JSON_UNQUOTE(
                JSON_SEARCH(`sort`, 'one', 2)
              ))
FROM `channels`
WHERE `id` = 1;

See db-fiddle.

edited May 13, 2017 at 19:49

answered May 13, 2017 at 19:34

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

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

2 Comments

Saroj Shrestha Over a year ago

I am using this JSON_REMOVE to remove a value on a JSON_COLUMn but it seems to update the column value to null if it can't find the value. Actually, in the above case when if user search for 5 shouldn't it return the original value ["1","2","3","4"]. Could you help with that??

wchiquito Over a year ago

@SarojShrestha: Try ... COALESCE(JSON_REMOVE(...), `sort`) ..., see db-fiddle.

Rajshekhar Rajaharia · Accepted Answer · 2017-10-01 14:08:19Z

1

Try This

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

Working for me

answered Oct 1, 2017 at 14:08

Rajshekhar Rajaharia

294 bronze badges

1 Comment

wchiquito Over a year ago

Try `sort` = ["1","4","3","2"].

Collectives™ on Stack Overflow

MySQL JSON_REMOVE json array

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related