json_app 関数で結果を JSON や JavaScript オブジェクト で出力

普通ではSELECT文などを実行すうと以下のようなフォーマットで出力されます。

 id | name 
----+------
  1 | foo

この出力を JSON にする方法を見ていきます。この方法では jq または Node.js を使います。

共通部分

json_app関数を使うと、出力のレコード部分を JSON フォーマットにできます。

psql --command \
  'select json_app(users) from users'
#            json_agg         
# --------------------------
#  [{"id":1,"name":"foo"}, +
#  {"id":2,"name":"bar"}]

好きなキーだけに絞りこみたい場合はFROMにサブクエリで、期待するカラムだけ返すクエリを渡します。

psql --command \
  'select json_agg(names) from (select name from users) as names'
#      json_agg      
# -------------------
#  [{"name":"foo"}, +
#  {"name":"bar"}]

上のカラム名部分が邪魔なので消したいですね。それには--tuples-onlyフラグを使います。

psql --tuples-only --command \
  'select json_agg(names) from (select name from users) as names'
#  [{"name":"foo"}, +
#  {"name":"bar"}]

次は改行(\n)と+が邪魔なので、これを削除します。それにはtrコマンドを使います。

psql --tuples-only --command \
  'select json_agg(names) from (select name from users) as names' \
| tr -d '\n+'
#  [{"name":"foo"},  {"name":"bar"}]

綺麗な JSON に変換

まだスペースが連続で複数あったりで若干気になるので整えます。これは単にjqに出力を渡せば完了です!

psql --tuples-only --command \
  'select json_agg(names) from (select name from users) as names' \
| tr -d '\n+' \
| jq .
# [
#   {
#     "name": "foo"
#   },
#   {
#     "name": "bar"
#   }
# ]

JavaScript 配列オブジェクトに変換

こちらもnode -pに出漁をそのまま渡すだけです。

psql --tuples-only --command \
  'select json_agg(names) from (select name from users) as names' \
| tr -d '\n+' \
| node -p
# [ { name: 'foo' }, { name: 'bar' } ]

nodeの引数に-を指定すると、そこが標準出力で展開されます。-pフラグは評価した内容をプリントするものなので、psqlからの JSON がそのまま JavaScript コードとして評価され、配列オブジェクトで出力されます。