とある帳票をつくるサービスがJSONを受け付ける形式であり、
検証にあたりSQLの結果から１対多のJSONが欲しかったのですが、DBのクライアントツールではサッと見当たらず
RubyかPythonでそんなものを作るライブラリなりコードなりありそうだなと思ったのですが、（多分あるとは思うのですが）
意外とコレといったものがなかったので勉強中のClojureで簡単なものを書いてみました。
先に申し上げますと、サービスに必要なJSONはフラットな階層でも工夫すれば問題ありませんでしたが。
そもそも欲しい結果
以下の関係の２つのTABLE、staffとdepartmentから
staff
| id | last_name | first_name | age | department_id |
|---|---|---|---|---|
| 1 | taro | yamada | 37 | 1 |
| 2 | ichiro | suzuki | 30 | 1 |
| 3 | hanako | hujiwara | 25 | 2 |
department
| id | name |
|---|---|
| 1 | 人事 |
| 2 | 営業 |
以下のようなSQLを書いて
select * from staff s
inner join department dep
on s.department_id = dep.id;
以下のようなJSONがサッと欲しかったです。
[
{
"id": 1,
"name": "人事",
"staffs": [
{
"id": 1,
"last_name": "taro",
"first_name": "yamada",
"age": 37,
"department_id": 1
},
{
"id": 2,
"last_name": "ichiro",
"first_name": "suzuki",
"age": 30,
"department_id": 1
}
]
},
{
"id": 2,
"name": "営業",
"staffs": [
{
"id": 3,
"last_name": "hanako",
"first_name": "hujiwara",
"age": 25,
"department_id": 2
}
]
}
]
SQLの結果をそのままJSONにするのは割とどれでもあるけれど階層にするものはない？
以下のような結果はどの言語でもDBクライアントツールでもサッとできます。
初めの結果と違ってdepartmentに紐づくstaffの結果がフラットな階層です。
[
{
"id": 1,
"last_name": "taro",
"first_name": "yamada",
"age": 37,
"department_id": 1,
"name": "人事"
},
{
"id": 1,
"last_name": "ichiro",
"first_name": "suzuki",
"age": 30,
"department_id": 1,
"name": "人事"
},
{
"id": 2,
"last_name": "hanako",
"first_name": "hujiwara",
"age": 25,
"department_id": 2,
"name": "営業"
}
]
階層のある（って表現するのが正しいかはわかりませんが）JSONググってもだいたいプログラム上で普通に書いて変換しているような感じでしたので
それだったらClojureの方が楽なのでは？と思い勉強にもなると思ってプログラムしてみました。
実際に書く
準備
前提
- java 1.8
- clojure 1.10.0
- postgresql 11.2
ビルドツール
- leiningen
Jsonへ変換するClojureのライブラリ
Clojure関連の情報は以下のリンクでほぼ揃います。(お世話になっています。)
私の知識
- Javaを業務で使い３年目くらい
- RubyとPythonは基本的な構文はわかり、https://www.codewars.com の問題をたまに挑戦する。
- Clojure入門２、３ヶ月くらい
ライブラリの追加
:dependencies [[org.clojure/clojure "1.10.0"]
[clj-postgresql "0.7.0"]
[cheshire "5.8.1"]]
本体部
ライブラリの設定とDB接続情報とSQLを簡潔にするラッパーを書いておく
(ns json-try
(:require [clj-postgresql.core :as pg])
(:require [clojure.java.jdbc :as jdbc])
(:require [cheshire.core :refer :all ])
(:require [clojure.java.io :refer [writer]])
)
;DBの接続情報の設定 最低限の設定のためpasswordなし
(def playground (pg/pool
:host "localhost"
:user "user"
:dbname "playground"))
;jdbc/queryメソッドが少々冗長なためラップする。
(defn q [sql]
(jdbc/query playground sql))
JSONファイルを吐くまで
;結果をそれぞれ束縛しておく
(def staff-map (q "select * from staff"))
(def department-map (q "select * from department"))
; staff-mapとdepartment-mapから階層をつくる関数
; child-nameはJSONにしたときのキーの名前として設定 foreign-keyはSQLにおける
; on staff.depertment_id = department.idの staff側の外部キー
; 役割がinner join のようなものなので関数名もそれにすればよかったかも
(defn to-parent-child [parent-map child-name child-map foreign-key]
(let [child-group-by-foreign-key (group-by foreign-key child-map)]
(map #(assoc % child-name (child-group-by-foreign-key (:id %)))
parent-map)))
;上記関数を使用して結果を束縛
(def parent-child
(to-parent-child department-map :staffs staff-map :department_id))
;上記結果をjsonファイルとして出力（出力せずコンソールに出すことももちろん可能）
(with-open [fout (writer "./result.json")]
(generate-stream parent-child fout {:pretty true}))
※参考REPLでのstaff-mapの動きとto-parent-childの動き
(もしClojureを触らない方がこの記事をみていただいたときのサンプル)
gifでのsample
sql実行
to-parent-child関数実行
group-byのところの動き department_idをキーにしたマップにしておく
上記で作ったものからキーを後ろに書くとその結果が取れる。1を後ろに起き、紐づく２つのスタッフが取得される。
結果
[
{
"id": 1,
"name": "人事",
"staffs": [
{
"id": 1,
"last_name": "taro",
"first_name": "yamada",
"age": 37,
"department_id": 1
},
{
"id": 2,
"last_name": "ichiro",
"first_name": "suzuki",
"age": 30,
"department_id": 1
}
]
},
{
"id": 2,
"name": "営業",
"staff": [
{
"id": 3,
"last_name": "hanako",
"first_name": "hujiwara",
"age": 25,
"department_id": 2
}
]
}
]
Clojureでやろうと思った発想のきっかけ
ClojureでのDBを扱う基本であると思われるclojure.java.jdbcを
使用してSQLをREPLで実行したときそのまま画面上にMapとして返ってきたので「これは気持ちがいい、、Map（連想配列、辞書型とも）で返ってくるなら他の関数もそのまま使えるじゃないか、ORMって実際いるものだったのか？」と感想を持っていました。
冒頭の件があったときにまずRubyやPythonで探したのですが階層をもったJSONは普通にプログラムとして書いていたサンプルが多く、そもそもあまり需要、必要性のないものと思ったのですが、せっかくClojure勉強中だったのでMapで結果が変えるならJSONも割と楽に行けるのでは？というのがきっかけでした。
ただし、Clojureにまだまだ慣れていないので上記to-parent-child関数を書くのも１−２時間かかったのでまぁ楽ではなかったですし、もっといい書き方がきっとあるのでしょう。精進します。
以上です。