2013年9月3日火曜日

PostgreSQL9.3 の新機能 ~JSON 編~

こんにちは。渡辺です。

前回は、PostgreSQL の新機能のうちビューについて解説しました。今回は JSON サポートの新機能について解説します。

■JSON サポート

RFC4627 に準拠した JSON サポートは PostgreSQL9.2 から追加されました。

http://www.postgresql.org/docs/9.2/static/datatype-json.html

JSON 型のカラムに保存されるデータは、JSON 形式として妥当であるかバリデーションされます。TEXT 型カラムに保存した場合、正しい JSON データが保存される保証はありませんでしたが、PostgreSQL9.2 の JSON 型を使用すると確実に正しい JSON データが保存できるようになりました。

JSON は、UTF-8 のみサポートするので他の文字エンコーディングは使えませんが、\uXXXX 形式の文字表記ができます。この標記を使えば、どのデータベースの文字エンコーディングでも利用できます。

■JSON サポートの強化

PostgreSQL9.3 では、JSON型データを操作する 4 つのオペレータと新たな 10 の関数が追加されました。これにより、PostgreSQL9.2 では JSON 型データは保存できる程度でしたが、データベースからも JSON データの利用できるようになります。

■PostgreSQL9.2 の関数

http://www.postgresql.org/docs/9.2/static/functions-json.html
  • array_to_json(anyarray [, pretty_bool])
  • 配列を JSON として返す。PostgreSQL の多次元配列が JSON 配列として返される。pretty_bool が真の場合、1 次元の要素の後に改行が追加される。

    [例]

    user1@127 ~=# SELECT array_to_json('{{1,5},{99,100}}'::int[]);
      array_to_json   
    ------------------
     [[1,5],[99,100]]
    (1 行)
    
  • row_to_json(record [, pretty_bool])
  • 行を JSON として返す。pretty_bool が真の場合、一次元の要素の後に改行が追加される。

    [例]

    user1@127 ~=# SELECT row_to_json(row(1,'foo'));
         row_to_json     
    ---------------------
     {"f1":1,"f2":"foo"}
    (1 行)
    

■PostgreSQL9.3 で追加された演算子と関数

http://www.postgresql.org/docs/9.3/static/functions-json.html

◇演算子

  • ->
  • 配列要素またはオブジェクトフィールドを取得する。

  • ->>
  • 配列要素の値またはオブジェクトフィールドの値をテキストとして取得する

  • #>
  • 指定されたパスの JSON オブジェクトを取得する

  • #>>
  • 指定されたパスの JSON オブジェクトをテキストとして取得する。

    [JSON 用演算子の利用例]

    user1@127 ~=# SELECT '[1,2,3]'::json->2;
     ?column? 
    ----------
     3
    (1 行)
    
    時間: 0.481 ms
    user1@127 ~=# SELECT '{"a":1,"b":2}'::json->'b';
     ?column? 
    ----------
     2
    (1 行)
    
    時間: 0.399 ms
    user1@127 ~=# SELECT '[1,2,3]'::json->>2;
     ?column? 
    ----------
     3
    (1 行)
    
    時間: 0.304 ms
    user1@127 ~=# SELECT '{"a":1,"b":2}'::json->>'b'
    user1-# ;
     ?column? 
    ----------
     2
    (1 行)
    
    時間: 0.322 ms
    user1@127 ~=# SELECT '{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}';
     ?column? 
    ----------
     3
    (1 行)
    
    時間: 0.485 ms
    user1@127 ~=# SELECT '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'
    ;
     ?column? 
    ----------
     3
    (1 行)
    
    時間: 0.355 ms
    

◇関数

  • to_json(anyelement)
  • JSON 型を返す。組み込みデータ型でない場合、JSON 型にキャストされる。その他の値は JSON として正しい値となるようにエスケープされる。

    [例]

    
    user1@127 ~=# SELECT to_json('Fred said "Hi."'::text);
           to_json       
    ---------------------
     "Fred said \"Hi.\""
    (1 行)
    
  • json_array_length(json)
  • JSON の配列要素数を返す。再帰的に要素数は数えない。

    [例]

    user1@127 ~=# SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');
     json_array_length 
    -------------------
                     5
    (1 行)
    
  • json_each(json)
  • JSON オブジェクトのキー / 値のペアのセットを返す。再帰的な処理は行わない。

    [例]

    user1@127 ~=# SELECT * from json_each('{"a":"foo", "b":"bar"}');
     key | value 
    -----+-------
     a   | "foo"
     b   | "bar"
    (2 行)
    
  • json_each_text(from_json json)
  • JSON オブジェクトのキー / 値のペアのセットを文字列として返す。再帰的な処理は行わない。

    [例]

    
    user1@127 ~=# SELECT * from json_each_text('{"a":"foo", "b":"bar"}');
     key | value 
    -----+-------
     a   | foo
     b   | bar
    (2 行)
    
  • json_extract_path(from_json json, VARIADIC path_elems text[])
  • パス指定された JSON オブジェクトを返す。

    [例]

    
    user1@127 ~=# SELECT json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4');
      json_extract_path   
    ----------------------
     {"f5":99,"f6":"foo"}
    (1 行)
    
    
  • json_extract_path_text(from_json json, VARIADIC path_elems text[])
  • パス指定された JSON オブジェクトをテキストとして返す。

    [例]

    
    user1@127 ~=# SELECT json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6');
     json_extract_path_text 
    ------------------------
     foo
    (1 行)
    
  • json_object_keys(json)
  • JSON のキーをテキストとして返す。再帰的な処理は行わない。

    [例]

    
    user1@127 ~=# SELECT json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}');
     json_object_keys 
    ------------------
     f1
     f2
    (2 行)
    
  • json_populate_record(base anyelement, from_json json, [, use_json_as_text bool=false])
  • JSON データからレコードを生成する。フィールド名が一致しないと NULL となり、データ型が一致しない場合、エラーとなる。

    [例]

    
    user1@127 ~=# CREATE TYPE x AS (f1 int, f2 bool, f3 text);
    CREATE TYPE
    user1@127 ~=# SELECT * FROM json_populate_record(null::x, '{"f1":3,"f2":true,"f3":"Some text"}');
     f1 | f2 |    f3     
    ----+----+-----------
      3 | t  | Some text
    (1 行)
    user1@127 ~=# SELECT * FROM json_populate_record(null::x, '{"f1":3,"f2":true,"f3":"Some text", "f4":"Extra"}');
     f1 | f2 |    f3     
    ----+----+-----------
      3 | t  | Some text
    (1 行)
    user1@127 ~=# SELECT * FROM json_populate_record(null::x, '{"f1":3,"foo":"Bar","f3":"Some text"}');
     f1 | f2 |    f3     
    ----+----+-----------
      3 |    | Some text
    (1 行)
    user1@127 ~=# SELECT * FROM json_populate_record(null::x, '{"f1":3,"f2":"Bar","f3":"Some text", "f4":"Extra"}');
    ERROR:  invalid input syntax for type boolean: "Bar"
    
  • json_populate_recordset(base anyelement, from_json json, [, use_json_as_text bool=false]
  • JSON データからレコードセットを生成する。フィールド名が一致しないと NULL となり、データ型が一致しない場合、エラーとなる。

    [例] (データ型xは前の例と同じ)

    user1@127 ~=# SELECT * FROM json_populate_recordset(null::x, '[{"f1":1,"f2":false, "f3":"Foo"},{"f1":3,"f2":true, "f3":"Bar"}]');
     f1 | f2 | f3  
    ----+----+-----
      1 | f  | Foo
      3 | t  | Bar
    (2 行)
    
  • json_array_elements(json)
  • JSON 配列の要素を展開する。再帰的な処理は行われない。

    [例]

    user1@127 ~=# SELECT json_array_elements('[1,true, [2,false]]');
     json_array_elements 
    ---------------------
     1
     true
     [2,false]
    (3 行)
    

■JSON 関数とテーブル

JSON 関数は JSON 型を持つテーブルと一緒に利用すると様々なデータ操作を行えます。

user1@127 ~=# CREATE TABLE my_json(id SERIAL PRIMARY KEY, j JSON);
時間: 132.419 ms
user1@127 ~=# INSERT INTO my_json (j) VALUES ('{"f1":1,"f2":true,"f3":"Foo"}');
INSERT 0 1
user1@127 ~=# INSERT INTO my_json (j) VALUES ('{"f1":2,"f2":false,"f3":"bar"}');
INSERT 0 1

[json_each() にクエリ結果を渡す]

user1@127 ~=# SELECT * FROM json_each((SELECT j FROM my_json WHERE id=1));
 key | value 
-----+-------
 f1  | 1
 f2  | true
 f3  | "Foo"
(3 行)

[クエリで JSON 型を取得し、json_extract_path() 関数と "->" オペレータで同じ値を取得する]

user1@127 ~=# SELECT json_extract_path(j, 'f1') AS f1a, j->'f1' AS f1b FROM my_json WHERE id = 3;
         f1a         |         f1b         
---------------------+---------------------
 {"f11":11,"f12":12} | {"f11":11,"f12":12}
(1 行)

慣れるまでは、どの JSON 関数がレコードを受け取るのか、レコードセットを受け取るのか戸惑うかも知れません。しかし、一度解ってしまえば簡単です。

■まとめ

PostgreSQL マニュアルに記載されている例とほぼ同じですが、json_populate_record()/json_populate_recordset() や JSON 関数をテーブルで利用する例は参考になると思います。

PostgreSQL9.2 では JSON 型のデータは保存できるだけでしたが、PostgreSQL9.3 からは JSON 型用の演算子と関数を使って様々な操作が行えます。JSON データをストアードプロシージャなどで処理したい場合、9.2 では苦労しますが 9.3 からは随分容易に取り扱えるようになりました。

データの受け渡しフォーマットとしてデファクトスタンダード化している JSON が、PostgreSQL で容易に取り扱えるようになる事は喜ばしい事です。

0 件のコメント:

コメントを投稿