將 Postgres 中的可變行數加入 json 數組

我有下表：

timestamp | type | value | source
----------+------+-------+--------
2020-01-01| 1    | 10    | 2
2020-01-01| 2    | 20    | 2
2020-01-02| 1    | 5     | 4
2020-01-02| 2    | 6     | 3
2020-01-02| 5    | 4     | 3
2020-01-02| 4    | 8     | 1

每個timestamp+type對都是唯一的。對於 each timestamp，可以有可變數量的行（但每個都有不同的type）。value並且source是實際數據。

現在我想將 by 聚合timestamp到一個 json 數組中，其中對於每個時間戳，都包含可用的 ( type, value, source) 元組。理想情況下，類似：

[
 {
   "2020-01-01": [{"type": 1, "value": 10, "source": 2}, {"type": 2, "value": 20, "source": 2}]
 },
 {
   "2020-01-02": [{"type": 1, "value": 5, "source": 4}, {"type": 2, "value": 6, "source": 3}, {"type": 5, "value": 4, "source": 3}, {"type": 4, "value": 8, "source": 1}]
 }
]

只要資訊存在並正確分組，我對輸出格式並沒有真正的偏好，例如，這也可以：

[
 {
   "timestamp": "2020-01-01", "data": [{"type": 1, "value": 10, "source": 2}, {"type": 2, "value": 20, "source": 2}]
 },
 {
   "timestamp": "2020-01-02", "data": [{"type": 1, "value": 5, "source": 4}, {"type": 2, "value": 6, "source": 3}, {"type": 5, "value": 4, "source": 3}, {"type": 4, "value": 8, "source": 1}]
 }
]

或者這個（我猜這需要轉換type為字元串，這不是問題）：

[
 {
   "timestamp": "2020-01-01", "1": {"value": 10, "source": 2}, "2": {"value": 20, "source": 2}
 },
 {
   "timestamp": "2020-01-02", "1": {"value": 5, "source": 4}, "2": {"value": 6, "source": 3}, "5": {"value": 4, "source": 3}, "4": {"value": 8, "source": 1}
 }
]

即使這樣，只要事先知道欄位排序：

[
 {
   "2020-01-01": [[1, 10, 2], [2, 20, 2]]
 },
 {
   "2020-01-02": [[1, 5, 4], [2, 6, 3], [5, 4, 3], [4, 8, 1]]
 }
]

簡而言之，無論是最簡單/最有效的。

WITH cte AS (
   SELECT "timestamp", json_agg(json_build_object('type', "type", 
                                                  'value', "value",
                                                  'source', "source")) intermediate_json
   FROM test
   GROUP BY 1
)
SELECT json_agg(json_build_object("timestamp", intermediate_json)) final_json
FROM cte

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=2fc37233058437189b8e0a8eea05a01b

引用自：https://dba.stackexchange.com/questions/311160