0

I am pretty inexperience working with json in Postgres and it is making getting the data I need out very difficult. Using the query below I can see that callflow gives me the times for each section of the call. Can I get times where hangup_time is grater than 0? Ideally I just need time in seconds between created_time and hangup_time for each row. 

select
    xml_cdr_uuid,
    json->'callflow'
from fusionpbx.public.v_xml_cdr
where start_stamp > now() - '1 day'::interval;

which gives me arrays of times like this for each row.

[
    {
        "times":
        {
            "hangup_time": "1588604603083829",
            "bridged_time": "1588604592603811",
            "created_time": "1588604575783849",
            "answered_time": "1588604575803817",
            "progress_time": "1588604575803817",
            "transfer_time": "0",
            "last_hold_time": "0",
            "resurrect_time": "0",
            "hold_accum_time": "0",
            "progress_media_time": "0",
            "profile_created_time": "1588604582343873"
        },
        "extension": {...},
        "@attributes": {...},
        "caller_profile": {...},
    },
    {
        "times":
        {
            "hangup_time": "0",
            "bridged_time": "0",
            "created_time": "1588604575783849",
            "answered_time": "1588604575803817",
            "progress_time": "1588604575803817",
            "transfer_time": "1588604582343873",
            "last_hold_time": "0",
            "resurrect_time": "0",
            "hold_accum_time": "0",
            "progress_media_time": "0",
            "profile_created_time": "1588604575783849"
        },
        "extension": {...},
        "@attributes": {...},
        "caller_profile": {...},
    },
    {
        "times":
        {
            "hangup_time": "0",
            "bridged_time": "0",
            "created_time": "1588604575783849",
            "answered_time": "0",
            "progress_time": "0",
            "transfer_time": "1588604575783849",
            "last_hold_time": "0",
            "resurrect_time": "0",
            "hold_accum_time": "0",
            "progress_media_time": "0",
            "profile_created_time": "1588604575783849"
        },
        "extension": {...},
        "@attributes": {...},
        "caller_profile": {...},
    }
]
Chris
  • 811
  • 8
  • 17
  • So you want the returned JSON array reduced to array elements where the key `hangup_time` is greater than zero? Or do you only want to have rows that contain at least one array element where that value is greater than zero? Or do you want rows where all array elements have a value greater than zero –  May 06 '20 at 14:34
  • 1
    @a_horse_with_no_name, the end result that I am going for is to have a value `post_transfer_time` which is a seconds difference between `created_time` and `hangup_time`. Thanks! – Chris May 06 '20 at 16:11
  • 1
    you will need to extract this information from 'callflow' using `json_array_elements` and `->>` – jaksco May 06 '20 at 16:19

0 Answers0