Edited at

JOINのあるSQL結果をJSONにしたかったけれどRubyとPythonでサッとわからなかったのでClojureでやってみた。

とある帳票をつくるサービスがJSONを受け付ける形式であり、

検証にあたりSQLの結果から1対多のJSONが欲しかったのですが、DBのクライアントツールではサッと見当たらず

RubyかPythonでそんなものを作るライブラリなりコードなりありそうだなと思ったのですが、(多分あるとは思うのですが)

意外とコレといったものがなかったので勉強中のClojureで簡単なものを書いてみました。

先に申し上げますと、サービスに必要なJSONはフラットな階層でも工夫すれば問題ありませんでしたが。


そもそも欲しい結果

以下の関係の2つのTABLE、staffdepartmentから


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を書いて


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関連の情報は以下のリンクでほぼ揃います。(お世話になっています。)

Clojure/ClojureScript関連リンク集


私の知識


  • Javaを業務で使い3年目くらい

  • RubyとPythonは基本的な構文はわかり、https://www.codewars.com の問題をたまに挑戦する。

  • Clojure入門2、3ヶ月くらい


ライブラリの追加


project.clj



:dependencies [[org.clojure/clojure "1.10.0"]
[clj-postgresql "0.7.0"]
[cheshire "5.8.1"]]



本体部

ライブラリの設定とDB接続情報とSQLを簡潔にするラッパーを書いておく


json-try.cljの冒頭



(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ファイルを吐くまで


json-try.clj続き



;結果をそれぞれ束縛しておく
(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を後ろに起き、紐づく2つのスタッフが取得される。




結果


result.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": "営業",
"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関数を書くのも1−2時間かかったのでまぁ楽ではなかったですし、もっといい書き方がきっとあるのでしょう。精進します。

以上です。