PostgreSQLでJSONBを用いたデータ結合【PostgreSQL】

PostgreSQL

Last updated at 2025-01-01Posted at 2024-12-22

説明

データをJSONBに変換して結合する方法

1.　テーブル定義。

-- 業務日付
CREATE TABLE work_day (
    user_id SERIAL,
    work_date DATE,
    PRIMARY KEY (user_id, work_date)
);
-- 主務
CREATE TABLE work_main (
    user_id SERIAL,
    work_code VARCHAR,
    start_date DATE,
    end_date DATE,
    etc VARCHAR,
    PRIMARY KEY (user_id, work_code, start_date)
);
-- 副務
CREATE TABLE work_sub (
    user_id SERIAL,
    work_code VARCHAR,
    start_date DATE,
    end_date DATE,
    etc VARCHAR,
    PRIMARY KEY (user_id, work_code, start_date)
);

2.　サンプルデータ

-- 業務日付
user_id	work_date
1	2024/10/01
1	2024/10/02
1	2024/10/03
1	2024/10/04
1	2024/10/05
2	2024/10/01
2	2024/10/02
2	2024/10/03
2	2024/10/04
2	2024/10/05
3	2024/10/01
3	2024/10/02
3	2024/10/03
3	2024/10/04
3	2024/10/05

-- 主務
user_id	work_code	start_date	end_date
1	A001	2024/10/01	2024/10/03
1	A001	2024/10/02	2024/10/04
1	A001	2024/10/03	2024/10/05
2	B001	2024/10/01	2024/10/03
2	B001	2024/10/02	2024/10/04
2	B001	2024/10/03	2024/10/05
3	C001	2024/10/01	2024/10/03
3	C001	2024/10/02	2024/10/04
3	C001	2024/10/03	2024/10/05

-- 副務
user_id	work_code	start_date	end_date
1	X001	2024/10/01	2024/10/03
1	X001	2024/10/02	2024/10/04
1	X001	2024/10/03	2024/10/05
2	Y001	2024/10/01	2024/10/03
2	Y001	2024/10/02	2024/10/04
2	Y001	2024/10/03	2024/10/05
3	Z001	2024/10/01	2024/10/03
3	Z001	2024/10/02	2024/10/04
3	Z001	2024/10/03	2024/10/05

3.　WITHで`各テーブルの情報をJSONB形式でまとめるサブクエリを作成。
4.　業務日付とサブクエリで作成したJSONを結合して取得。

-- 各テーブルの情報をJSONB形式でまとめるサブクエリを作成
WITH work_main_json AS (
    SELECT
        wm.user_id,
        wd.work_date,
        jsonb_agg(jsonb_build_object(
            'user_id', wm.user_id,
            'work_code', wm.work_code,
            'start_date', wm.start_date,
            'end_date', wm.end_date,
            'etc', wm.etc
        )) AS work_main_json
    FROM
        work_day wd
    LEFT JOIN work_main wm 
        ON wd.user_id = wm.user_id 
        AND wd.work_date BETWEEN wm.start_date AND wm.end_date
    GROUP BY
        wm.user_id, wd.work_date
),
work_sub_json AS (
    SELECT
        ws.user_id,
        wd.work_date,
        jsonb_agg(jsonb_build_object(
            'user_id', ws.user_id,
            'work_code', ws.work_code,
            'start_date', ws.start_date,
            'end_date', ws.end_date,
            'etc', ws.etc
        )) AS work_sub_json
    FROM
        work_day wd
    LEFT JOIN work_sub ws 
        ON wd.user_id = ws.user_id 
        AND wd.work_date BETWEEN ws.start_date AND ws.end_date
    GROUP BY
        ws.user_id
        , wd.work_date
)

-- メインクエリでLEFT JOINを使用してサブクエリと結合
SELECT
    wd.user_id,
    wd.work_date,
    wmj.work_main_json,
    wsj.work_sub_json
FROM
    work_day wd 
    LEFT JOIN work_main_json wmj 
        ON wd.user_id = wmj.user_id 
        AND wd.work_date = wmj.work_date 
    LEFT JOIN work_sub_json wsj 
        ON wd.user_id = wsj.user_id 
        AND wd.work_date = wsj.work_date 
ORDER BY
    wd.user_id
    , wd.work_date;

5.　取得結果

user_id	work_date	work_main_json	work_sub_json
1	2024/10/01	[{"etc": null, "user_id": 1, "end_date": "2024-10-03", "work_code": "A001", "start_date": "2024-10-01"}]	[{"etc": null, "user_id": 1, "end_date": "2024-10-03", "work_code": "X001", "start_date": "2024-10-01"}]
1	2024/10/02	[{"etc": null, "user_id": 1, "end_date": "2024-10-03", "work_code": "A001", "start_date": "2024-10-01"}, {"etc": null, "user_id": 1, "end_date": "2024-10-04", "work_code": "A001", "start_date": "2024-10-02"}]	[{"etc": null, "user_id": 1, "end_date": "2024-10-03", "work_code": "X001", "start_date": "2024-10-01"}, {"etc": null, "user_id": 1, "end_date": "2024-10-04", "work_code": "X001", "start_date": "2024-10-02"}]
1	2024/10/03	[{"etc": null, "user_id": 1, "end_date": "2024-10-03", "work_code": "A001", "start_date": "2024-10-01"}, {"etc": null, "user_id": 1, "end_date": "2024-10-04", "work_code": "A001", "start_date": "2024-10-02"}, {"etc": null, "user_id": 1, "end_date": "2024-10-05", "work_code": "A001", "start_date": "2024-10-03"}]	[{"etc": null, "user_id": 1, "end_date": "2024-10-03", "work_code": "X001", "start_date": "2024-10-01"}, {"etc": null, "user_id": 1, "end_date": "2024-10-04", "work_code": "X001", "start_date": "2024-10-02"}, {"etc": null, "user_id": 1, "end_date": "2024-10-05", "work_code": "X001", "start_date": "2024-10-03"}]
1	2024/10/04	[{"etc": null, "user_id": 1, "end_date": "2024-10-04", "work_code": "A001", "start_date": "2024-10-02"}, {"etc": null, "user_id": 1, "end_date": "2024-10-05", "work_code": "A001", "start_date": "2024-10-03"}]	[{"etc": null, "user_id": 1, "end_date": "2024-10-04", "work_code": "X001", "start_date": "2024-10-02"}, {"etc": null, "user_id": 1, "end_date": "2024-10-05", "work_code": "X001", "start_date": "2024-10-03"}]
1	2024/10/05	[{"etc": null, "user_id": 1, "end_date": "2024-10-05", "work_code": "A001", "start_date": "2024-10-03"}]	[{"etc": null, "user_id": 1, "end_date": "2024-10-05", "work_code": "X001", "start_date": "2024-10-03"}]
2	2024/10/01	[{"etc": null, "user_id": 2, "end_date": "2024-10-03", "work_code": "B001", "start_date": "2024-10-01"}]	[{"etc": null, "user_id": 2, "end_date": "2024-10-03", "work_code": "Y001", "start_date": "2024-10-01"}]
2	2024/10/02	[{"etc": null, "user_id": 2, "end_date": "2024-10-03", "work_code": "B001", "start_date": "2024-10-01"}, {"etc": null, "user_id": 2, "end_date": "2024-10-04", "work_code": "B001", "start_date": "2024-10-02"}]	[{"etc": null, "user_id": 2, "end_date": "2024-10-03", "work_code": "Y001", "start_date": "2024-10-01"}, {"etc": null, "user_id": 2, "end_date": "2024-10-04", "work_code": "Y001", "start_date": "2024-10-02"}]
2	2024/10/03	[{"etc": null, "user_id": 2, "end_date": "2024-10-03", "work_code": "B001", "start_date": "2024-10-01"}, {"etc": null, "user_id": 2, "end_date": "2024-10-04", "work_code": "B001", "start_date": "2024-10-02"}, {"etc": null, "user_id": 2, "end_date": "2024-10-05", "work_code": "B001", "start_date": "2024-10-03"}]	[{"etc": null, "user_id": 2, "end_date": "2024-10-03", "work_code": "Y001", "start_date": "2024-10-01"}, {"etc": null, "user_id": 2, "end_date": "2024-10-04", "work_code": "Y001", "start_date": "2024-10-02"}, {"etc": null, "user_id": 2, "end_date": "2024-10-05", "work_code": "Y001", "start_date": "2024-10-03"}]
2	2024/10/04	[{"etc": null, "user_id": 2, "end_date": "2024-10-04", "work_code": "B001", "start_date": "2024-10-02"}, {"etc": null, "user_id": 2, "end_date": "2024-10-05", "work_code": "B001", "start_date": "2024-10-03"}]	[{"etc": null, "user_id": 2, "end_date": "2024-10-04", "work_code": "Y001", "start_date": "2024-10-02"}, {"etc": null, "user_id": 2, "end_date": "2024-10-05", "work_code": "Y001", "start_date": "2024-10-03"}]
2	2024/10/05	[{"etc": null, "user_id": 2, "end_date": "2024-10-05", "work_code": "B001", "start_date": "2024-10-03"}]	[{"etc": null, "user_id": 2, "end_date": "2024-10-05", "work_code": "Y001", "start_date": "2024-10-03"}]
3	2024/10/01	[{"etc": null, "user_id": 3, "end_date": "2024-10-03", "work_code": "C001", "start_date": "2024-10-01"}]	[{"etc": null, "user_id": 3, "end_date": "2024-10-03", "work_code": "Z001", "start_date": "2024-10-01"}]
3	2024/10/02	[{"etc": null, "user_id": 3, "end_date": "2024-10-03", "work_code": "C001", "start_date": "2024-10-01"}, {"etc": null, "user_id": 3, "end_date": "2024-10-04", "work_code": "C001", "start_date": "2024-10-02"}]	[{"etc": null, "user_id": 3, "end_date": "2024-10-03", "work_code": "Z001", "start_date": "2024-10-01"}, {"etc": null, "user_id": 3, "end_date": "2024-10-04", "work_code": "Z001", "start_date": "2024-10-02"}]
3	2024/10/03	[{"etc": null, "user_id": 3, "end_date": "2024-10-03", "work_code": "C001", "start_date": "2024-10-01"}, {"etc": null, "user_id": 3, "end_date": "2024-10-04", "work_code": "C001", "start_date": "2024-10-02"}, {"etc": null, "user_id": 3, "end_date": "2024-10-05", "work_code": "C001", "start_date": "2024-10-03"}]	[{"etc": null, "user_id": 3, "end_date": "2024-10-03", "work_code": "Z001", "start_date": "2024-10-01"}, {"etc": null, "user_id": 3, "end_date": "2024-10-04", "work_code": "Z001", "start_date": "2024-10-02"}, {"etc": null, "user_id": 3, "end_date": "2024-10-05", "work_code": "Z001", "start_date": "2024-10-03"}]
3	2024/10/04	[{"etc": null, "user_id": 3, "end_date": "2024-10-04", "work_code": "C001", "start_date": "2024-10-02"}, {"etc": null, "user_id": 3, "end_date": "2024-10-05", "work_code": "C001", "start_date": "2024-10-03"}]	[{"etc": null, "user_id": 3, "end_date": "2024-10-04", "work_code": "Z001", "start_date": "2024-10-02"}, {"etc": null, "user_id": 3, "end_date": "2024-10-05", "work_code": "Z001", "start_date": "2024-10-03"}]
3	2024/10/05	[{"etc": null, "user_id": 3, "end_date": "2024-10-05", "work_code": "C001", "start_date": "2024-10-03"}]	[{"etc": null, "user_id": 3, "end_date": "2024-10-05", "work_code": "Z001", "start_date": "2024-10-03"}]

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up