正規化された複数のテーブルに 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

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

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