AABlog：So-netブログ

	ブログをはじめるログイン

JSONオブジェクトと第一正規形【その3 json_transform】　[SQLポケリ]

前回に続きRDBMSにおけるJSON型の話である。
前回では、JSONデータのうちJSON配列をRDBMSのテーブルのように変換する方法について解説した。
今回はOracleにおいてJSONデータを変更できるjson_transform関数を紹介する。

json_transform

OracleにおいてJSONデータを変更できる関数。
なんとselectで値を変更できる。マジか。

どういう変更ができるかというと

REMOVE	データを削除
KEEP	指定されたもの以外を削除
RENAME	フィールド名を変更
SET	値の変更もしくは追加
REPLACE	値の変更
INSERT	データの追加
APPEND	配列の末尾にデータを追加

といった感じである。
まぁ普通に配列の要素やフィールドの追加、削除、更新ができるのであろう。
編集対象はJSONパス式で指定するみたい。
パスで指定された要素やフィールドが存在しない場合の挙動は各サブコマンドで異なる。エラーにしたり無視したりするオプションがある。
まぁ、こんだけあればなんでもできるように思える。

REMOVE

順番にやってみるか。
この前作ったJSONデータで配列要素の0番目を削除してみるか。

SQL> select json_transform(j, remove '$[0]') from jfoo;

JSON_TRANSFORM(J,REMOVE'$[0]')
--------------------------------------------------------------------------------
[{"name":"suzuki","age":32},{"name":"honda","age":45}]
[{"name":"syouhei","age":25}]
[]

おお、なんかできたぞ。
json_transformの第一引数にはJSONデータを渡す。
第二引数にはサブコマンドとそれに続けてJSONパス式を書く。
サブコマンドによるが、パス式の後にJSONデータが必要になる場合もあり。REMOVE、KEEPの場合は必要ない。

json_transformは処理後のJSONデータを戻してくる。
結果をみると確かに実行前にあったはずの要素がひとつ消えている。

ふと「しまった、追加してから消せばよかったかも」と後悔するのだが、Oracleならデフォルトでトランザクションかかっているから、ROLLBACKすればいいじゃん。でも、SELECTしかしてないけど... ROLLBACK効くのか？

やってみた。

SQL> rollback;

ロールバックが完了しました。

SQL> select j from jfoo;

J
--------------------------------------------------------------------------------
[{"name":"asai","age":58}, {"name":"suzuki","age":32}, {"name":"honda","age":45]]
[{"name":"ichiro","age":52}, {"name":"syouhei","age":25}]
[{"name":"honda","age":45}]

できた！
ムムム、これは気を付けないと「ヤバいやつ」かも知れない。
SELECT命令ではデータを更新しないので「ロックは発生しない」と思っているとハマる可能性があるのね。まぁ、今までもSELECT FOR UPDATEみたいなことをすればロックがかかるのでjson_transformを使うとFOR UPDATEが付くよっていう風に覚えておく。

KEEP

データが戻ったので今度はKEEPをやってみるか。REMOVEが指定されたデータを削除するのに対して、KEEPは指定されたものは残して(KEEPして)他を削除する。

SQL> select json_transform(j, keep '$[0]') from jfoo;

JSON_TRANSFORM(J,KEEP'$[0]')
--------------------------------------------------------------------------------
[{"name":"asai","age":58}]
[{"name":"ichiro","age":52}]
[{"name":"honda","age":45}]

なるほど。
わかったのでROLLBACKして戻しておく。

APPEND

削除したら今度は追加かな。簡単だと思われる配列の末尾に要素を追加するAPPENDをやってみる。JavaScriptならpushかな。

select json_transform(j, append '$' = '{"name":"kawasaki","age":10}' format json) from jfoo;

JSON_TRANSFORM(J,APPEND'$'='{"NAME":"KAWASAKI","AGE":10}'FORMATJSON)
--------------------------------------------------------------------------------
[{"name":"asai","age":58},{"name":"suzuki","age":32},{"name":"honda","age":45},{"name":"kawasaki","age":10}]
[{"name":"ichiro","age":52},{"name":"syouhei","age":25},{"name":"kawasaki","age":10}]
[{"name":"honda","age":45},{"name":"kawasaki","age":10}]

うーんできちゃった。format jsonを付けないとJSONデータとみなされないので注意。
これもROLLBACKして戻す。

INSERT

配列の先頭に要素を追加する場合はINSERTを使う。JavaScriptならunshift？

SQL> select json_transform(j, insert '$' = '{"name":"kawasaki","age":10}' format json) from jfoo;

行1でエラーが発生しました。:
ORA-40810: ルート値の更新は許可されていません JZN-00409:
JSON_TRANSFORMではルート値を更新できません

なんですと？ルート値は変更できないとのこと。ルート値というのは「どうも$のこと」らしいぞ。
どうすれば...末尾に追加する際のAPPENDではどこに追加するのかが決定しているので$の指定だけでよかったのだが、INSERTではどの位置に追加するのかを指定しないといけないらしい。位置は要素のインデックスで指定する。

SQL> select json_transform(j, insert '$[0]' = '{"name":"kawasaki","age":10}' format json) from jfoo;

JSON_TRANSFORM(J,INSERT'$[0]'='{"NAME":"KAWASAKI","AGE":10}'FORMATJSON)
--------------------------------------------------------------------------------
[{"name":"kawasaki","age":10},{"name":"asai","age":58},{"name":"suzuki","age":32},{"name":"honda","age":45}]
[{"name":"kawasaki","age":10},{"name":"ichiro","age":52},{"name":"syouhei","age":25}]
[{"name":"kawasaki","age":10},{"name":"honda","age":45}]

今度はできた。

JavaScriptのunshiftでは必ず先頭に追加される。json_transformでのINSERTは要素位置を指定できるので、spliceの方に近いのかも。
ちなみに、insert $[last+1] のようにパスを指定するとAPPENDと同じになる。

SET

削除、追加ときたので次は更新だな。
SETで値を変更できる。このフィールドの値だけ変えたいんだよねぇっていうケースはけっこうあると思う。

SQL> select json_transform(j, set '$[0]' = '{"name":"kawasaki","age":10}' format json) from jfoo;

JSON_TRANSFORM(J,SET'$[0]'='{"NAME":"KAWASAKI","AGE":10}'FORMATJSON)
--------------------------------------------------------------------------------
[{"name":"kawasaki","age":10},{"name":"suzuki","age":32},{"name":"honda","age":4
5}]

[{"name":"kawasaki","age":10},{"name":"syouhei","age":25}]
[{"name":"kawasaki","age":10}]

JSONパスでフィールド名まで指定することもできる。

SQL> select json_transform(j, set '$[0].name' = '"kawasaki"' format json) from jfoo;

JSON_TRANSFORM(J,SET'$[0].NAME'='"KAWASAKI"'FORMATJSON)
--------------------------------------------------------------------------------
[{"name":"kawasaki","age":58},{"name":"suzuki","age":32},{"name":"honda","age":4
5}]

[{"name":"kawasaki","age":52},{"name":"syouhei","age":25}]
[{"name":"kawasaki","age":45}]

要素0のnameフィールドだけ変更してみた。

setで更新するときはSQLのUPDATE命令使いたくなるなぁ...と思ったらOracleのマニュアルにもUPDATEでやる例が載っているじゃないの。

SQL> update jfoo set j = json_transform(j, set '$[0].name' = '"kawasaki"' format json);

4行が更新されました。

RETURNINGしていないから結果が見えなくて、ちゃんとできているのか不安だが、SELECTで確認したらちゃんと更新されてた。
ということは、今までSELECTでやってみてたけど、UPDATEでもできるのね。

JSONデータを更新した後で結果を見たい場合 -> select json_transform()
別に結果は見なくてよい場合 -> update xxx set j = json_transform()

と使い分けるのが良いかも。

REPLACE

REPLACEでも値を更新することができる。SETとの違いはJSONパスと一致するJSONデータが存在しない場合の挙動。SETではパスが見つからない場合はその位置に新規にフィールドなり配列要素なりを作って更新する。REPLACEでは対象が見つからない場合、単に更新されない。

じゃあやってみるか。
addressという名前のフィールドが存在してないのでaddressフィールドに値をREPLACEで更新してみよう。

SQL> select json_transform(j, replace '$[0].address' = '"埼玉県"' format json) from jfoo;

JSON_TRANSFORM(J,REPLACE'$[0].ADDRESS'='"埼玉県"'FORMATJSON)
--------------------------------------------------------------------------------
[{"name":"asai","age":58},{"name":"suzuki","age":32},{"name":"honda","age":45}]
[{"name":"ichiro","age":52},{"name":"syouhei","age":25}]
[{"name":"honda","age":45}]

まぁ、当たり前の挙動な感じ。
SQLのUPDATE命令で更新の対象行が存在しない場合でもエラーにはならないことと似ている。
REPLACEをSETに変更してやってみよう。

SQL> select json_transform(j, set '$[0].address' = '"埼玉県"' format json) from jfoo;

JSON_TRANSFORM(J,SET'$[0].ADDRESS'='"埼玉県"'FORMATJSON)
--------------------------------------------------------------------------------
[{"name":"asai","age":58,"address":"埼玉県"},{"name":"suzuki","age":32},{"name":"honda","age":45}]
[{"name":"ichiro","age":52,"address":"埼玉県"},{"name":"syouhei","age":25}]
[{"name":"honda","age":45,"address":"埼玉県"}]

ふーん。addressフィールドが追加されてる！

フィールドでやってみたが、配列要素でも自動で追加されるみたい。
なのだが、なんか恐ろしいことが書いてある。

　指定されたJSONパスが配列の大きさを超えるような場合、配列の大きさが自動調整される

マジか。

　調整された配列の間にできた隙間はnullで埋められる

ホントに？

ヤバいな。配列の添え字をミスると大変なことになりそう。
例えば...

select json_transform(j, set '$[10000]' = '{"address":"埼玉県"}' format json) from jfoo;

こんなことしたら10000個のnullが隙間に埋められるのか？
やってみるか。

SQL> select json_transform(j, set '$[0].address' = '"埼玉県"' format json) from jfoo;

行1でエラーが発生しました。:
ORA-40478: 出力値が大きすぎます(最大: 4000) JZN-00018:
シリアライザへの入力が大きすぎます

ああ、なるほど。CLOB型でもない限り巨大なJSONデータは持てないわけね。
そこはひとまず安心するか。

他RDBMSでのJSONデータ変更

今回はOracleでのjson_transformについて調べてみた。json_transform()を使うとSELECT命令内であってもトランザクションが発生することがわかった。
JSONパスにしてもJavaScript寄りな考えになっているなぁと感じた。

最近はPostgresを扱うことが多いのだが、ポスグレにjson_transform()は存在しない。
MySQLはOracleに引き取られたのでjson_transformもあるのかな？と思ったが、json_insert()やjson_remove()、json_set()、json_replace()といった関数が存在している。
MS SQL Serverでは、json_modify()関数がある。
よくよく調べたらポスグレにもjsonb_set()とかjsonb_insert()があるじゃない。

ちょっとなんか困るぅ。

どうしてこうもバラバラなのよ。

SQLやJSONとは無関係なのだが、新しく本が出るのでその宣伝をしておく。いわゆる案件である。