Advent Calendar の 20日目のやつです。

巷でぶっ叩かれてる印象の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パラメータ忘れるし…
当時の感想は次の通り。

で、まあ作りました。のがこちら

前回のエントリで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 さんです。

Add Comments

名前
 
  絵文字
 
 
プロフィール

john_doe_

Twitter
instagram(SnapWidget)
タグクラウド
  • ライブドアブログ