Advent Calendar の 20日目のやつです。
巷でぶっ叩かれてる印象のJSON型ですが、いいやつだと思ってます。オレは嫌いじゃないよ！
ただ、そこそこでかいJSONを確認すると…
というわけで、JSONデータを確認するときはjqを使うことが世の常なのかなって思ってますが、PostgreSQL上のJSONデータをjqで見ようとするとpsqlを使ってこんな感じのことをしないといけなかったりします。
だるいしpsqlパラメータ忘れるし…
当時の感想は次の通り。
で、まあ作りました。のがこちら。
前回のエントリでPL/Python云々のようなことを書きましたが、実はこれがやりたかったのでした。
最初Cで書いてたんですが、パイプを使ったコマンドの実行結果を戻すのがエライ面倒で、若干ふざけた仕様で一旦終わらせてたんですが、アンカンファレンスで永安さんがPL/Pythonを使ったデモなど色々されてたんで、ああ…これで作りなおそうと思った次第でございます。
以下補足。
ただ…デモ環境/本番環境はRDSなんですよねえ…というオチ。
明日はhouseisland さんです。
巷でぶっ叩かれてる印象のJSON型ですが、いいやつだと思ってます。オレは嫌いじゃないよ！
ただ、そこそこでかいJSONを確認すると…
localhost postgres@test=# CREATE TABLE json_tbl (id serial primary key, json_col json);
CREATE TABLE
Time: 8.730 ms
localhost postgres@test=# INSERT INTO json_tbl (json_col) VALUES ('{"line":[{"line_cd":11311,"line_name":"JR中央本線(東京～塩尻)"},{"line_cd":11401,"line_name":"八ヶ岳高原線"},{"line_cd":11403,"line_name":"JR信越本線(篠ノ井～長野)"},{"line_cd":11407,"line_name":"JR飯山線"},{"line_cd":11409,"line_name":"北アルプス線"},{"line_cd":11411,"line_name":"JR中央本線(名古屋～塩尻)"},{"line_cd":11412,"line_name":"JR篠ノ井線"},{"line_cd":11413,"line_name":"JR飯田線(豊橋～天竜峡)"},{"line_cd":11414,"line_name":"JR飯田線(天竜峡～辰野)"},{"line_cd":99403,"line_name":"しなの鉄道線"},{"line_cd":99404,"line_name":"上田電鉄別所線"},{"line_cd":99405,"line_name":"長野電鉄長野線"},{"line_cd":99407,"line_name":"上高地線"},{"line_cd":99427,"line_name":"北しなの線"}]}');
INSERT 0 1
Time: 1.700 ms
localhost postgres@test=#
localhost postgres@test=# SELECT * FROM json_tbl;
-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id | 1
json_col | {"line":[{"line_cd":11311,"line_name":"JR中央本線(東京～塩尻)"},{"line_cd":11401,"line_name":"八ヶ岳高原線"},{"line_cd":11403,"line_name":"JR信越本線(篠ノ井～長野)"},{"line_cd":11407,"line_name":"JR飯山線"},{"line_cd":11409,"line_name":"北アルプス線"},{"line_cd":11411,"line_name":"JR中央本線(名古屋～塩尻)"},{"line_cd":11412,"line_name":"JR篠ノ井線"},{"line_cd":11413,"line_name":"JR飯田線(豊橋～天竜峡)"},{"line_cd":11414,"line_name":"JR飯田線(天竜峡～辰野)"},{"line_cd":99403,"line_name":"しなの鉄道線"},{"line_cd":99404,"line_name":"上田電鉄別所線"},{"line_cd":99405,"line_name":"長野電鉄長野線"},{"line_cd":99407,"line_name":"上高地線"},{"line_cd":99427,"line_name":"北しなの線"}]}
Time: 0.511 ms
localhost postgres@test=#
みづれーっつーの。(※ テストデータはこちらのサイトから拝借)というわけで、JSONデータを確認するときはjqを使うことが世の常なのかなって思ってますが、PostgreSQL上のJSONデータをjqで見ようとするとpsqlを使ってこんな感じのことをしないといけなかったりします。
[postgres@localhost ~]$ psql -Atc "SELECT json_col FROM json_tbl WHERE id = 1" test | jq .
{
"line": [
{
"line_cd": 11311,
"line_name": "JR中央本線(東京～塩尻)"
},
{
"line_cd": 11401,
"line_name": "八ヶ岳高原線"
},
{
"line_cd": 11403,
"line_name": "JR信越本線(篠ノ井～長野)"
},
{
"line_cd": 11407,
"line_name": "JR飯山線"
},
{
"line_cd": 11409,
"line_name": "北アルプス線"
},
{
"line_cd": 11411,
"line_name": "JR中央本線(名古屋～塩尻)"
},
{
"line_cd": 11412,
"line_name": "JR篠ノ井線"
},
{
"line_cd": 11413,
"line_name": "JR飯田線(豊橋～天竜峡)"
},
{
"line_cd": 11414,
"line_name": "JR飯田線(天竜峡～辰野)"
},
{
"line_cd": 99403,
"line_name": "しなの鉄道線"
},
{
"line_cd": 99404,
"line_name": "上田電鉄別所線"
},
{
"line_cd": 99405,
"line_name": "長野電鉄長野線"
},
{
"line_cd": 99407,
"line_name": "上高地線"
},
{
"line_cd": 99427,
"line_name": "北しなの線"
}
]
}
[postgres@localhost ~]$
だるいしpsqlパラメータ忘れるし…
当時の感想は次の通り。
ҟҽղհɾօ ʍօɾ@_john_doe_
これはめちゃわかる。が、これもこれで面倒…なんですよねえ…RT @hayamiz_retro: #jpug_study postgresのJSON演算子はクソなので、個人的には psql -qAt の出力を jq でパイプに繋ぐのがベストプラクティスになってる
2015/10/13 10:21:10
SELECT jq(json) FROM table WHERE id = 1 って感じでjqがフックされてる感じにならんもかね。
2015/10/13 10:21:58
で、まあ作りました。のがこちら。
前回のエントリでPL/Python云々のようなことを書きましたが、実はこれがやりたかったのでした。
最初Cで書いてたんですが、パイプを使ったコマンドの実行結果を戻すのがエライ面倒で、若干ふざけた仕様で一旦終わらせてたんですが、アンカンファレンスで永安さんがPL/Pythonを使ったデモなど色々されてたんで、ああ…これで作りなおそうと思った次第でございます。
[postgres@localhost ~]$ createlang -l test Procedural Languages Name | Trusted? ---------+---------- plpgsql | yes [postgres@localhost ~]$ psql test psql (9.3.3) Type "help" for help. localhost postgres@test=# create extension plpythonu; CREATE EXTENSION Time: 166.155 ms Time: 0.334 ms localhost postgres@test=# \q [postgres@localhost ~]$ psql test < pg_jq.sql NOTICE: 00000: function pg_jq(json,text) does not exist, skipping LOCATION: does_not_exist_skipping, dropcmds.c:245 DROP FUNCTION Time: 0.591 ms CREATE FUNCTION Time: 15.131 ms [postgres@localhost ~]$でセットすると
localhost postgres@test=# SELECT pg_jq(json_col, '.') FROM json_tbl WHERE id = 1;
pg_jq
-----------------------------------------------
{ +
"line": [ +
{ +
"line_cd": 11311, +
"line_name": "JR中央本線(東京～塩尻)" +
}, +
{ +
"line_cd": 11401, +
"line_name": "八ヶ岳高原線" +
}, +
{ +
"line_cd": 11403, +
"line_name": "JR信越本線(篠ノ井～長野)"+
}, +
{ +
"line_cd": 11407, +
"line_name": "JR飯山線" +
}, +
{ +
"line_cd": 11409, +
"line_name": "北アルプス線" +
}, +
{ +
"line_cd": 11411, +
"line_name": "JR中央本線(名古屋～塩尻)"+
}, +
{ +
"line_cd": 11412, +
"line_name": "JR篠ノ井線" +
}, +
{ +
"line_cd": 11413, +
"line_name": "JR飯田線(豊橋～天竜峡)" +
}, +
{ +
"line_cd": 11414, +
"line_name": "JR飯田線(天竜峡～辰野)" +
}, +
{ +
"line_cd": 99403, +
"line_name": "しなの鉄道線" +
}, +
{ +
"line_cd": 99404, +
"line_name": "上田電鉄別所線" +
}, +
{ +
"line_cd": 99405, +
"line_name": "長野電鉄長野線" +
}, +
{ +
"line_cd": 99407, +
"line_name": "上高地線" +
}, +
{ +
"line_cd": 99427, +
"line_name": "北しなの線" +
} +
] +
}
(1 row)
Time: 22.147 ms
localhost postgres@test=#
こうなると。んで当然
localhost postgres@test=# SELECT pg_jq(json_col, '.line[0].line_cd') FROM json_tbl WHERE id = 1; pg_jq ------- 11311 (1 row) Time: 8.913 ms localhost postgres@test=#こういうこともできると。
以下補足。
- jq自体はめちゃめちゃ高機能なんですが、私の用途であればこれで十分
- PL/Python自体はuntrustedなので、スーパーユーザで
- Python通してて、結局ハイライトが消えてすっごく残念
ただ…デモ環境/本番環境はRDSなんですよねえ…というオチ。
明日はhouseisland さんです。
