Sum the duration of an member in nested json array in posgres

Question

need an help

I need to sum the duration of the days for an particular id in an nested jason through postgres query

--Sample data

[{
    "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}
]

The output should be in this format

id duration
55  7:00
56  9:00

Thanks

Not sure how `"book_on": "12:00"` and `"book_off": "1:00"` yields ` "duration": "2:00"`? — Adrian Klaver, Jan 06 '23 at 20:51
Sorry Adrian might be the off time is 2:00 the above json is duration cal any ways to call whole duration by group by id — nikhil raj, Jan 06 '23 at 21:22
I would be skeptical of any data where the parts don't agree. — Adrian Klaver, Jan 06 '23 at 23:17

score 1 · Accepted Answer · answered Jan 07 '23 at 01:30

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

Sum the duration of an member in nested json array in posgres

1 Answers1