在posgres中对嵌套json数组中的成员的持续时间求和

Question

需要帮助

我需要通过postgres查询对嵌套jason中特定id的天数求和

——样本数据

[{
"id": 55,
"r_id": 2,
"friday": null,
"monday": {
"dia_id": "wsd",
"book_on": "12:00",
"turn on": 2,
"book_off": "5:00",
"duration": "5:00"},
"sunday": {
"dia_id": "asd",
"book_on": "12:00",
"turn on": 1,
"book_off": "1:00",
"duration": "2:00"},
"tuesday": null,
"saturday": null,
"thursday": null,
"wenseday": null,
"rownumber": 48,
"insert_time": null,
"update_time": null},
{
"id": 56,
"r_id": 2,
"friday": null,
"monday": {
"dia_id": "csd",
"book_on": "12:00",
"turn on": 4,
"book_off": "4:00",
"duration": "4:00"},
"sunday": {
"dia_id": "psd",
"book_on": "12:00",
"turn on": 3,
"book_off": "5:00",
"duration": "5:00"},
"tuesday": null,
"saturday": null,
"thursday": null,
"wenseday": null,
"rownumber": 49,
"insert_time": null,
"update_time": null}
]

输出格式应为

id duration
55  7:00
56  9:00

感谢

Answer 1

WITH t AS (
SELECT
jsonb_path_query(t.*, '$.id') AS id,
s::text::interval AS duration
FROM
jsonb_array_elements('[{
"id": 55,
"r_id": 2,
"friday": null,
"monday": {
"dia_id": "wsd",
"book_on": "12:00",
"turn on": 2,
"book_off": "5:00",
"duration": "5:00"},
"sunday": {
"dia_id": "asd",
"book_on": "12:00",
"turn on": 1,
"book_off": "1:00",
"duration": "2:00"},
"tuesday": null,
"saturday": null,
"thursday": null,
"wenseday": null,
"rownumber": 48,
"insert_time": null,
"update_time": null},
{
"id": 56,
"r_id": 2,
"friday": null,
"monday": {
"dia_id": "csd",
"book_on": "12:00",
"turn on": 4,
"book_off": "4:00",
"duration": "4:00"},
"sunday": {
"dia_id": "psd",
"book_on": "12:00",
"turn on": 3,
"book_off": "5:00",
"duration": "5:00"},
"tuesday": null,
"saturday": null,
"thursday": null,
"wenseday": null,
"rownumber": 49,
"insert_time": null,
"update_time": null}
]
'::jsonb) AS t,
LATERAL jsonb_path_query(t.*, '$.*.duration') AS s
)
SELECT
id,
sum(duration)
FROM
t
GROUP BY
id
ORDER BY
id;
id |   sum    
----+----------
55 | 07:00:00
56 | 09:00:00