kaztomo日記

_ [PostgreSQL]OANDA API で取得し JSON形式で保存したヒストリカルデータを PostgreSQL で読んでみる

JSON形式で読んで扱えるということで、色々とググりながら SQL文を勉強中。

FileName : PathToJson ... [Pythonから出力した JSONファイル]

{
"USD_JPY": [
    {
        "complete": true,
        "closeMid": 111.4825,
        "highMid": 111.52,
        "lowMid": 111.3545,
        "volume": 2112,
        "openMid": 111.356,
        "time": "2017-05-18T20:00:00.000000Z"
    },
    {
        "complete": true,
        "closeMid": 111.567,
        "highMid": 111.6115,
        "lowMid": 111.451,
        "volume": 2936,
        "openMid": 111.5375,
        "time": "2017-05-18T21:00:00.000000Z"
    },
(snip)
],
"EUR_JPY": [
    {
        "complete": true,
        "closeMid": 123.781,
        "highMid": 123.884,
        "lowMid": 123.743,
        "volume": 539,
        "openMid": 123.7515,
        "time": "2017-05-18T20:00:00.000000Z"
    },
 (snip)
 ]
}

このファイルを PostgreSQL v9.6 で読み込んで、指定した配列要素番号にある closeMid を取得。

% createdb currency --locale=ja_JP.UTF-8 --encoding=UTF-8 --template template0
% psql currency
currency=# create table json_exam as select pg_read_file('PathToJson')::json as jsonColumn;
currency=# \d json_exam
テーブル "public.json_exam"
     列     |  型  | 修飾語
------------+------+--------
 jsoncolumn | json |

currency=# SELECT jsoncolumn::jsonb#>'{USD_JPY,1,closeMid}' FROM json_exam;
   ?column?
 ----------
  111.567
 (1 行)

currency=# SELECT jsoncolumn::jsonb#>'{EUR_JPY,0}' FROM json_exam;
                         ?column?
----------------------------------------------------------------------------
{"time": "2017-05-18T20:00:00.000000Z", "lowMid": 123.743, "volume": 539,
"highMid": 123.884, "openMid": 123.7515, "closeMid": 123.781, "complete": true}
(1 行)

参考にさせていただいたサイト：

• 9.15. JSON関数と演算子
• PostgreSQLのJSONデータ型っていうのをためしてみる