mysqlでJSONデータの一部削除(splice的なやつ)

概要

mysqlでJSON形式のデータの一部削除を行いたくなったのでちょっと調べてみました。
結論を先に書いておくと、現時点では削除したい要素が「文字列」の場合のみ下記のやり方でできます。
後、重複ない想定をしてます。複数ある場合は、複数回update文を流す必要しかないのか・・・な？

やりかた

ObjectでもArrayでも操作は同じです。

検索値が文字列の場合

Object

SET @json_data = '{ "hoge": "HOGE", "fuga": "FUGA"}';
-- valueとして"HOGE"を持つ要素を消したい場合
SELECT @json_data as before_removing, 
JSON_REMOVE(
  @json_data, 
-- JSON_UNQUOTEでJSON_SEARCHの結果を整形してJSON_REMOVEで使えるようにする
  JSON_UNQUOTE(
-- JSON_SEARCHで該当のデータのindexを取得
    JSON_SEARCH(@json_data, 'one', "HOGE") 
  )
) as after_removing;

結果

before_removing	after_removing
{ "hoge": "HOGE", "fuga": "FUGA"}	{"fuga": "FUGA"}

Array

SET @array_data = '["1", "2", "3"]';
-- "2"を消したい場合
SELECT @array_data as before_removing, 
JSON_REMOVE(
  @array_data, 
  JSON_UNQUOTE(
    JSON_SEARCH(@array_data, 'one', "2")
  )
) as after_removing;

結果

before_removing	after_removing
["1", "2", "3"]	["1", "3"]

検索値が数値の場合

SET @array_data = '[1, 2, 3]';
-- 2を消したい場合
SELECT 
@array_data as before_removing, 
-- JSON_REMOVEの第2引数がnullの場合は、演算結果がnullになる
JSON_REMOVE(
  @array_data, 
  IFNULL(
    JSON_UNQUOTE(JSON_SEARCH(@array_data, 'one', 2)),
    CONCAT('$[', JSON_LENGTH( @array_data) + 1, ']') -- 検索結果が見つからない場合は、ありえない参照を渡す
  )
);

結果

before_removing	after_removing
[1, 2, 3]	[1, 2, 3]

・・・消えないだと!!!

調べてみたら、ここに

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

...search_strか。。。終わったorz
どうやら、JSON_SEARCHで検索できるのは文字列だけのようです。
もしかしたらそのうちできるようになるかも

おわりに

JSONのデータをsqlだけでいい感じに削除したかったのだけれど、文字列の場合しかうまくいかなそうでした。
まあ、そもそもRDBでJSON使うのどうなの問題はありそう。
JSONを使ったほうがいい？ケースやアンチパターンがいまいちまだわからない。