LoginSignup
3

More than 5 years have passed since last update.

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

Last updated at Posted at 2018-12-18

去年のアドベントカレンダーでは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 で済まそうとすると、現場によっては怒られるので注意して下さい

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
3