我有下表:
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]]
}
]
简而言之,无论是最简单/最有效的。
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=2fc37233058437189b8e0a8eea05a01b