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
ҟҽղհɾօ ʍօɾ@_john_doe_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 さんです。