PostgreSQLのJSON型

業務で主にPostgreSQLを使用しているのですが、Ver9.2以降でJSON型というのが追加されたとの事で、少し使ってみました。

使い方

機能としては、

• 型(INDEXも使える)
• 演算子
• 関数

とあるのですが、今回はJSON型と演算子を使ってみました。

JSON型

公式のドキュメントは

www.postgresql.jp

にあります。 内部的には、JSON型は入力データのテキストをそのまま、JSONB型(9.4から)はバイナリ形式で格納されているようです。

JSON型を使ったテーブルは、以下のようにして作れます。

CREATE TABLE TEST_TABLE (
 ID SERIAL PRIMARY KEY,
 DATA JSON
);

データはJSONテキストをそのままINSERTします。 ORACLEのEMPレコード的なものを入れてみました。

INSERT INTO TEST_TABLE(DATA) VALUES ('
  {
    "name": "Smith",
    "job": "clark",
    "salary": 800
  }
');
INSERT INTO TEST_TABLE(DATA) VALUES ('
  {
    "name": "Allen",
    "job": "salesman",
    "salary": 1600
  }
');

そのままSELECTすると、テキストデータのよう結果を取得できます。

DB=# SELECT * FROM TEST_TABLE;
 id |         data
----+----------------------
  1 |
    : {
    :   "name": "Smith",
    :   "job": "clark",
    :   "salary": 800
    : }
    :
  2 |
    : {
    :   "name": "Allen",
    :   "job": "salesman",
    :   "salary": 1600
    : }
    :
(2 rows)

演算子

JSON型の便利なところは、データとして入っているJSONの項目に対して条件を指定してSELECTができることです。->>という演算子を使用することで、以下のようにJSONオブジェクトのフィールドの名前を指定して、テキスト値として取得できます。

DB=# SELECT * FROM TEST_TABLE WHERE data->>'name' LIKE 'Sm%';
 id |        data
----+--------------------
  1 |
    : {
    :   "name": "Smith",
    :   "job": "clark",
    :   "salary": 800
    : }
    :
(1 row)

DB=# SELECT * FROM TEST_TABLE
DB-# WHERE CAST(data->>'salary' AS INTEGER)  BETWEEN 1000 AND 2000;
 id |         data
----+----------------------
  2 |
    : {
    :   "name": "Allen",
    :   "job": "salesman",
    :   "salary": 1600
    : }
    :
(1 row)

さらにオブジェクトの深い階層も、->という演算子で指定することができます。

以下のように、階層を持つJSONをINSERTした後に、

INSERT INTO TEST_TABLE(DATA) VALUES ('
{
  "name": "Jones",
  "job": "manager",
  "salary": 1600,
  "dept": {
    "deptno": 100,
    "deptname": "name1"
  }
}
');

->演算子を使い、dept階層下の値を条件に指定できます。

DB=# SELECT * FROM TEST_TABLE 
DB-# WHERE data->'dept'->>'deptname' like '%1';
 id |           data
----+---------------------------
  3 |
    :   {
    :     "name": "Jones",
    :     "job": "manager",
    :     "salary": 1600,
    :     "dept": {
    :       "deptno": 100,
    :       "deptname": "name1"
    :     }
    :   }
    :
(1 row)

実際は、このまま使うと文字列の全文検索となってしまうので、検索対象にINDEXを貼ると良いようです。

CREATE INDEX IDX_NAME ON TEST_TABLE ( (data->>'name') );
CREATE INDEX IDX_SALARY ON TEST_TABLE (CAST(data->>'salary' AS INTEGER));