How to interpret the MTTR raw data (json) in the database?

Question

Hello,

i want to do calculations on the MTTR (mean time to resolve) which cant be done by JQL so i searched the value in the DB and saw that it is stored as JSON (see below). Is there an explanation for this? Or are there examples on how to use it in an SQL?

{
   "timeline":{
      "events":[
         {
            "date":1508920800676,
            "types":[
               "START"
            ]
         },
         {
            "date":1508920959597,
            "types":[
               "PAUSE"
            ]
         }
      ]
   },
   "ongoingSLAData":{
      "goalId":86,
      "startTime":1508920800676,
      "paused":true,
      "thresholdData":{
         "calculatedAt":1508920959597,
         "remainingTime":25041079,
         "thresholdsConfigChangeDate":1507531971180,
         "thresholdsConfigChangeMsEpoch":1507531971180
      }
   },
   "completeSLAData":[

],
   "metricId":19,
   "definitionChangeDate":1507798977743,
   "definitionChangeMsEpoch":1507798977743,
   "goalsChangeDate":1508411143600,
   "goalsChangeMsEpoch":1508411143600,
   "goalTimeUpdatedDate":1507790885527,
   "goalTimeUpdatedMsEpoch":1507790885527,
   "metricCreatedDate":1507531971180,
   "updatedDate":1508920959862
}

jonas.bloeink · Answer

Hi vmishovski,

you can use this query and then in the column "Textvlaue" is the JSON string:

select Top 10 
jiraissue.issuenum, customfield.cfname, customfieldvalue.TEXTVALUE 
from jiraissue
inner join customfieldvalue on customfieldvalue.ISSUE = jiraissue.id
inner join customfield on customfield.id = customfieldvalue.CUSTOMFIELD
where cfname like 'MTTR'
;

vmishovski · Answer

Hello @Kilian would you share how did you extracted the above?