PostgreSQLのLAGとjson_build_objectを組み合わせた動き

概要

PostgreSQLでLAGとjson_build_objectを組み合わせて動かす機会があったため、備忘として残しておく

LAGとjson_build_objectの説明

以下の記事が分かりやすかった

LAGの説明

今更ながらPostgreSQLで「現在の行の値」と「現在の行から前にある行の値」を簡単に比較したかった～window関数便利～

json_build_objectの説明

【初心者向け】PostgreSQLでJSONデータ型を作成する

組み合わせるとどうなるか？

まず、テーブルを作る

create table test_table001 (
	id INTEGER,
	value_bigint BIGINT,
	value_double DOUBLE PRECISION,
	value_date DATE,
	value_timestamp TIMESTAMP,
	value_string VARCHAR
);

ランダムなデータを入れておく

INSERT INTO test_table001 (id, value_bigint, value_double, value_date, value_timestamp, value_string) VALUES
	(1, 9223372036854775807, 123.45, '2025-02-13', '2025-02-13 12:34:56', 'Alice'),
	(2, -9223372036854775808, -123.45, '2025-12-31', '2025-12-31 23:59:59', 'Bob'),
	(3, 1234567890123456789, 0.0, '1970-01-01', '1970-01-01 00:00:00', 'Charlie'),
	(4, 8876543210987654321, 999.99, '9999-12-31', '9999-12-31 23:59:59', 'David'),
	(5, null, null, null, null, null),
	(6, 1234567890, 456.78, '2023-01-01', '2023-01-01 12:00:00', 'Eve'),
	(7, 9876543210, 789.01, '2023-02-01', '2023-02-01 13:00:00', 'Frank');

データは以下が格納されている

 id |     value_bigint     | value_double | value_date |   value_timestamp   | value_string
----+----------------------+--------------+------------+---------------------+--------------
  1 |  9223372036854775807 |       123.45 | 2025-02-13 | 2025-02-13 12:34:56 | Alice
  2 | -9223372036854775808 |      -123.45 | 2025-12-31 | 2025-12-31 23:59:59 | Bob
  3 |  1234567890123456789 |            0 | 1970-01-01 | 1970-01-01 00:00:00 | Charlie
  4 |  8876543210987654321 |       999.99 | 9999-12-31 | 9999-12-31 23:59:59 | David
  5 |                      |              |            |                     |
  6 |           1234567890 |       456.78 | 2023-01-01 | 2023-01-01 12:00:00 | Eve
  7 |           9876543210 |       789.01 | 2023-02-01 | 2023-02-01 13:00:00 | Frank
(7 rows)

このデータに対しLAGとjson_build_objectを組み合わせた動きを見てみる。
以下の取り出し方をしてどのようなデータが取り出されるか確認する。

以下のクエリで動きを見てみる。

WITH dummy AS (
	SELECT id, value_bigint, value_double, value_date, value_timestamp, value_string FROM test_table001
	)
SELECT
	id,
	value_bigint,
	value_double,
	value_date,
	value_timestamp,
	value_string,
	LAG(value_bigint) OVER (ORDER BY id) AS lagged_value_bigint,
	LAG(json_build_object('value_bigint', value_bigint, 'value_double', value_double, 'value_date', value_date), 1) OVER (ORDER BY id) AS lagged_json_value
FROM dummy;

LAG(value_bigint) OVER (ORDER BY id)
→id列で並べ替えた順序でvalue_bigint列の前の行の値を取得する

LAG(json_build_object('value_bigint', value_bigint, 'value_double', value_double, 'value_date', value_date), 1) OVER (ORDER BY id)
→LAGとjson_build_objectを組み合わせたデータの取り出し
json_build_objectにkeyとvalueの組み合わせで偶数倍のデータをインプットするときに、json_build_objectをLAG関数で囲うとどうなるか？
１つのデータだけがLAGされるのか、json_build_object内において列名で指定しているデータに対して全てLAGが適用されるのか？

結果

 id |     value_bigint     | value_double | value_date |   value_timestamp   | value_string | lagged_value_bigint  |                                       lagged_json_value
----+----------------------+--------------+------------+---------------------+--------------+----------------------+------------------------------------------------------------------------------------------------
  1 |  9223372036854775807 |       123.45 | 2025-02-13 | 2025-02-13 12:34:56 | Alice        |                      |
  2 | -9223372036854775808 |      -123.45 | 2025-12-31 | 2025-12-31 23:59:59 | Bob          |  9223372036854775807 | {"value_bigint" : 9223372036854775807, "value_double" : 123.45, "value_date" : "2025-02-13"}
  3 |  1234567890123456789 |            0 | 1970-01-01 | 1970-01-01 00:00:00 | Charlie      | -9223372036854775808 | {"value_bigint" : -9223372036854775808, "value_double" : -123.45, "value_date" : "2025-12-31"}
  4 |  8876543210987654321 |       999.99 | 9999-12-31 | 9999-12-31 23:59:59 | David        |  1234567890123456789 | {"value_bigint" : 1234567890123456789, "value_double" : 0, "value_date" : "1970-01-01"}
  5 |                      |              |            |                     |              |  8876543210987654321 | {"value_bigint" : 8876543210987654321, "value_double" : 999.99, "value_date" : "9999-12-31"}
  6 |           1234567890 |       456.78 | 2023-01-01 | 2023-01-01 12:00:00 | Eve          |                      | {"value_bigint" : null, "value_double" : null, "value_date" : null}
  7 |           9876543210 |       789.01 | 2023-02-01 | 2023-02-01 13:00:00 | Frank        |           1234567890 | {"value_bigint" : 1234567890, "value_double
" : 456.78, "value_date" : "2023-01-01"}

json_build_object内において列名で指定しているデータに対して全てLAGが適用された！