Edited at

正規化された複数のテーブルに JSON の値を反映する

去年のアドベントカレンダーではPostgreSQL で JSON を組み立てるというのを書いたので、今年は JSON からテーブルへ値を入れるにはどうしたらいいかというのを書きます

CREATE TABLE parents (

parent_id bigserial PRIMARY KEY,
"name" varchar NOT NULL UNIQUE
);

CREATE TABLE children (
child_id bigserial PRIMARY KEY,
"name" varchar NOT NULL UNIQUE,
parent_id bigint NOT NULL REFERENCES parents(parent_id)
);

あれなので、function を作ります

CREATE OR REPLACE FUNCTION upsert_parent(parent json) RETURNS TABLE(parent json)

AS $$
with parent_candidates AS ( -- 引数の JSON を非正規形のテーブルに変換
SELECT *
FROM json_to_record(
$1::JSON
) AS p("name" varchar, children json)
)
, children_candidates AS ( -- 第一正規形っぽく変換
SELECT p."name" AS parent_name
, c.*
FROM parent_candidates AS p
, json_to_recordset(p.children) AS c("name" varchar)
)
, new_parents AS ( -- parent の値を INSERT する
INSERT INTO parents("name")
SELECT p."name"
FROM parent_candidates AS p
ON CONFLICT DO NOTHING
RETURNING * -- 既存の値だったら何も返さない
)
, exist_parents AS ( -- parent が既存の値だったら、それを返す
SELECT parents.*
FROM parents
JOIN parent_candidates USING("name")
)
, current_parents AS ( -- 新規の値か既存の値、どちらかが必ず返るはず
SELECT * FROM new_parents
UNION
SELECT * FROM exist_parents
)
, new_children AS ( -- children の値を INSERT する。既存の値だったら parent_id を更新
INSERT INTO children("name", parent_id)
SELECT c."name"
, p.parent_id AS parent_id
FROM children_candidates AS c
JOIN current_parents AS p ON parent_name = p."name"
ON CONFLICT("name") DO UPDATE SET parent_id = EXCLUDED.parent_id
RETURNING * -- 新規でも既存でも全部返る
)
, destroy_useless_children AS ( -- parent に関連する、入力されなかった child を消す
DELETE FROM children AS c
USING current_parents AS cur
WHERE c.parent_id = cur.parent_id
AND c."name" NOT IN (SELECT "name"
FROM new_children)
)

SELECT (json_agg(t)->>0)::JSON AS parent
FROM (
SELECT p.parent_id
, p."name"
, json_agg(children) AS children
FROM new_children AS children
JOIN current_parents AS p USING(parent_id)
GROUP BY 1, 2
) AS t
$$ LANGUAGE sql
;

これを使うと、関数に JSON の値を入れるだけで、イイカンジに分解して各テーブルに INSERT したり UPDATE したりして、最終的にどういう感じになったかをまとめた JSON の値を返してくれるようになります

以下が例です

SELECT upsert_parent('{"name":"hoge","children":[{"name":"a"},{"name":"b"}]}'::json);

--{
-- "parent_id":1,
-- "name":"hoge",
-- "children":[
-- {"child_id":1,"name":"a","parent_id":1},
-- {"child_id":2,"name":"b","parent_id":1}
-- ]
--}
SELECT parents."name"
, children."name"
FROM children
JOIN parents USING(parent_id);
-- name | name
-- ------+------
-- hoge | a
-- hoge | b

SELECT upsert_parent('{"name":"fuga","children":[{"name":"c"},{"name":"d"}]}'::json);
-- {
-- "parent_id":2,
-- "name":"fuga",
-- "children":[
-- {"child_id":3,"name":"c","parent_id":2},
-- {"child_id":4,"name":"d","parent_id":2}
-- ]
-- }
SELECT parents."name"
, children."name"
FROM children
JOIN parents USING(parent_id);
-- name | name
-- ------+------
-- hoge | a
-- hoge | b
-- fuga | c
-- fuga | d

SELECT upsert_parent('{"name":"piyo","children":[{"name":"e"},{"name":"a"}]}'::json);
-- {
-- "parent_id":3,
-- "name":"piyo",
-- "children":[
-- {"child_id":5,"name":"e","parent_id":3},
-- {"child_id":1,"name":"a","parent_id":3}
-- ]
-- }
-- child 'a' を parent 'hoge' から奪っている
SELECT parents."name"
, children."name"
FROM children
JOIN parents USING(parent_id);
-- name | name
-- ------+------
-- hoge | b
-- fuga | c
-- fuga | d
-- piyo | e
-- piyo | a

SELECT upsert_parent('{"name":"hoge","children":[{"name":"d"},{"name":"e"}]}'::json);
-- {
-- "parent_id":1,
-- "name":"hoge",
-- "children":[
-- {"child_id":4,"name":"d","parent_id":1},
-- {"child_id":5,"name":"e","parent_id":1}
-- ]
-- }
-- child 'd' を parent 'fuga' から奪っている
-- child 'e' を parent 'piyo' から奪っている
-- child 'b' を破棄している
SELECT parents."name"
, children."name"
FROM children
JOIN parents USING(parent_id);
-- name | name
-- ------+------
-- fuga | c
-- piyo | a
-- hoge | d
-- hoge | e

SELECT upsert_parent('{"name":"fuga","children":[{"name":"c"},{"name":"d"}]}'::json);
-- {
-- "parent_id":2,
-- "name":"fuga",
-- "children":[
-- {"child_id":3,"name":"c","parent_id":2},
-- {"child_id":4,"name":"d","parent_id":2}
-- ]
-- }
-- child 'd' を parent 'hoge' から奪い返している
SELECT parents."name"
, children."name"
FROM children
JOIN parents USING(parent_id);
-- name | name
-- ------+------
-- piyo | a
-- hoge | e
-- fuga | c
-- fuga | d

こういう感じで、なんでもかんでも1回の SQL で済まそうとすると、現場によっては怒られるので注意して下さい

なお、今日は、わしの誕生日なので、よろしくお願いします