PostgreSQL JSON の応用メモ

去年 Qiita [PostgreSQL 9.4 の JSON サポートについて](http://qiita.com/kumazo@github/items/9010f9dad134b9d9d16d)紹介させてもらったが、まだ書きかけで jsonb の応用例としてや軽い検証記事や SQL イデオムを追記する予定だったが、整理しきれず放置したままになっていた。

そうこうしている内に早くも次期バージョン PostgreSQL 9.5 の情報がちらほらと出始めてきてしまった。

もうまとめるのは諦めることにして、その時のメモや雑多なコードから動くものを本稿に残しておく。
興味とヒマのある方は拾い読みしてほしい。

# 1. 開発言語からみた jsonb

プログラマの立場からみて、普段の開発で使うORM/フレームワークと jsonb の相性はどうなのか。
ここでは代表的なプログラミング言語環境でさらっとイメージだけつかんでおきたい。

個人的に Docker がマイブーム(死語)なので、検証環境に Docker を活用したい。
postgresSQL の検証環境は [DockerHub 公式イメージ](https://registry.hub.docker.com/_/postgres/)の library/postgres で、現時点での latest はバージョン 9.4.3 。
お試しなので Data Volume は切らず、パスワードなし、文字コードはデフォルト (en_US.UTF8) で問題なし。

```bash
$ docker run --name pg -p 5432:5432 -d postgres
73346173b0b54b99062d154d9eb9962fdde9d9b3e580b992315d59d9bade9f33
$ docker stop pg
pg
$ docker start pg
pg
$ docker exec -it pg psql -U postgres
psql (9.4.3)
Type "help" for help.

postgres=#
```

楽ちん、ちょー便利。

postgres の docker コンテナにクライアントコンテナからリンクすると、接続情報が環境変数として取得できる。その内容はだいたい以下のようになるはず。

```bash
$ docker run --rm --link pg:db postgres printenv  | grep DB | sort
DB_ENV_LANG=en_US.utf8
DB_ENV_PGDATA=/var/lib/postgresql/data
DB_ENV_PG_MAJOR=9.4
DB_ENV_PG_VERSION=9.4.3-1.pgdg70+1
DB_NAME=/hopeful_wilson/db
DB_PORT=tcp://172.17.0.4:5432
DB_PORT_5432_TCP=tcp://172.17.0.4:5432
DB_PORT_5432_TCP_ADDR=172.17.0.4
DB_PORT_5432_TCP_PORT=5432
DB_PORT_5432_TCP_PROTO=tcp
```

サンプルとして使用するテーブルスキーマは以下の通り。

```sql:サンプルテーブル
-- 投稿記事的ありがちテーブル
drop table posts;
create table posts (
  id serial primary key,
  jbpost jsonb,     -- スキーマレスな JSON で何でも詰め込む
  jbtags jsonb      -- タグを JSON 配列で持つ
);

insert into posts (jbpost, jbtags) values
('{"author":"キー太", "message":"日本語でおk"}', 
 '["hoge", "Qiita"]'
),
('{  "author": "Snafkin", 
     "title": "Postgres 入門", 
     "content": "はじめに...", 
     "comments": ["1get!", "2get!"] }', 
 '["PostgreSQL", "JSON"]'
);
```

SQL 文を psql にコピペする場合、`-n`オプションをつけておかないと日本語がはじかれるのは相変わらずだ。


## 1.1. JavaScript/Node.js

そもそも JSON は、JavaScript の初期化子(ObjectとArrayのリテラル)を起源とするデータ表現であり、Node.js との親和性は高いはずだ。

Node.js 環境には DockerHub 公式 (library/node) を使ってみる。

```bash:library/node
$ docker pull node
$ docker run --rm node node --version
v0.12.6

# npm で pg モジュールをローカルインストールする。
# boot2docker などで Windows のフォルダを共有している場合、 
# --no-bin-links をつけないとうまくいかない。
$ docker run --rm -it -v "$PWD":/usr/src/myapp -w /usr/src/myapp node npm install pg
pg@4.4.0 node_modules/pg
├── packet-reader@0.2.0
├── pg-connection-string@0.1.3
├── buffer-writer@1.0.0
├── generic-pool@2.1.1
├── pg-types@1.7.0
├── semver@4.3.6
└── pgpass@0.0.3 (split@0.3.3)

$ ls
node_modules/
```

取りあえず SELECT だけしてみよう。

```js:pg_json.js
//console.log(process.env);
var pg = require('pg');
pg.defaults.user = "postgres";

var conString = process.env.DB_PORT_5432_TCP + "/postgres";

var client = new pg.Client(conString);
client.on('drain', client.end.bind(client));
client.connect(function(err) {
  if(err) {
    return console.error('could not connect to postgres', err);
  }

  var filter = { author: "Snafkin" };
  client.query('SELECT * FROM posts WHERE jbpost @> $1 ', [ filter ], function(err, result) {
    if(err) {
      return console.error('error running query', err);
    }
    console.log(result.rows);
    console.log(result.rows[0].jbpost.title);
    console.log(result.rows[0].jbpost.comments);
    console.log(result.rows[0].jbpost.comments[1]);
    client.end();
  });
});
```

```bash:実行
$ ls
pg_json.js   node_modules

$ docker run --rm -it --link pg:db -v "$PWD":/usr/src/myapp -w /usr/src/myapp node node pg_json.js
[ { id: 2,
    jbpost:
     { title: 'Postgres 入門',
       author: 'Snafkin',
       content: 'はじめに...',
       comments: [Object] },
    jbtags: [ 'PostgreSQL', 'JSON' ] } ]
Postgres 入門
[ '1get!', '2get!' ]
2get!
```

シームレスだよ、おっ母さん！

なんと jsonb カラムは JavaScript オブジェクトツリーとして返されている。
パラメータに渡した JavaScript の Object はそのまま JSON として使われている。

すばらしい。
パラメータに JSON を文字列で渡しても OK だ。
しかしなぜだか Array ではエラーになる。

```js
// var filter = ["JSON"];  // error running query { [error: invalid input syntax for type json]
var filter = '["JSON"]'; // 文字列 OK
client.query('SELECT * FROM posts WHERE jbtags @> $1 ', [filter]...
```

INSERT 文などでパラメータの型によって気を使いたくない。
いっそのことパラメータを全部 JSON 文字列にまとめると jsonb_populate_record() で楽できる。

```js:insert.js
// エンティティクラス
function Post(author, title, content) {
  this.author = author;
  this.title = title;
  this.content = content; 
}
var post = new Post("Moomin", "My Love", "のんのおぉぉぉぉーー!");
var tags = '["Nonon"]';

var client = new pg.Client(conString);
client.on('drain', client.end.bind(client));
client.connect();

// データを JSON 文字列にまとめて jsonb_populate_record() を通す
var data = JSON.stringify( {jbpost: post, jbtags: tags} );
var insert = client.query(
  "INSERT INTO posts (jbpost, jbtags) SELECT jbpost, jbtags FROM jsonb_populate_record(null::posts, $1) RETURNING * ",
  [ data ]
);
insert.on("error", function(err){console.log(err);});
insert.on("row", function(row, result) {
  console.log(row);  
});
```

UPDATE 文ではそのような救いはなく、カラムごとの対応が避けられない。
(この点は 9.5 で改善される予定)

```js:pg_json_update.js
var update = client.query(
  "UPDATE posts SET (jbpost, jbtags) = ($2, $3) WHERE id = $1 RETURNING * ",
  [id, post, JSON.stringify(tags)]
);
update.on('error', function(err){console.log(err);});
update.on("row", function(row, result) {
  console.log(row);
});
```
また、JSON データ内部の差分だけを更新することはできず、丸ごと上書きとなる。

まあ、深入りしないで次に行こう。

### 【参考】
* brianc/node-postgres - GitHub  
https://github.com/brianc/node-postgres

## 1.2. Ruby/ActiveRecord

Rails は PostgreSQL をよく見ている。
Rails 4.2 で jsonb がサポートされている。

* [Ruby on Rails 4.0 リリースノート](http://railsguides.jp/4_0_release_notes.html) 2013/06/27
>  * PostgreSQLの配列型サポートが追加されました。配列カラムの作成時に任意のデータ型を使用できます。それらのデータ型はフルマイグレーションやスキーマダンプでもサポートされます。
>  * PostgreSQL hstoreレコードを作成できるようになりました。

* [Ruby on Rails 4.1 リリースノート](http://railsguides.jp/4_1_release_notes.html) 2014/04/08
>  * json値が書き込み時に型変換されます。これにより値がデータベースからの読み出し時と一貫します。

* [Ruby on Rails 4.2 リリースノート] (http://railsguides.jp/4_2_release_notes.html) 2014/12/19
>  * PostgreSQLアダプターでPostgreSQL 9.4+のjsonbデータタイプがサポートされました。

つまり jsonb 型を明示的に指定できるということ。

```rb
    create_table :hoge do |t|
      t.jsonb :jb_data
    end
```

これは期待できる。
Docker 環境で試してみよう。

```bash:Docker
# ActiveRecord と postgres アダプタの gem が入ったイメージを用意する。
# DockerHub 公式の ruby:onbuild を使うと、Gemfile を用意するだけで
# その内容をインストールした ruby 環境イメージを簡単に作成することができる。
$ docker pull ruby
$ docker run --rm ruby ruby --version
ruby 2.2.2p95 (2015-04-13 revision 50295) [x86_64-linux]

$ ls
Gemfile     Dockerfile

$ cat Gemfile
source "https://rubygems.org"
gem "activerecord"
gem "pg"

$ cat Dockerfile
FROM ruby:onbuild

$ docker run --rm -v $PWD:/usr/src/app -w /usr/src/app ruby bundle install
$ ls
Gemfile       Gemfile.lock  Dockerfile

# イメージをビルドする。名前は適当
$ docker build -t pgruby .

$ docker run --rm pgruby bundle list
Gems included by the bundle:
  * activemodel (4.2.1)
  * activerecord (4.2.1)
  * activesupport (4.2.1)
  :
  * pg (0.18.2)
  :
```
PostgreSQL にアクセスしてみよう。

```ruby:pg_json.rb
require 'active_record'
require 'logger'
require 'pp'
#pp ENV

ActiveRecord::Base.logger = Logger.new(STDOUT)
ActiveRecord::Base.establish_connection(
    adapter:  "postgresql",
    host:     ENV["DB_PORT_5432_TCP_ADDR"],
    username: "postgres"
)

class Post < ActiveRecord::Base
end

filter = '{"author": "Snafkin"}'
posts = Post.where("jbpost @> ?", filter)
pp posts
posts.each do |post|
    puts post.jbpost.class
    puts post.jbtags.class
    puts post.jbpost['title']
    puts post.jbtags[0]
end
```

```bash:実行
$ ls
pg_json.rb

$ docker run -it --rm --link pg:db -v $PWD:/usr/src/app -w /usr/src/app pgruby ruby pg_json.rb
D, [2015-07-01T00:00:00.000000 #1] DEBUG -- :   Post Load (1.2ms)  SELECT "posts ".* FROM "posts" WHERE (jbpost @> '{"author": "Snafkin"}')
[#<Post:0x007fb52c544f50
  id: 2,
  jbpost:
   {"title"=>"Postgres \u5165\u9580",
    "author"=>"Snafkin",
    "content"=>"\u306F\u3058\u3081\u306B...",
    "comments"=>["1get!", "2get!"]},
  jbtags: ["PostgreSQL", "JSON"]>]
Post
Hash
Array
Postgres 入門
PostgreSQL
```

すばらしい。
jsonb が Ruby の Hash と Array にそのまま変換されている。

ただし where() に渡すパラメータには、いろいろ試してみたが、JSON の文字列でしか指定できないようだ。
とりあえず Hash などを渡す時にはあらかじめ JSON 文字列に変換しておこう。

また where() にネイティブな `@>` 演算子がそのまま記述できているが、`?` 演算子などはパラメータのプレースホルダ '?' と干渉するという。

* [RailsでPostgreSQLのJSONBデータ型を使う | 日々雑記](http://y-yagi.tumblr.com/post/110683307855/rails-postgresql-jsonb)

> で、一個問題があって、JSONB用の一部の演算子に"?“ が使われていて、これがActiveRecordのプレースホルダ用の演算子(”?“)とバッティングしています。

なんとかエスケープとかできたいところだが、とりあえず、ハッシュ渡し（名前付きプレースホルダ）の方で回避できる。

```rb
posts = Post.where("tags ? ?", 'hoge')  # NG
posts = Post.where("tags ? :tag", {tag: 'hoge'})  # OK
```

でもこれは Rails だけの問題じゃなはずだ。

データの更新にはハッシュや配列をそのまま代入するか、JSON文字列を渡してもいい。
JSON 文字列を設定してもパースされず、だだの文字列値として保存される。

```ruby
post.jbpost['title'] = "How to breed Nyoro-nyoro for beginners"
post.jbpost['meta'] = {modified_at: DateTime.now()} # Hash
post.jbpost['comments'] = ["Good", "Bad"]           # Array
post.jbpost['style'] = '{"color": "#FF0000"}'       # JSON 文字列 は文字列のまま

post.save
```

create でもハッシュや配列がそのまま渡せる。
Ruby 2.2 で拡張された Hash のリテラル記法はネイティブ JSON とほとんど区別がつかない。

```ruby
Post.create(
  {
    "jbpost": {
      "author": "Moomin",
      "title": "Ruby on Rails 入門",
      "content": {"chapters": []},
    },
    "jbtags": ["Ruby", "ActiveRecord"]
  }
)
```

### 【参考】

* RailsでPostgreSQLのJSONBデータ型を使う | 日々雑記
http://y-yagi.tumblr.com/post/110683307855/rails-postgresql-jsonb

* Active Record and PostgreSQL — Ruby on Rails Guides
http://edgeguides.rubyonrails.org/active_record_postgresql.html

* Ruby 2.2.0 リファレンスマニュアル > リテラル > ハッシュ式
http://docs.ruby-lang.org/ja/2.2.0/doc/spec=2fliteral.html#hash


## 1.3. PHP/PDO

どんどん行こう、次はPHP/PDO だ。

```bash:DockerHub_php
# DockerHub 公式の php はほぼスッピンだが、イメージ内に
# docker-php-ext-install というツールが用意されている。
# これに extention 名を指定して実行するだけでビルド・インストールされる。
# インストールできる extension は /usr/src/php/ext 配下に置かれている。
$ docker pull php
$ docker run --rm php php -v
PHP 5.6.10 (cli) (built: Jun 12 2015 21:13:26)
Copyright (c) 1997-2015 The PHP Group
Zend Engine v2.6.0, Copyright (c) 1998-2015 Zend Technologies

$ ls
Dockerfile

$ cat Dockerfile
FROM php:latest
RUN apt-get update && apt-get install -y libpq-dev && docker-php-ext-install pdo_pgsql

# イメージをビルド
$ docker build -t pgphp .

$ docker run --rm pgphp php -m | grep -i pdo
PDO
pdo_pgsql
pdo_sqlite

```

```php:pg_json.php
<?php

$host = getenv('DB_PORT_5432_TCP_ADDR');
$port = getenv('DB_PORT_5432_TCP_PORT');
$user = 'postgres';
$pass = 'postgres';
$dsn  = "pgsql:dbname=postgres;host={$host};port={$port}";

try {
	$pdo = new PDO($dsn, $user, $pass);
	$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
	$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
	$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

	$sql = 'SELECT * from posts where jbpost @> ? ';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array('{"author":"Snafkin"}'));
	$rows = $stmt->fetchall();
	var_dump($rows);
	print $rows[0]["jbpost"] . "\n";

	$pdo = null;
} catch (PDOException $e) {
    print($e->getMessage() . "\n");
}
```

```bash:実行
$ ls
pg_json.php

$ docker run -it --rm --link pg:db -v $PWD:/usr/src/app -w /usr/src/app pgphp php pg_json.php
array(1) {
  [0]=>
  array(3) {
    ["id"]=>
    int(2)
    ["jbpost"]=>
    string(111) "{"title": "Postgres 入門", "author": "Snafkin", "content": "はじめに...", "comments": ["1get!", "2get!"]}"
    ["jbtags"]=>
    string(22) "["PostgreSQL", "JSON"]"
  }
}
{"title": "Postgres 入門", "author": "Snafkin", "content": "はじめに...", "comme
nts": ["1get!", "2get!"]}
```

PDO はパラメータもカラム値の戻りも JSON を文字列として扱われる。

Node.js や Rails のように、JSON をオブジェクトツリーで操作したいだろうか。
PHP 標準の JSON 関数(json_encode()/json_decode())を使えば、stdClass や連想配列に変換できる。
json_encode() で自前のクラスでも JSON に変換可能だが、逆のマッピングはそう簡単にはいかない。

```php
class Post {
    public $id = null;
    public $jbpost = array("title" => "Hello PDO");
    public $jbtags = array("PDO", "JSON");
};

    $post = new Post;

    // 行全体を JSON でインサート
    $sql = 'INSERT INTO posts(jbpost, jbtags) SELECT jbpost, jbtags FROM jsonb_populate_record(null::posts, ?)';
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array(json_encode($post)));
    
    // いったん行全体を JSON にして返す
    $id = $pdo->lastInsertId('posts_id_seq');
    $sql = 'SELECT row_to_json(posts)  from posts where id = ? ';
    $stmt = $pdo->prepare($sql);    
    $stmt->execute(array($id));
    // stdClass オブジェクトに変換する
    foreach ($stmt->fetchall(PDO::FETCH_FUNC, 'json_decode') as $row) {
         print $row->jbpost->author . "\n";
         print $row->jbtags[0] . "\n";
    }
```

## 1.4. Java/JDBC

Java/JDBC での jsonb に関しては、すでにネット上にいくつかの検証記事が公開されていて、単純にはうまくいかないことがわかっている。
以下の記事に詳しい。

* [PostgreSQL 9.4 の新機能 JSONB 型を使ってみよう]
(https://www.postgresql.jp/events/jpugpgcon2014files/B5_PGCon2014-JSONB-datatype) - PGCon2014/ぬこ＠横浜氏講演資料PDF

JSON データを受け取るだけなら String 扱いで可能だが、逆に PostrgeSQL に送る場合、可変長文字列から jsonb への型変換で拒否されてしまうようだ。

```text
Caused by: org.postgresql.util.PSQLException: ERROR: column "jbpost" is of type jsonb but expression is of type character varying
```

上記記事などによれば、JDBC PreparedStatement に引き渡すオブジェクトに jsonb 型を設定した org.postgresql.util.PGobject を指定することで解決できるという。

```java
// コードは上記記事参照
```

PDO でもただの文字列渡しなだけのように見えるが、そんな細かい対応がされているのだろうか？
まあ、そこは深入りせず現実的な対応の方を考えよう。

生 JDBC ではなく、ORM フレームワークなどを利用する場合、どこかにカラム型を変換する拡張を仕込まなければならない。
すでにその実例もいくつか見られる。

* JPA の@AttributeConverterの実装で解決した例
　* [Glassfish JPA JSONB - @//メモ]
(http://hondou.homedns.org/pukiwiki/pukiwiki.php?Glassfish%20JPA%20JSONB)

* Hibernate の UserType として実装した例
 * [pires/hibernate-postgres-jsonb](https://github.com/pires/hibernate-postgres-jsonb) - GitHub
 * [Mapping postgreSQL JSON column to Hibernate value type - Stack Overflow]
(http://stackoverflow.com/questions/15974474/mapping-postgresql-json-column-to-hibernate-value-type)

Java 開発者は環境や実装に依存してコードが汚されるのを嫌う。
JPA などの標準に従った開発プロジェクトによっては、エンティティクラスをフレームワーク固有のアノーテーションなどで汚されるのは許しがたいかもしれない。

もしネイティブ SQL の使用が許されるならパラメータにキャストを仕込むという安直な方法もある。

```sql
INSERT INTO posts(jbpost) values (?::jsonb);
INSERT INTO posts(jbpost) values (CAST( ? AS jsonb));
```

大抵のフレームワークは要所でネイティブ SQL を差し込む口を用意している。
その SQL 文を外出しにできる事を示せれば、潔癖症のママも許してくれるかもしれない。

ここでは、その方向を模索してみる。

### 1.4.1. JPA/Hibernate

たとえば Hibernate はカスタム SQL という拡張機能を持つ。

カスタム SQL を使うと INSERT や UPDATE、DELETE までネイティブな SQL 文で差し替えることができる。
カスタム SQL ではネイティブ SQL を`@SQLInsert`といったアノーテーションや、XMLのマッピングファイルに記述することができる。

```java:JpaHibernatePgJsonDemo.java
package hoge;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

//org.hibernate:hibernate-entitymanager:4.3.8.Final
import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQuery;
import javax.persistence.Persistence;
import javax.persistence.Table;
import org.hibernate.annotations.SQLInsert;
import org.hibernate.annotations.SQLUpdate;

//org.projectlombok:lombok:1.16.2
import lombok.Data;

/** エンティティクラス */
@Data
@Entity()
@Table(name = "posts")
// Hibernate カスタム SQL。　XMLファイルでも定義可。
@SQLInsert(sql = "INSERT INTO posts(jbpost, jbtags) VALUES (?::jsonb, ?::jsonb)", callable = true)
@SQLUpdate(sql = "UPDATE posts SET (jbpost, jbtags) = (?::jsonb, ?::jsonb) where id = ?", callable = true)
// JPQL
@NamedQuery(name = "find", query = "select p from Post p where p.id = :id")
class Post {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    Integer id;
    String jbPost; // JSON テキスト
    String jbTags; // JSON テキスト
}

/** デモ */
public class JpaHibernatePgJsonDemo {
    public static void main(String[] args) {
        final EntityManagerFactory emf = getEntityManagerFactory();
        final EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        try {
        	Post post = new Post();
            post.setJbPost("{\"title\":\"カスタムSQL\"}");
            post.setJbTags("[\"Hibernate\", \"Custom SQL\"]");

            // INSERT
            tx.begin();
            em.persist(post);
            tx.commit();
            System.out.println(post);

            // UPDATE
            tx.begin();
            post.setJbPost("{\"author\":\"Snafkin\"}");
            tx.commit();

            // ネイティブクエリ
            @SuppressWarnings("unchecked")
            List<Post> posts = em.createNativeQuery(
                    "select * from posts where jbpost @> cast(:filter as jsonb)", Post.class)
                    .setParameter("filter", "{\"author\":\"Snafkin\"}")
                    .getResultList();

            for (Post p : posts) {
                System.out.println(p);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            em.close();
        }
        emf.close(); // これを忘れるとスレッドが残る。
    }

    /** PostgreSQL セッション初期化 */
    private static EntityManagerFactory getEntityManagerFactory() {
        Map<String, Object> props = new HashMap<>();
        props.put("javax.persistence.jdbc.url",
                "jdbc:postgresql://localhost:5432/postgres");
        props.put("javax.persistence.jdbc.user", "postgres");
        props.put("javax.persistence.jdbc.password", "postgres");

        return Persistence.createEntityManagerFactory("default", props);
    }
}
```

```xml:persistence.xml
<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
             version="2.0">
<persistence-unit name="default" transaction-type="RESOURCE_LOCAL">
  <properties>
    <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver"/>
    <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
    <property name="hibernate.show_sql" value="true"/>
    <property name="hibernate.format_sql" value="true"/>
  </properties>
</persistence-unit>
</persistence>
```

```bash:実行
# 省略
```

取りあえず jsonb キャスト作戦で JSON が String として扱えることは確認できたがかなり苦労した。
少なくとも Hibernate はお勧めしない。

ます ネイティブクエリではパラメータのプレースホルダが干渉しまくる。
`?` 演算子は仕方ないとしても、`::jsonb` キャストが名前付きパラメータのプレースホルダと認識されてエラーになる。
JPA 的には、名前付きパラメータはサポートしないはずなのだが、親切なことだ。

カスタム SQL にもクセがあって、 SQL のパラメータの順序が強制的に固定されてしまい、しかもその規則が定義されない。
[マニュアル](http://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html/ch18.html#querysql-cud)の Tips によれば、パラメータの順序を知るにはログレベルを DEBUG にした上で、出力された SQL 文から実際の順序を確認しろという。(ドイヒー)
これでは運用保守に余計な負担をかけ、Hibernate の将来の実装で変わるリスクも抱えてしまう。
まったく、愛のない話だ。

### 1.4.2. MyBatis

そもそも JPA のような DB を隠蔽し抽象化し標準化する理想主義的なフレームワーク上で、非標準的なことをやろうとするから無理が出る。

その点、現実主義の MyBatis なら遠慮はいらない。
MyBatis がオブジェクトにマッピングするのはテーブルではなくネイティブな SQL 文そのものだ。

```MyBatisPgJsonDemo.java
package hoge;

import java.util.List;

//'org.projectlombok:lombok:1.16.2'
import lombok.Data;

//'org.mybatis:mybatis:3.2.8'
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.Environment;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;

//'org.postgresql:postgresql:9.4-1200-jdbc41'
import org.postgresql.ds.PGSimpleDataSource;

public class MyBatisPgJsonDemo {

    /** エンティティ クラス */
    @Data
    public static class Post {
        Integer id;
        String jbPost;  // JSON テキスト
        String jbTags;	// JSON テキスト
    }

    /** データアクセスマッパ */
    public static interface PostMapper {
        // SQL の記述はアノーテーションより外部XMLファイルの方が扱いやすい。
        @Insert("INSERT INTO posts (jbpost, jbtags) VALUES (#{jbPost}::jsonb, #{jbTags}::jsonb)")
        @Options(useGeneratedKeys = true, keyProperty = "id")
        public int create(Post book);

        @Update("UPDATE posts SET (jbpost, jbtags) = (#{jbPost}::jsonb, #{jbTags}::jsonb) where id = #{id}")
        public int save(Post post);
        
        @Select("SELECT * FROM posts WHERE id = #{id}")
        public Post find(@Param("id") int id);

        @Select("SELECT * FROM posts WHERE jbPost @> #{filter}::jsonb ")
        public List<Post> findBy(@Param("filter") String json_filter);
    }

    /** デモ */
    public static void main(String[] args) {

        try (SqlSession session = openPgSession()) {
            PostMapper mapper = session.getMapper(PostMapper.class);

            Post post = new Post();
            post.setJbPost("{\"author\":\"Snafkin\", \"title\": \"MyBatis 入門\"}");
            post.setJbTags("[]");
            mapper.create(post);
            System.out.println(post);

            post.setJbTags("[\"MyBatis\", \"Postgres\"]");
            mapper.save(post);
            
            List<Post> posts = mapper.findBy("{\"author\":\"Snafkin\"}");
            for (Post p : posts) {
                System.out.println(p);
            }
            
            session.rollback();
        } catch(Exception e) {
        	e.printStackTrace();
        }
    }

    /** PostgreSQL セッション初期化 */
    private static SqlSession openPgSession() {
    	org.apache.ibatis.logging.LogFactory.useStdOutLogging();
    	
        PGSimpleDataSource ds = new PGSimpleDataSource();
        ds.setServerName("localhost");
        ds.setDatabaseName("postgres");
        ds.setUser("postgres");
        ds.setPassword("postgres");

        Environment env = new Environment("devel",
                new JdbcTransactionFactory(), ds);
        Configuration conf = new Configuration(env);
        conf.addMapper(PostMapper.class);

        return new SqlSessionFactoryBuilder().build(conf).openSession();
    }
}
```

```bash:実行
# 省略
```
JSON データは String でマッピングされる。

JSON を String ではなく Map やクラスなどの Java オブジェクトにマッピングしたいとなると面倒だ。

MyBatis にも DB と Java のデータ変換をカスタマイズするめの仕掛け(TypeHandlerなど)が用意さているので、それを実装して好きな JSON ライブラリを使って、ドメインクラスへのバインディングなり、ベーシックな Map のツリーに変換することもやればできる。

そこで MOXy のような JSON をサポートした JAXB 実装を利用すれば、XML バインディングを使ったレガシーなクラスをそのまま JSON ベースに移行させることなんかもできるかもしれない。

もっとも 近頃 DB にアクセスするのはサービス API とかバックエンド側とか呼ばれるレイヤーであり、大抵 RESTFul 的な JSON API を提供しているだろう。
JSON をクラスに詰め替えてまた JSON に変換するというはちょっと無駄な感じもする。
それならいっそドメインロジックを SQL に寄せてサービスレイヤは極薄にしておき、JSON を String のまま丸投げしあうような設計にするというのも MyBatis ならアリだ。

結論としては Java で jsonb をそのまま使うのは面倒で、フレームワークなどによって個別の検証と配慮が必要になり、本格活用には時期尚早といえるだろう。
jsonb が今後 Java のフレームワークで対応されるようになるには、 JSON が SQL 標準に採用される日を待たねばならない。

* [次のSQL標準は何が盛り込まれる？ －第2回DBオフライン ：EnterpriseZine（エンタープライズジン）]
(http://enterprisezine.jp/dbonline/detail/6841)（要登録）

### 【参考】

* PostgreSQL 9.4 の新機能 JSONB 型を使ってみよう- PGCon2014/ぬこ＠横浜氏講演資料PDF 
https://www.postgresql.jp/events/jpugpgcon2014files/B5_PGCon2014-JSONB-datatype 
jsonb まとめとして必読資料

* PostgreSQLのJSON列にJavaからデータを登録する - Symfoware
http://symfoware.blog68.fc2.com/blog-entry-1246.html

* Hibernate Reference Documentation 4.3.9.Final 18.3. Custom SQL for create, update and delete
http://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html/ch18.html#querysql-cud

* MyBatis &#x2013; MyBatis 3 | 設定
https://mybatis.github.io/mybatis-3/ja/configuration.html#typeHandlers

* 知っているようで知らないプリペアードクエリ | yohgaki&#039;s blog
http://blog.ohgaki.net/postgresql-prepared-query-explained
Data Type の OID を使用するかは API 側の実装によるらしい。

# 2. JSON for SQL

SQL で JSON が使えると何ができるのか。

NoSQL、ドキュメント指向、スキーマレスなデータストアといった新たなパラダイムとの融合が開く可能性に妄想は膨らむが、現実にはちょっと便利な拡張カラムと言ったところか。

しかし視点を変えれば、jsonb で許された柔軟性と操作性は、SQL の持つレガシーな問題に新たな攻略法を提供する強力アイテムにもなるかもしれない。

## 2.1. ページネーションをなんとかする

一覧表示のページ分けで、指定範囲のデータと総件数を１回のクエリで取りたい。
DB 側で JSON にまとめて返してもらえるとありがたいのだが。

以下のようなイメージで出力できたい。

```json
{
  "total": 123456, 
  "offset": 20, 
  "count": 10, 
  "rows": [
    {"id": 2, "product_name": "かにバン", "product_code": "X123",・・・・・・},
    {"id": 6, "product_name": "２色バン", "product_code": "X456", ・・・・・・},
    ・
    ・
    ・
  ]
}

```

 ```json_build_object()```や```json_agg()```を使えばページ情報と検索結果のデータを一個の JSON にまとめることができる。

```sql
WITH hits AS (
    -- 検索条件と位置による絞り込み
    -- ここでは Index Only Scan になるよう心掛ける。

    SELECT
        p.id,                            -- 検索結果、データは ID のみ
        count(p.id) OVER() AS _total,    -- 総件数
        row_number() OVER( ORDER BY #{sort_cond} ) -- ソート条件 
            AS _position                 -- 行番号
    FROM
        products AS p
    INNER JOIN makers AS m ON p.id = m.product_id
    INNER JOIN ・・・
    INNER JOIN ・・・
    ・                     -- 悪夢の連鎖
    ・
    WHERE TRUE

      #{search_cond}       -- 邪悪な呪文

    LIMIT  #{page_size}    -- １ページあたりの行数
    OFFSET #{offset}       -- 開始位置
),
rows AS (
   -- 検索結果から一覧項目を収集する。
   SELECT p.id, p.product_name, p.product_code, m.maker_name, ・・・
   FROM hits
   INNER JOIN products AS p ON hits.id = p.id 
   INNER JOIN makers AS ON p.id = m.product_id
   INNER JOIN ・・          -- サボテンモンスター
   ORDER BY hits._position
)
SELECT
    json_build_object(
        'total',   hits._total,             -- 総件数
        'offset',  min(hits._position - 1), -- 先頭行の位置
        'count',   count(hits.id),         -- 取得件数
        'rows',    json_agg(rows)          -- 検索結果リスト
    ) AS result
FROM hits LEFT JOIN rows USING(id)
GROUP BY hits._total
;
```

## 2.2. EAV を JSON でなんとかする

EAV（Entity-Attribute－Value） は SQL アンチパターンの一つとして知られる。
json_object_agg() を使うと、EAV なデータを JSON としてまとめることができる。

```sql
-- EAV なテーブル設計
CREATE TABLE issues ( issue_id SERIAL PRIMARY KEY);
CREATE TABLE issue_attributes (
    issue_id    int references issues(issue_id),
    attr_name   varchar(100) NOT NULL,
    attr_value  varchar(100)
);

-- JSONにまとめてマテビューで固める
-- MATERIALIZED VIEW は 9.3 からのサポート
CREATE MATERIALIZED VIEW json_issues AS
    SELECT issue_id, json_object_agg(attr_name, attr_value) AS attrs 
    FROM issue_attributes 
    GROUP BY issue_id
;

SELECT * FROM json_issues
WHERE attrs->>'issue_type' = 'BUG'

;
 issue_id |                                                                   at
trs
----------+---------------------------------------------------------------------
----------------------------------------------------------------------
        1 | { "reported_by" : "101", "product_id" : "201", "issue_type" : "BUG",
 "status" : "OPEN", "severity" : "HIGH", "version_affected" : "1.0" }
        2 | { "reported_by" : "102", "product_id" : "202", "issue_type" : "BUG",
 "status" : "OPEN", "severity" : "LOW", "version_affected" : "1.2" }
(2 rows)
```

EAV に対するソリューションの一つに「シリアライズ LOB パターン」というのがある。
サブタイプに固有の非共通データはまとめて LOB カラムに詰め込んでしまう。

LOB のフォーマットには、JSON がふさわしい。
 ```json_object_agg()``` を使って、EAV を シリアライズ LOB なテーブルに変換してみよう。

```sql
-- シリアライズ LOB なテーブル
CREATE TABLE lob_issues (
-- 共通カラム
    issue_id         int PRIMARY KEY,  
    reported_by      int,
    product_id       int,
    priority         varchar(20),
    version_resolved varchar(20),
    status           varchar(20),
    issue_type       varchar(10),
-- JSON LOB カラム
    attributes       jsonb
);

-- EAV テーブルを LOB テーブルに変換する
with cols(name) as (
-- LOB テーブルのカラム名を収集
    SELECT * FROM json_object_keys(row_to_json(
        jsonb_populate_record(null::lob_issues, '{}')
    ))
),
src(id, name, value) AS (
-- ネタとなる EAV データ
    SELECT issue_id, attr_name, attr_value FROM issue_attributes
    UNION ALL
    SELECT issue_id, 'issue_id', issue_id::text FROM issue_attributes
),
base(id, name, value) AS (
-- 共通カラムのデータを仕分ける
    SELECT src.* FROM src INNER JOIN cols USING(name)
),
ext(id, name, value) AS (
-- 共通カラム以外のデータを仕分ける
    SELECT src.* FROM src LEFT JOIN cols USING(name)
    WHERE cols.name IS NULL
),
data(id, jb_data) AS (
-- いったんすべてのカラムをjsonbにまとめる（INSERTで楽をするため）
    SELECT id, json_object_agg(name, value)::jsonb as data
    FROM (
      SELECT id, name, to_json(value) AS value FROM base
      UNION ALL
      SELECT id, 'attributes' AS name, json_object_agg(name, value) AS value -- LOB
      FROM ext GROUP BY id
    ) AS x
    GROUP BY id
)
INSERT INTO lob_issues
    SELECT rec.*
    FROM data, jsonb_populate_record(NULL::lob_issues, data.jb_data) AS rec
;
```

長い SQL になったが、実行するのは一回だけなのでこれでよしとしよう。

```sql:シリアライズLOBテーブルの使用例
SELECT
    issue_id,
    product_id,
    attributes->>'severity' AS severity,
    attributes->>'version_affected' AS version_affected 
FROM lob_issues
WHERE status <> 'CLOSED'
  AND issue_type = 'BUG'
;
 issue_id | product_id | severity | version_affected
----------+------------+----------+------------------
        1 |        201 | HIGH     | 1.0
        2 |        202 | LOW      | 1.2
(2 rows)
```

### 参考

* SQLアンチパターン - Bill Karwin オライリー・ジャパン  
http://www.oreilly.co.jp/books/9784873115894/


## 2.3. ナイーブツリーを JSON でなんとかする

レコードに親子関係のあるテーブルを、再帰的な階層構造をもつ一個の JSON に変換してみる。

出力イメージ

```json
{
  "id":1,
  "content":"1. aaaaaaaaa",
  "children":[
    {
      "id":2,
      "content":"1.1. bbbbbbbbb",
      "children":[]
    },
    {
      "id":3,
      "content":"1.2. ccccccccc",
      "children":[
        { 
          "id":4,
          "content":"1.2.1. dddddddd",
          "children":[
  :
  :
  :
  ]
}

```

```sql

-- ナイーブなテーブル設計
CREATE TABLE comments (comment_id int PRIMARY KEY, parent_id int, comment text);

-- ナイーブなサンプルデータ
INSERT INTO comments VALUES
  (1, NULL, '1'), (2, 1, '1-1'), (3, 1, '1-2'), (4, 2, '1-1-1'),
  (5, 2, '1-1-2'), (6, 5, '1-1-2-3'), (7, 1, '1-3'), (8, NULL, '2')
;

-- ナイーブツリーを JSON 階層に変換する。
-- 引数で指定した ID のノード以下を取得する。
CREATE FUNCTION json_comments_tree(pid int) RETURNS json 
AS $$
  WITH sub_tree(children) AS (
    SELECT array_agg( json_comments_tree( comment_id ) ) -- 再帰
    FROM comments WHERE parent_id = pid
  ),
  tree AS (
    SELECT node.*, children
    FROM comments AS node, sub_tree 
    WHERE node.comment_id = pid
  )
  SELECT row_to_json(tree, FALSE) -- 整形は役に立たない。
  FROM tree
  ;
$$ LANGUAGE SQL;

```

再帰 CTE ではうまくいかず断念。
ストアドファンクションで再帰関数として作成した。

```sql:使用例
SELECT json_comments_tree(2)->'children'->1;
           ?column?
-------------------------------
 {"comment_id":5,             +
  "parent_id":2,              +
  "comment":"1-1-2",          +
  "children":[{"comment_id":6,+
  "parent_id":5,              +
  "comment":"1-1-2-3",        +
  "children":null}]}
(1 row)

SELECT json_comments_tree(1)#>>'{children,0,children,1,comment}';
 ?column?
----------
 1-1-2
(1 row)

```

### 参考

* SQLアンチパターン - Bill Karwin オライリー・ジャパン  
http://www.oreilly.co.jp/books/9784873115894/

## 2.4. OTLT を JSON でなんとかする

いわゆるコードマスターテーブルは OTLT（One True Lookup Table）という SQL アンチパターンに該当する。
クエリによってはこれを何個も JOIN するハメになる。
コードマスターを一個の JSON に変換しておけば、クエリ内では連想配列として扱えるはずだ。

```sql

-- OTLT なテーブル設計
CREATE TABLE code_master (
  id int,
  code_type varchar(20),
  code varchar(10),
  value varchar(40)
);

-- JSONにまとめてマテビューに固める
-- MATERIALIZED VIEW は 9.3 からのサポート
CREATE MATERIALIZED VIEW code_lookup (get)
AS
  SELECT json_object_agg( code_type, code_values )::jsonb AS get
  FROM (
    SELECT code_type, json_object_agg(code, value) AS code_values
    FROM code_master GROUP BY code_type
  ) AS m
;

```

```sql:使用例
SELECT
    t.title,
    l.get->'task'->>t.task_code AS task, 
    l.get->'status'->>t.status_code AS task_status, 
    l.get->'status'->>t.review_status_code AS review_status, 
    l.get->'status'->>t.release_status_code AS release_status, 
    l.get->'priority'->>t.priority_code AS priority
FROM 
    tickets AS t, 
    code_lookup AS l
;

-- パラメータ化して実行時に指定可能
SELECT
    l.get->#{code_type}->>#{code} AS code_value, 
FROM 
    code_lookup AS l
;
```

## 2.5. ログちゃんぽん

スキーマレス(Schemaless)なストアとは、スキーマを選ばない(Schema Independent)ストアであるともいえる。

たとえば Fluentd の出力を PostgreSQL に保存することを考える。
Fluentd の出力するログレコードは JSON 形式なのでそのまま jsonb 型のカラムに格納できる。
しかもたった一つのテーブルで、スキーマの異なる複数のサービスのログを保存できる。

JSON　型で格納する ProstreSQL 用の出力プラグインはすでにあって、たとえば[fluent-plugin-pgjson](https://github.com/choplin/fluent-plugin-pgjson)では以下の様なシンプルなテーブルを定義になる。

```sql
CREATE TABLE fluentd (
    tag Text
    ,time Timestamptz
    ,record jsonb
);
```

このログレコードは SQL で集計・分析ができる。
以下のようなクエリで、まとめてエラーログを取得するここともできる。
問題が発生したときに、各サービスレイヤーからのログファイルを慌ててかき集めずとも、一括でログレコードが時系列に並べられてうれしいかもしれない。

```sql

-- 直近15秒のエラーを検索する
SELECT * FROM fluentd
WHERE stamp > date_trunc('sec', now()) - interval(' 15 sec ')
AND ( record @> '{"code":500}'
   OR record @> '{"level":"error"}'
   OR record @> '{"priority":"ERR"}'
   OR record @> '{"error_severity":"ERROR"}'
)
ORDER BY stamp DESC
;
```

### 参考
* choplin/fluent-plugin-pgjson - github
https://github.com/choplin/fluent-plugin-pgjson

* PostgreSQLのログをFluentdで収集する - Tech-Sketch
http://tech-sketch.jp/2014/07/postgresqlfluentd.html

## 2.6. 監査ログテーブル

スキーマレスなデータのちゃんぽんが役に立ちそうなもう一つの例として監査ログがある。

どんなテーブルのデータでも JSON にシリアライズして保存すれば、汎用的な監査テーブルひとつで十分になる。

```sql

-- 汎用監査履歴テーブル
DROP TABLE IF EXISTS audit_history;
CREATE TABLE audit_history (
    tstamp timestamptz,
    event text,
    user_name text,
    table_name text,
    data jsonb
);

-- トリガーで更新を記録する
CREATE OR REPLACE FUNCTION auditor() RETURNS trigger  
AS $$
DECLARE
   jb_data jsonb;
BEGIN
    jb_data := CASE TG_OP
        WHEN 'INSERT'  THEN row_to_json(NEW)
        WHEN 'UPDATE'  THEN row_to_json(NEW) 
        WHEN 'DELETE'  THEN row_to_json(OLD)  -- NEWなし
        ELSE NULL
    END;
    INSERT INTO audit_history VALUES(
        current_timestamp, TG_OP, current_user, TG_TABLE_NAME, jb_data
    );
    RETURN NULL;  -- AFTERトリガー用
END;
$$ LANGUAGE plpgsql;
```

監査関数はどのテーブルにも適用でき、 jsonb_populate_record() で元のテーブルスキーマに復元できる。

```sql:使用例

CREATE TRIGGER foo_audit_trigger
    AFTER INSERT OR UPDATE OR DELETE ON foo
    FOR EACH ROW  EXECUTE PROCEDURE auditor();

CREATE TRIGGER bar_audit_trigger
    AFTER INSERT OR UPDATE OR DELETE ON bar
    FOR EACH ROW  EXECUTE PROCEDURE auditor();

-- 監査履歴を参照するときに、テーブルのレコード型に戻す
SELECT a.stamp, a.event, f.* 
FROM audit_history AS a, jsonb_populate_record(null::foo, a.data) AS f
ORDER BY stamp

```

これでは満足できない。
ここやはり、更新レコード全体ではなく、その都度の差分だけ保存できるようにしないとディスクがモッタイナイと考えるのが日本人の血統だ。

jsonb には hstore とは違って操作演算子・関数が不十分なので、差分を抽出する操作が複雑になる。

```sql

-- 汎用監査履歴テーブル
DROP TABLE IF EXISTS audit_history;
CREATE TABLE audit_history (
    tstamp timestamptz,
    txid bigint,             -- トランザクションID
    event text,              -- INSERT, UPDATE, DELETE
    app_name text,           -- クライアント接続時に指定できる。
    user_name text,          -- ユーザ名
    rspec jsonb,             -- レコード特定情報 {"schema":"public", "table":"emp", "pkey":{"emp_id":1234}}
    diff jsonb               -- 変更差分
);

-- 更新レコードから差分カラムだけを監査ログに記録するトリガー
CREATE OR REPLACE FUNCTION auditor() RETURNS trigger    
AS $auditor$
DECLARE
   jb_data jsonb;     -- 更新レコード
   jb_diff jsonb;   -- 差分データ
   jb_rspec jsonb;    -- レコード特定情報
   app_name text;
BEGIN

    -- AFTER 専用
    -- 変更内容はBEFOREよりAFTERのほうが確実。
    IF TG_WHEN <> 'AFTER' THEN
       RAISE EXCEPTION 'Trigger %: Table %: Ignored when %. ', TG_NAME, TG_TABLE_NAME, TG_WHEN;
    END IF;

    CASE TG_OP
    WHEN 'INSERT' THEN 
        jb_data := to_json(NEW)::jsonb;
        jb_diff := jb_data;
    WHEN 'UPDATE' THEN 
        jb_data := to_json(NEW)::jsonb;
        -- 更新なら NEW - OLD　の差分を取得し、JSON にまとめる
        -- TODO 関数化
        SELECT json_object_agg(key, value)::jsonb
        FROM (
            SELECT * FROM jsonb_each( to_json(NEW)::jsonb)
            EXCEPT ALL
            SELECT * FROM jsonb_each( to_json(OLD)::jsonb)
        ) AS kv
        INTO jb_diff;
    WHEN 'DELETE' THEN 
        jb_data := to_json(OLD)::jsonb;
        jb_diff := jb_data;
    ELSE
       RAISE WARNING 'Trigger %: Ignored event %. ', TG_NAME, TG_OP;
       RETURN NULL;
    END CASE;

    -- 差分データからはプライマリキーが失われてしまうので別に取っておく必要がある。
    -- TODO 関数化
    WITH pk (col) AS (
        -- プライマリキーのカラム名を取得する。
        SELECT cu.column_name
        FROM pg_constraint AS c
        INNER JOIN information_schema.constraint_column_usage AS cu
          ON (c.contype, c.conname) = ('p', cu.constraint_name)
        WHERE c.conrelid = TG_RELID
    ),
    pkv (key, val) AS (
        -- プライマリキーの値を取得する
        SELECT pk.col, jb_data->pk.col FROM pk
    ),
    rspec AS (
        SELECT 
            TG_TABLE_SCHEMA AS "schema", 
            TG_TABLE_NAME AS "table", 
            json_object_agg(pkv.key, pkv.val) AS pkey
        FROM pkv
    )
    SELECT to_json(rspec)::jsonb FROM rspec
    INTO jb_rspec; 
    -- {"schema":"public", "table":"emp", "pkey":{"emp_id":1234}}
    

    -- アプリケーション名を取得
    SELECT setting FROM pg_settings WHERE name = 'application_name' 
    INTO app_name;

    -- 差分レコードを追加
    INSERT INTO audit_history VALUES(
        current_timestamp, txid_current(), TG_OP, app_name, current_user, jb_rspec, jb_diff
    );

    RETURN NULL;  -- AFTER
END;
$auditor$ LANGUAGE plpgsql;

```

トリガーにしては重そうすぎるな。

```sql:使用例
-- サンプルテーブルを定義し、監査トリガーを設定。
CREATE TABLE abc (
  abc_id serial PRIMARY KEY,
  aaa text, bbb text, ccc text
);
CREATE TRIGGER abc_audit
AFTER INSERT OR UPDATE OR DELETE ON abc 
  FOR EACH ROW EXECUTE PROCEDURE auditor();

CREATE TABLE xyz (
  xyz_id serial PRIMARY KEY,
  xxx int, yyy int, zzz int
);
CREATE TRIGGER xzy_audit
AFTER INSERT OR UPDATE OR DELETE ON xyz 
  FOR EACH ROW EXECUTE PROCEDURE auditor();

-- テーブルの更新
INSERT INTO abc(aaa, bbb, ccc) VALUES('boo', 'foo', 'woo');
INSERT INTO xyz(xxx, yyy, zzz) VALUES(1, 2, 3);
INSERT INTO abc(aaa, bbb, ccc) VALUES('AAA', 'BBB', 'CCC');
UPDATE abc SET aaa = 'BOO' WHERE abc_id = 1;
UPDATE abc SET (bbb, ccc) = ('FOO', NULL) WHERE abc_id = 1;
UPDATE abc SET bbb = 'FOO' WHERE abc_id = 1;          -- No effect
DELETE FROM abc WHERE abc_id = 1;
UPDATE xyz SET zzz = 100 WHERE xyz_id = 1;


-- 更新履歴の差分のみが JSON で保存されている。
SELECT rspec->>'table' AS "table", diff 
FROM audit_history 
ORDER BY tstamp;

 table |                          diff
-------+---------------------------------------------------------
 abc   | {"aaa": "boo", "bbb": "foo", "ccc": "woo", "abc_id": 1}
 xyz   | {"xxx": 1, "yyy": 2, "zzz": 3, "xyz_id": 1}
 abc   | {"aaa": "AAA", "bbb": "BBB", "ccc": "CCC", "abc_id": 2}
 abc   | {"aaa": "BOO"}
 abc   | {"bbb": "FOO", "ccc": null}
 abc   | {}
 abc   | {"aaa": "BOO", "bbb": "FOO", "ccc": null, "abc_id": 1}
 xyz   | {"zzz": 100}
(8 rows)

-- 差分から更新履歴を復元する。
-- 関数化したかったが断念。
WITH 
df AS (
    SELECT tstamp, event, diff FROM audit_history
    WHERE rspec @> '{"table": "abc", "pkey":{"abc_id":1}}'  -- テーブル名とキーを指定する。
),
jb AS (
    SELECT DISTINCT
        df.*, 
        json_object_agg(kv.key, kv.value) OVER (ORDER BY df.tstamp) ::jsonb  AS rec
    FROM df, jsonb_each(df.diff) AS kv
)
SELECT jb.tstamp, jb.event, r.*
FROM jb, jsonb_populate_record( NULL::abc, jb.rec) AS r -- テーブルの型を指定する 
ORDER BY jb.tstamp
;
            tstamp             | event  | abc_id | aaa | bbb | ccc
-------------------------------+--------+--------+-----+-----+-----
 2014-09-21 11:58:28.729355+09 | INSERT |      1 | boo | foo | woo
 2014-09-21 11:58:28.779535+09 | UPDATE |      1 | BOO | foo | woo
 2014-09-21 11:58:28.799599+09 | UPDATE |      1 | BOO | FOO |
 2014-09-21 11:58:28.829915+09 | DELETE |      1 | BOO | FOO |
(4 rows)

```

## 2.7. ポリシーカラム

状況によってカラムへのアクセスを制御したい場合がある。

たとえば、セキュリティ上ユーザ権限によって特定カラムの値を取得しないようにしたい。
もちろんアプリ側のプログラムロジックで制御すればいいのだが、json_populate_record() 系関数を使えば JSON でマスクすることもできる。
マスク用の JSON は実行時にパラメータで指定できるので、設定ファイル等で管理できる。

```sql
-- private
SELECT s.* FROM users,  json_populate_record(users, '{}') AS s
WHERE users.id = 1;
id | nick_name | real_name |  password  |       email
---+-----------+-----------+------------+-------------------
 1 | Qiita     | キー太    | 1234565678 | qiita@example.com
1 row)

-- protected
SELECT s.* FROM users,  json_populate_record(users, '{"password":"****"}') AS s
WHERE users.id = 1;
 id | nick_name | real_name | password |       email
----+-----------+-----------+----------+-------------------
  1 | Qiita     | キー太    | ****     | qiita@example.com
(1 row)

-- public
SELECT s.* FROM users,  json_populate_record(users, '{"password":null, "real_name":null}') AS s
WHERE users.id = 1;
 id | nick_name | real_name | password |       email
----+-----------+-----------+----------+-------------------
  1 | Qiita     |           |          | qiita@example.com
(1 row)

```

逆に、テーブルの特定カラムの内容をダミーにしておいて、JSON に持たせたデータでカラムを上書きするという使い方も考えられる。

```sql:任意のカラムを暗号化する例

-- ポリシーで暗号化カラムを指定する
CREATE TABLE user_info (
  id        int PRIMARY KEY,
  name      text NOT NULL, 
  email     text,
  phone     text,
  address   text,
  cc_name   text,
  cc_number text,
  :
  :
  secret    bytea   -- 任意の機密カラムをまとめてJSON化したうえで暗号化しておく。
);

-- 機密カラムにはダミーを入れておく。
SELECT *
FROM user_info WHERE id = 1111
;
  id  | name | ... | cc_name | cc_number | ... | secret            
------+------+-----+---------+-----------+ ----+-------------------
 1111 | hoge | ... |****     |****       | ... | \xe5a4aae9838e... 

-- 暗号化されたJSONを復号し、カラムを上書きする。
SELECT cf.*
FROM user_info, 
     json_populate_record(user_info, 
          pgp_sym_decrypt(user_info.secret, 'key123')
     ) AS cf
WHERE user_info.id = 1111
;
  id  | name | ... | cc_name  |    cc_number     | ...
------+------+-----+----------+------------------+---
 1111 | hoge | ... | niconico | 1234567812345678 | ...

```

ただし、データの更新や管理が簡単にはいかない。


## 2.8. データ交換



# 3. SQL for JSON

次期リリース PostgreSQL 9.5 では jsonb の連結やフィールドの追加削除もできるようになるようだ。

* PostgreSQL: Documentation: 9.5: JSON Functions and Operators  
  http://www.postgresql.org/docs/9.5/static/functions-json.html

新機能を五月雨式に拡充するやり方がポスグレ流なのかは知らないが、これでようやく一通りの操作に十分な機能そろうことになる。

プログラマ的にはもはや、JSON が使える SQL というより、SQL が使える JSON と言っていい。
しかもナンチャッテではない本物の SQL が使える JSON プラットフォームだ。

その自由度は、ある種の技術的臨界を超えて新たなフレームを PostgreSQL 上に出現させる可能性をも秘める。
その辺の妄想については、9.5 のリリースを待って次回検証の機会に持ち越そう。


# 4. まとめ

なし

# 参考

各セクション内に追記した。