PostgreSQL9.5の強力なJSON関数で、ネストした構造を持つJSONを自在に組み立てる

(この記事はmixi advent calendar5日目の投稿です。)

年内にリリース予定のPostgreSQL9.5ではjsonb型のデータを操作する関数が拡充され、より自由自在にJSONを扱うことが可能となります。この記事ではPostgreSQL9.5を用い、REST APIJSON生成部分を SQLのみで 実現するサンプルコードを紹介することで、「なにこれ、こんなことできるならORM使う必要なくなるじゃん」みたいな感じで皆さんに驚いてもらうことを目的としています。[1]

以下、次に示すスキーマを持つブログサービスのREST APIを題材として話を進めます。

create table "User" (
  id varchar(10),
  name varchar(20)
);

create table "Post" (
  id int,
  user_id varchar(10),
  content text
);

create table "Reply" (
  id int,
  user_id varchar(10),
  post_id int,
  message text
);

insert into "User" (id, name) values('hoge', 'ほげ'),('foo', 'ふう'),('bar', 'ばあ');
insert into "Post" (user_id, content) values(1, 'hoge', '今日は天下寿司を食べた');
insert into "Post" (user_id, content) values(2, 'foo', 'ぬるぽ');
insert into "Reply" (user_id, post_id, message) values('foo', 1, 'いいね!'),('bar', 1, '炙りサーモンある');
insert into "Reply" (user_id, post_id, message) values('bar', 2, 'ガッ');

まずはじめに、ユーザーのIDを指定してオブジェクトを取得するエンドポイント/users/:idを作ります。

select to_jsonb("User") as "/users/:id"
from "User"
where id = $1
;

このクエリは、特定のidを持つユーザーのJSON Objectを生成するクエリです。to_jsonbは様々な型の上に定義されていますが、row型を引数として渡すと(このクエリではテーブルの参照であるUserという識別子を渡しているためrow型を渡していることになります)、カラム名をキーとしたJSON Object(jsonb型)を返します。プリペアドステートメント引数$1にhogeを代入してクエリを実行した結果が以下になります。

 {"id": "hoge", "name": "ほげ"}

期待する結果になってますね!

次に、すべてのユーザーのリストを取得するエンドポイント/usersを作ります。Userオブジェクトの配列を取得するクエリは次のようになります。

select jsonb_set('{}'::jsonb, '{users}', jsonb_agg("user")) as "/users"
from (
  select to_jsonb("User") as "user"
  from "User"
) t
;

先ほど紹介したto_jsonbでUserレコードをjsonb型に変換した後、それを集約関数jsonb_aggに渡してArrayにしています。さらにそれをusersをキーとするObjectで包んでいます。

結果は下。ちゃんとデータがネストされてる!

{"users": [{"id": "hoge", "name": "ほげ"}, {"id": "foo", "name": "ふう"}, {"id": "bar", "name": "ばあ"}]}

では次に、すべての記事のリストを取得する/postsを作ります。 その際、オブジェクトにはuser_idではなく、Userオブジェクトそのものをネストして含めたいとします。クエリはこんな感じになります。

select jsonb_set('{}'::jsonb, '{posts}', jsonb_agg("post")) as "/posts"
from (
  select jsonb_set(to_jsonb("Post") - 'user_id', '{user}', to_jsonb("User")) as "post"
  from "Post"
     , "User"
  where "Post"."user_id" = "User"."id"
) t
;

to_jsonb("Post") - 'user_id'はPostをObject化した後user_idキーを取り除いています。それをjsonb_set関数に渡してuserをキーとしてUserのオブジェクトを値として追加しています。

結果は下。超便利。

 {"posts": [{"id": 1, "user": {"id": "hoge", "name": "ほげ"}, "content": "今日は天下寿司を食べた"}, {"id": 2, "user": {"id": "foo", "name": "ふう"}, "content": "ぬるぽ"}]}

さらにPostオブジェクトに、Replyオブジェクトの配列を持つrepliesフィールドを追加したいとします。クエリはこうなります。

select jsonb_set('{}'::jsonb,
                 '{posts}',
                 jsonb_agg(jsonb_set("post" - 'user_id',
                                     '{user}',
                                     to_jsonb("User")))) as "/posts"
from (
  select jsonb_set(to_jsonb("Post"), '{replies}', jsonb_agg("Reply")) as "post"
       , "Post"
  from "Post"
     , "Reply"
  where "Reply"."post_id" = "Post"."id"
  group by "Post"
) t
   , "User"
where ("Post")."user_id" = "User"."id"
;

group by "Post"でPostテーブルのレコードを1つの値として扱っているのがミソです。こういうふうに構造をネストさせるクエリが自在に書けるのがPostgresの素晴らしいところ。

結果は以下。

{
  "posts": [
    {
      "id": 1,
      "user": {
        "id": "hoge",
        "name": "ほげ"
      },
      "content": "今日は天下寿司を食べた",
      "replies": [
        {
          "id": 2,
          "message": "炙りサーモンある",
          "post_id": 1,
          "user_id": "bar"
        },
        {
          "id": 1,
          "message": "いいね!",
          "post_id": 1,
          "user_id": "foo"
        }
      ]
    },
    {
      "id": 2,
      "user": {
        "id": "foo",
        "name": "ふう"
      },
      "content": "ぬるぽ",
      "replies": [
        {
          "id": 3,
          "message": "ガッ",
          "post_id": 2,
          "user_id": "bar"
        }
      ]
    }
  ]
}

(結果は見やすいようにprettifyしています。)

ついでに、集約関数を使ってみたいので、postオブジェクトに返信の数を表すreply_countというフィールドを追加してみます。 7行目を以下のように変更します。

-  select jsonb_set(to_jsonb("Post"), '{replies}', jsonb_agg("Reply")) as "post"
+  select jsonb_set(jsonb_set(to_jsonb("Post"), '{replies}', jsonb_agg("Reply")), '{reply_count}', to_jsonb(count("Reply"."id"))) as "post"

実行結果は以下です。

{
  "posts": [
    {
      "id": 1,
      "user": {
        "id": "hoge",
        "name": "ほげ"
      },
      "content": "今日は天下寿司を食べた",
      "replies": [
        {
          "id": 2,
          "message": "炙りサーモンある",
          "post_id": 1,
          "user_id": "bar"
        },
        {
          "id": 1,
          "message": "いいね!",
          "post_id": 1,
          "user_id": "foo"
        }
      ],
      "reply_count": 2
    },
    {
      "id": 2,
      "user": {
        "id": "foo",
        "name": "ふう"
      },
      "content": "ぬるぽ",
      "replies": [
        {
          "id": 3,
          "message": "ガッ",
          "post_id": 2,
          "user_id": "bar"
        }
      ],
      "reply_count": 1
    }
  ]
}

SQL15行でここまで出来る、すごい!

まとめ

  • レコードをJSONに変換する場合はto_jsonbを使う
    • 行を1つの値(ROW型)として扱えるところにPostgresの妙がある
  • Objectに値を追加する場合はjsonb_set、削除する場合は-を使う
  • 複数のJSONB型を配列にするには集約関数jsonb_aggを使う

[1] ここでのORMとは、クエリを部品化して合成可能にする機能ではなく実行結果をModelと呼ばれるオブジェクトに移し替える機能のことを指しています。(例えばActiveRecordなど。)







--

以下はポエムなので適当に読み流して下さい。

RDBSのクエリって、保存された正規化されたテーブルをJOINして(=つまり非正規化して)データを見たい形/使いたい形に加工するためのものなので(参考:データベースの三層モデル) このスライドでも言及されてるけど、第一非正規なデータを返すことだって出来ていいはず。なぜ我々は正規化されたテーブルからjsonを作るのにRailsをかます必要があるのか?というのは以前から疑問に思っていたので、Postgres9.5のJSON関数に出会った時は強く感動した。

REST APIを書くときにサーバーサイドMVCは必要ないよね、っていうのは以前から思っていて、DBから取得したデータをわざわざModelオブジェクトにmappingして、クライアントに返すときにはシリアライズしているのは無駄に思える。RDBSから結果がHashのArrayで帰ってくるなら、それを引数として受け取って、データを操作する関数を書けばそのほうがシンプルにすむのに。そしてそれをそのままJSONに変換するだけでいいじゃん。

そういうわけで、JSON APIを書こうとした時に、データの加工という本来のDBの役割をPostgresはしっかり果たしてくれるわけです。ネストしたデータ構造だって自在に組み立てれるんです。不要なレイヤを挟まずにアーキテクチャをシンプルに保てるというのは素晴らしいことだと思うのです。

あと、Postgresのクエリの表現力はすごいというのを伝えたい。MySQL5.7にもJSON対応が入ったけど、上で紹介したようなことは出来ない。Postgresにはrow型が存在しているおかげでネストしたJSONを取得するクエリがシンプルに書ける。この記事では紹介出来てないけど、他にも多様なJSON操作関数があって、それが実現できているのはひとえにクエリの表現力のおかげ。LTした時のスライドで紹介したけど、ネストしたJSONをそのままjsonb型に突っ込んで、JSON内部のArrayに対して集約関数を実行するみたいなこともPostgreだからできる。

パフォーマンスの部分でMySQLにかなわない部分はあるとは思うけど、RDBの正当な進化の結果として現代的なJSONの機能を手にしており、ソースも常に読みやすくメンテナンスされており、Postgresはすごい。

以上、駄文失礼しました。

Raspberry PiとUSB-DACを組み合わせて格安で便利なAVアンプを作った

f:id:tacke_jp:20140121065919j:plain

なにこれ?

Raspberry PiにUSB-DACを接続して、持っていたアンプで鳴らしてみたら思った以上にいい音が出た。 AirPlayサーバー化すると、iPhoneに入っている曲やYoutubeやニコ動の作業用BGMを飛ばして聴ける。 VLCをインストールすると、ネットラジオを聴いたりNASにあるファイルを再生したりブラウザから曲選択や音量調整が出来るようになる。 ベットでごろ寝しながらすべてを操作できるの、非常に便利。 しかもRaspberry Piと既存のアンプやコンポがあれば低コストで実現できるので、 ノウハウを公開します!

はじめに

AirPlay対応のAVアンプって便利ですよね。 寝転がりながらiPhoneを使ってYoutubeニコニコ動画の作業用BGMを気分で選べばそれを高音質で再生してくれるし、 ネットラジオなどのインターネット経由のストリーミング再生もこなしてくれます。 ものによってはネットワークストレージに保存されている音楽ファイルを再生することも出来ます。 けどお値段はそれなりにしますよね…

(参考:ONKYO TX-NR626)

4万円もするならそのお金で〇〇でも買う!なんて買い渋っている人も多いんじゃないでしょうか。 そこで、あなたの押入れに眠っている未開封のRaspberry Piと久しく鳴らしていないアンプを使って、 低コストで便利なAVアンプを作成しましょう!

必要なもの

Raspberry Pi

f:id:tacke_jp:20140121065953j:plain

RSオンラインなどから通販で購入することが出来ます。

以下の3つも用意する必要があります。

  • Wifiドングル (無ければLANケーブルで接続)
  • SDHCカード (自分はclass10などの高速なものを購入しました)
  • USBキーボード (USBマウス) (Raspberry PiにOSをインストールする際に必要になります)

USB-DAC

f:id:tacke_jp:20140121070054j:plain

DAC = Digital Analog Convertor
デジタルオーディオデータを入力すると、アナログ音源に変換して出力します。

USB-DACの場合、PCM音源をUSBで転送するとアナログに変換して出力してくれるので、 それを既存のアナログアンプなどのオーディオリソースにつなげて再生することが出来ます。

僕は安く済ませるため大須(1)で発見した組み立てキットAKI-DAC-U2704を1800円で購入しました。 このキットに搭載されているPCM2704音質に定評があるらしいです。 電子工作素人だったのでハンダ付けに少し苦戦しました。コテ先が細いハンダごてを利用するといいと思います。

音質にこだわらないのであれば、Raspberry Piのステレオミニ出力を直接アンプに流すでもいいでしょう。 ただ、アンプ側のインピーダンスと合わない場合ちっとも大きな音が出ないため、 その場合はUSB-DACを利用するとかオペアンプを使うとかしないといけないです。

もっと音質にこだわりたいなら、ヘッドホン向けに売られている高級DACを利用するのもいいかもしれません。

アンプ(コンポ)

SANSUI AU-707

親父から譲り受けた、いにしえのアンプを使いました。 なんと1977年頃の代物だそうです。 SANSUIは定評のあるメーカーだったそうで、 音が鳴らなくなっていたのをメンテしてもらったら、 (父曰く)元通りのいい音を出してくれるようになりました。

レコードやカセットなどの古きよきアナログ時代の頃のアンプや、 インターネットの波に取り残されて押入れに眠っているCD・MDコンポなどを利用するといいと思います。

費用

かかった費用は僕の場合はRaspberry PiとアンプとSDHCカードは既に持っていたので、

のみ購入で3000円程度です。安い!

手順

Raspberry Piのセットアップ

http://lifehacker.com/5976912/a-beginners-guide-to-diying-with-the-raspberry-pi を参考にしました。 以下、MacOSXでの操作を想定しています。

まず、Raspberry Pi向けのLinuxディストリビューションであるRaspbianのディスクイメージをSDHCカードに書き込みます。

SDHCカードを挿入したのち、ディスクユーティリティを利用して自動でマウントされたパーティションをアンマウントします。

$ wget http://downloads.raspberrypi.org/raspbian_latest -O raspbian.zip

$ unzip -x raspbian.zip

$ mount # SDHCカードのディスク番号の確認
/dev/disk0s2 on / (hfs, local, journaled)
devfs on /dev (devfs, local, nobrowse)
map -hosts on /net (autofs, nosuid, automounted, nobrowse)
map auto_home on /home (autofs, automounted, nobrowse)
/dev/disk2s1 on /Volumes/SDCard (exfat, local, nodev, nosuid, noowners)
# SDHCカードのディスク番号が2であることがわかった

$ sudo dd if=20YY-MM-DD-<name>-raspbian.img of=/dev/rdiskN bs=1m # N=先程調べたディスク番号

SDHCカードWifiドングル・USBキーボード・HDMIケーブルを接続し起動させます。 (電源を挿すと自動的に起動します。)

起動が完了すると設定画面になるので、次のことを行いましょう。

SDHCに書きこまれたディスクイメージは???MBのパーディションしか持っていないため、 SDHCカードの全領域を利用出来るようにパーティション容量を拡張します。

  • sshdの設定

sshdの設定を行うことで、以後の設定をリモートで行うことが出来るようにします。

  • GUIを無効化

CPUリソースを消費しそうな不要な機能は切っておきます。

USB-DACの接続

僕が利用したAKI-DAC-U2704は、接続しただけで認識してくれました。 ものによってはドライバのインストールが必要になるかもしれません。

USB-DACが正しく認識されたかどうかを確認するためには、以下のようにします。

pi@raspberrypi ~ $ cat /proc/asound/cards
 0 [ALSA           ]: BRCM bcm2835 ALSbcm2835 ALSA - bcm2835 ALSA
                      bcm2835 ALSA
 1 [DAC            ]: USB-Audio - USB Audio DAC
                      Burr-Brown from TI USB Audio DAC at usb-bcm2708_usb-1.2, full speed

0番目はRaspberry Piのオンボードサウンドカードです。 1番目に接続したDACが表示されていれば正しく認識されています。

オーディオデバイスの一覧を表示するためには、次のようにします。 オーディオデバイス名は、のちのAirPlayサーバーやVLCの利用の際に必要になります。

pi@raspberrypi ~ $ aplay -L
null
    Discard all samples (playback) or generate zero samples (capture)
default:CARD=ALSA
    bcm2835 ALSA, bcm2835 ALSA
    Default Audio Device
sysdefault:CARD=ALSA
    bcm2835 ALSA, bcm2835 ALSA
    Default Audio Device
default:CARD=DAC # これがUSB-DACのオーディオデバイス名
    USB Audio DAC, USB Audio
    Default Audio Device
...

また、ncurseベースのalsamixerコマンドで音量を調整することが出来ます。

pi@raspberrypi ~ $ alsamixer -c 1

f:id:tacke_jp:20140121053942p:plain

AirPlayサーバー化する

オープンソースAirTunesエミュレータshairportを利用します。

インストールの手順は、 http://audioplastic.org/blog/2013/01/10/AP1/ を参考にしました。

大まかな手順としては、

  • aptを利用して必要なライブラリをインストール
  • CPANから必要なPerlライブラリをインストール
  • shairportのビルド
  • shairportの起動 という流れになります。
pi@raspberrypi ~ $ apt-get install git libao-dev libssl-dev libcrypt-openssl-rsa-perl libio-socket-inet6-perl libwww-perl avahi-utils
pi@raspberrypi ~ $ sudo cpan install Net::SDP
pi@raspberrypi ~ $ git clone https://github.com/albertz/shairport.git shairport
pi@raspberrypi ~ $ cd shairport
pi@raspberrypi ~ $ make
pi@raspberrypi ~ $ ./shairport.pl -a AirPi --ao_devicename <オーディオデバイス名>

この状態で、iPhoneを確認すると、AirPiが選択できるようになってます。

f:id:tacke_jp:20140121054729p:plain

やったね!

Raspberry Piの起動時に自動的にデーモンとして起動させるには、次のようにします。

pi@raspberrypi ~ $ make install
pi@raspberrypi ~ $ sudo cp shairport.init.sample /etc/init.d/shairport
pi@raspberrypi ~ $ cd /etc/init.d
pi@raspberrypi ~ $ sudo chmod a+x shairport
pi@raspberrypi ~ $ sudo update-rc.d shairport defaults

けど、僕はtmux上で起動してssh接続を終了するときはデタッチするようにして使っています。

VLCを利用する

VLCは、主にLinuxで用いられているフリーの音楽/動画再生ソフトです。 GUIのみならずCUIやWebブラウザからの操作にも対応しています。 多機能なためRaspberry Piと組み合わせると非常に便利です。

VLCのインストール

pi@raspberrypi ~ $ sudo apt-get install vlc

SDHCカードに保存した曲を再生する

mp3ファイルの再生

pi@raspberrypi ~ $ vlc --aout alsa --alsa-audio-device <オーディオデバイス名> ~/Desktop/Perfume.mp3

ネットラジオを再生する

例として、http://www.listenlive.eu/jazz.html のClassic Jazzを再生します。

pi@raspberrypi ~ $ vlc --aout alsa --alsa-audio-device <オーディオデバイス名> http://broadcast.infomaniak.ch/jazz-wr01-128.mp3.m3u

ネットワークストレージに保存している曲を再生する

(未執筆: NTSSambaを利用する)

Webブラウザから操作する

https://wiki.videolan.org/Control_VLC_via_a_browser/ を参考にしました。

事前に/usr/share/vlc/lua/http/.hostsを編集して、自分(=Raspberry Pi)以外からのアクセスを許可しておく。

--extraintf=http --http-portフラグをセットしてvlcを起動。 デフォルトでは8080ポートを使いますが、--http-portフラグで変更可能です。

f:id:tacke_jp:20140121054020p:plain

このように、曲の再生・一時停止・音量調整や、 プレイリストや曲の選択などがブラウザからできるようになります。 iPhoneからだと、スマホ対応ページが表示されます。

f:id:tacke_jp:20140121054201p:plain

オフィスフロアやシェアハウスのオーディオの場合、 このように操作インターフェースを公開して、みんなが自由に流したい曲を登録できるようにすると楽しそうです。

おわりに

もともと、親父が昔から大切にしていたアンプを譲り受けて、 それをどう活用しようか悩んでいたところに @kikuchy のやりたいこと・作りたいものリストが目に止まって、 これだ!と思ってRaspbery Piと組み合わせてみたのが作成するきっかけでした。

現状の問題点としては、iPhoneで動画再生すると映像に対して音が少し遅れてしまいます。 Wifiでデータを飛ばしていたりRaspberry Piの処理速度のせいだとは思いますが、 違和感がない程度までディレイを減らせるようにチューニングするのが課題です。

思いつきと少しの労力で素晴らしいオーディオ環境を手に入れることができました。 みんなもオーディオライフをHACKしよう!

参考にしたサイト