やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

SQLite3学習 JSON拡張(json_patch)

 JSON文字列同士でマージしたものを返す。

成果物

json_patch()

 ターミナルで以下コマンドを実行する。

sqlite3

 対話モードにて以下コマンドをコピペし実行。

select json_patch('{}', '{"a:":2}');
{"a:":2}

 左をベースにして、右をパッチとしてマージする。

 では、左と右で別の型だったら? 右にすげ変わる。

select json_patch('{}', '[]');
[]

 追加・更新。

select json_patch('{"a":1}', '{"b":"B"}');
select json_patch('{"a":1}', '{"a":2}');
{"a":1,"b":"B"}
{"a":2}

 削除。

select json_patch('{"a":1}', '{"a":null}');
{}

パッチされる法則

  • 左(第一引数)が元データ。右(第二引数)が編集した部分
  • 同一キーがあればその値は右側のものになる
  • 右のみに存在するキーがあれば追加される
  • 右で{"key": null}のように値にnullがセットされていればキーごと削除する
編集 右の値
追加 {"左に未存のキー": "値"}
更新 {"左に既存のキー": "値"}
削除 {"左に既存のキー": null}

複数キーで試す

select json_patch('{"a":1,"b":2}','{"c":3,"d":4}');
select json_patch('{"a":[1,2],"b":2}','{"a":9}');
select json_patch('{"a":[1,2],"b":2}','{"a":null}');
select json_patch('{"a":1,"b":2}','{"a":9,"b":null,"c":8}');
select json_patch('{"a":{"x":1,"y":2},"b":3}','{"a":{"y":9},"c":8}');
{"a":1,"b":2,"c":3,"d":4}
{"a":9,"b":2}
{"b":2}
{"a":9,"c":8}
{"a":{"x":1,"y":9},"b":3,"c":8}

 最後のやつは深い階層でも、その部分のみ更新された。{"a":{"y":9},"b":3,"c":8}とはならない。

配列

 配列はまるごと上書きされる。単なる代入。

select json_patch('[1,2,3]','[1,4]');
[1,4]

 キーバリューやツリーのときはマージ方法に違和感はなかった。が、配列のときは微妙。

なぜ代入か

 え、[1,2,3,4]になってくれるのではないの? と思った。が、よく考えてみると重複を許すか許さぬか不定のため正解が定まらない。[1,1,2,3,4]を期待するかもしれない。または重複を削除したいかもしれない。すると[2,3]が期待値になる。

 こうなると論理演算したくなる。だが論理演算では重複できない。

 また、配列の順序に意味があるかもしれない。どの位置に挿入すればいいか関数で指定できない。

 上記の理由から、配列をマージしたあとの期待値を特定するのが難しい。だから代入することにしたのかもしれない。

UNION(和集合)

 ついでだから配列を論理演算で結合してみた。

.headers on
.mode column
select json_array(1,2,3) union select json_array(1,4);
select * from json_each((select json_array(1,2,3))) union select * from json_each((select json_array(1,4)));
select * from json_each((select json_array(1,2,3))) union select * from json_each((select json_array(1,4))) order by value asc;

 インデントで整形すると以下。

select * 
from json_each((select json_array(1,2,3))) 
union 
select * 
from json_each((select json_array(1,4))) 
order by value asc;
key         value       type        atom        id          parent      fullkey     path      
----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------
0           1           integer     1           1                       $[0]        $         
1           2           integer     2           2                       $[1]        $         
2           3           integer     3           3                       $[2]        $         
1           4           integer     4           2                       $[1]        $         

 [1,2,3,4]

EXCEPT(差集合)

select * from json_each((select json_array(1,2,3))) except select * from json_each((select json_array(1,4))) order by value asc;

 インデントで整形すると以下。

select * 
from json_each((select json_array(1,2,3))) 
except 
select * 
from json_each((select json_array(1,4))) 
order by value asc;
key         value       type        atom        id          parent      fullkey     path      
----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------
1           2           integer     2           2                       $[1]        $         
2           3           integer     3           3                       $[2]        $         

 [2,3]

INTERSECT(積集合)

select * from json_each((select json_array(1,2,3))) intersect select * from json_each((select json_array(1,4))) order by value asc;

 インデントで整形すると以下。

select * 
from json_each((select json_array(1,2,3))) 
intersect 
select * 
from json_each((select json_array(1,4))) 
order by value asc;
key         value       type        atom        id          parent      fullkey     path      
----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------
0           1           integer     1           1                       $[0]        $         

 [1]

所感

対象 パッチ方法
キーバリュー 変更
配列 変更
子要素 指定キーの値のみ変更

JSONクエリ内関数があったらいいな

JSONクエリ内関数

 こうなるとデータ構造には配列でなく、重複しないHashMapとか、順序が決まった操作を持つStack,Queueなどが欲しくなる。

 もしあったらどうなるだろうか?

a.json

{"queue": Q[1,2,3], "stack": S[1,2,3]}

 取得。

$.queue.dequeue()  --> 1  Q[2,3]
$.stack.pop() --> 3  S[1,2]

 追加。

$.queue.enqueue(4)  --> Q[1,2,3,4]
$.stack.push(4) --> S[1,2,3,4]

 ただの配列にも関数があればいいな。でも要求が高すぎてもはやSQLではなくなる。

{"array": [1,2,3]}
$.queue.push(4)  --> [1,2,3,4]
$.stack.first() --> 1  [2,3]
$.stack.last() --> 3  [1,2]
$.stack.get(1) --> 2  [1,3]
$.stack.ref(1) --> 2  [1,2,3]
$.stack.set(1,20) --> [1,20,3]
$.stack.set(2,+) --> [1,2,4]
$.stack.set(2,+4) --> [1,2,7]
$.stack.set_all(+4) --> [5,6,7]
$.stack.set_clouser(x => x%2==0 return x*10) --> [1,20,3]
$.stack.delete(2) --> [1,2]
$.stack.delete_head() --> [2,3]
$.stack.delete_tail() --> [1,2]
$.stack.delete_all() --> []

 ref()以外は変更操作。使ったらDB更新することになる。またはcommit()するまでオンメモリでやる。

 もしpush()という名のJSONオブジェクトキーが存在したら区別が付けられなくなる。

 だれかこういうデータ構造用メソッドをもったファイル形式・クエリ言語・パーサを設計・実装してくれ。

 妄想おわり。

情報源

対象環境

$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux

前回まで