JSON and Jira

SQL objects

  • Staging table for raw JSON, assume one row per issue populated using (in part) /rest/api/2/search for updated issues
  • SQL views that use OPENJSON, JSON_QUERY, JSON_VALUE to shred JSON for Jira responses
jirajson.sql 
DROP TABLE IF EXISTS dbo.IssueHistory_STAGED
 
CREATE TABLE dbo.IssueHistory_STAGED (
  IssueId AS CAST(JSON_VALUE(RawJson,'$.id') AS INT)
, HistoryRecordId INT NOT NULL IDENTITY
, StagedDateTime DATETIME NOT NULL DEFAULT GETDATE()
, SourceModifiedDateTime AS CAST(LEFT(JSON_VALUE(RawJson,'$.fields.updated'),23) AS DATETIME) 
, RawJson NVARCHAR(MAX)
)
 
DROP VIEW IF EXISTS dbo.uvw_IssueHistory
DROP VIEW IF EXISTS dbo.uvw_Issue
DROP VIEW IF EXISTS dbo.uvw_IssueComponent
DROP VIEW IF EXISTS dbo.uvw_IssueComment
DROP VIEW IF EXISTS dbo.uvw_IssueLabel
 
GO
 
CREATE VIEW dbo.uvw_IssueHistory 
AS
 
WITH hx AS (
  SELECT
    IssueId
  , RawJson
  , StagedDateTime AS VersionValidFromDateTime
  , COALESCE(
      DATEADD(
        MILLISECOND
      , -3
      , LEAD(StagedDateTime) OVER (
          PARTITION BY IssueId
          ORDER BY 
            StagedDateTime
          , HistoryRecordId
        ) 
      )
    , '9000-12-31'
    ) AS VersionValidToDateTime
  FROM dbo.IssueHistory_STAGED
)
SELECT 
  *
FROM hx
WHERE VersionValidToDateTime >= VersionValidFromDateTime
 
GO
 
CREATE VIEW dbo.uvw_Issue
AS
 
SELECT 
  IssueId
, JSON_VALUE(RawJson,'$.key') AS IssueCode
, CAST(LEFT(JSON_VALUE(RawJson,'$.fields.created'),23) AS DATETIME) AS CreatedDateTime
, JSON_VALUE(RawJson,'$.fields.project.name') AS IssueProject
--, CAST(JSON_VALUE(RawJson,'$.fields.project.id') AS INT) AS IssueProjectId
, JSON_VALUE(RawJson,'$.fields.issuetype.name') AS IssueType
--, CAST(JSON_VALUE(RawJson,'$.fields.issuetype.id') AS INT) AS IssueTypeId
, JSON_VALUE(RawJson,'$.fields.summary') AS Summary
--, JSON_VALUE(RawJson,'$.fields.description') AS [Description]
, JSON_VALUE(RawJson,'$.fields.status.name') AS IssueStatus
--, CAST(JSON_VALUE(RawJson,'$.fields.status.id') AS INT) AS IssueStatusId
, RawJson
FROM dbo.uvw_IssueHistory
WHERE GETDATE() BETWEEN VersionValidFromDateTime AND VersionValidToDateTime
 
GO
 
CREATE VIEW dbo.uvw_IssueComponent
AS
 
SELECT 
  src.IssueId
, JSON_VALUE(shred.[value], '$.name') AS ComponentName
--, CAST(JSON_VALUE(shred.[value], '$.id') AS INT) AS ComponentId
FROM dbo.uvw_Issue src
  CROSS APPLY OPENJSON(JSON_QUERY(src.RawJson,'$.fields.components')) shred
 
GO
 
CREATE VIEW dbo.uvw_IssueComment
AS
 
SELECT 
  src.IssueId
, JSON_VALUE(shred.[value], '$.body') AS Comment
, CAST(LEFT(JSON_VALUE(shred.[value],'$.created'),23) AS DATETIME) AS CreatedDateTime
, JSON_VALUE(shred.[value], '$.author.name') AS Author
FROM dbo.uvw_Issue src
  CROSS APPLY OPENJSON(JSON_QUERY(src.RawJson,'$.fields.comment.comments')) shred
 
GO
 
CREATE VIEW dbo.uvw_IssueLabel
AS
 
SELECT 
  src.IssueId
, shred.[value] AS Label
FROM dbo.uvw_Issue src
  CROSS APPLY OPENJSON(JSON_QUERY(src.RawJson,'$.fields.labels')) shred
 
GO

Testing

  • Populate staging table with three copies of issue JSON
  • inspect views
test.sql 
DECLARE @json NVARCHAR(MAX) = '{  
   "expand":"renderedFields,names,schema,operations,editmeta,changelog,versionedRepresentations",
   "id":"10148",
   "self":"https://jira.atlassian.com/rest/api/latest/issue/10148",
   "key":"JRASERVER-9",
   "fields":{  
      "customfield_18232":null,
      "customfield_12130":null,
      "customfield_18230":null,
      "customfield_18231":null,
      "customfield_16732":null,
      "customfield_12131":null,
      "customfield_14430":null,
      "customfield_10230":null,
      "customfield_16730":null,
      "fixVersions":[  
         {  
            "self":"https://jira.atlassian.com/rest/api/2/version/15918",
            "id":"15918",
            "description":"",
            "name":"4.4",
            "archived":false,
            "released":true,
            "releaseDate":"2011-08-02"
         }
      ],
      "resolution":{  
         "self":"https://jira.atlassian.com/rest/api/2/resolution/1",
         "id":"1",
         "description":"A fix for this issue is checked into the tree and tested.",
         "name":"Fixed"
      },
      "customfield_12531":"Not Started",
      "customfield_14830":"<div class=\"aui-message\">\r\n    <p>Thanks for taking the time to raise a Suggestion for JIRA, Atlassian values your input.\r\n\t\t\t<br/>\r\n\t\t\t<br/>\r\n\t\t\tWe receive feedback from a number of different sources and evaluate those when we plan our product roadmap. If you would like to know more about how the JIRA Product Management team uses your input in our planning process, please see <a href = \"https://answers.atlassian.com/questions/110373/how-does-the-jira-team-use-jira-atlassian-com\">our post on Atlassian Answers</a>.\r\n\t\t\t<br/>\r\n\t\t\t<br/>\r\n\t\t\tBefore creating a new Suggestion, please search the existing issues to see if your suggestion already exists. Otherwise, please fill in the fields below with the use case for your suggestion.\r\n\t\t\t<br/>\r\n\t\t\t<br/>\r\n\t\t\tKind Regards,\r\n\t\t\t<br/>\r\n\t\t\tJIRA Product Management Team</p>\r\n<span class=\"aui-icon icon-info\"></span>\r\n</div>",
      "customfield_12930":null,
      "customfield_11436":"2809",
      "customfield_11435":"2814",
      "customfield_11437":"2622",
      "customfield_17130":null,
      "lastViewed":null,
      "customfield_10180":null,
      "customfield_17531":null,
      "customfield_17530":null,
      "customfield_17930":null,
      "customfield_11431":"36994",
      "customfield_11434":"2657",
      "customfield_17933":null,
      "labels":[  
         "affects-cloud",
         "affects-server"
      ],
      "customfield_11433":"36429",
      "customfield_10610":43.0,
      "aggregatetimeoriginalestimate":2160000,
      "timeestimate":2160000,
      "versions":[  
 
      ],
      "issuelinks":[  
         {  
            "id":"50308",
            "self":"https://jira.atlassian.com/rest/api/2/issueLink/50308",
            "type":{  
               "id":"10020",
               "name":"Blocker",
               "inward":"is blocked by",
               "outward":"blocks",
               "self":"https://jira.atlassian.com/rest/api/2/issueLinkType/10020"
            },
            "outwardIssue":{  
               "id":"126823",
               "key":"JSWSERVER-2855",
               "self":"https://jira.atlassian.com/rest/api/2/issue/126823",
               "fields":{  
                  "summary":"GreenHopper Hourly Burndown Gadget - Update Every 15 Minutes or as Scheduled",
                  "status":{  
                     "self":"https://jira.atlassian.com/rest/api/2/status/6",
                     "description":"The issue is considered finished, the resolution is correct. Issues which are not closed can be reopened.",
                     "iconUrl":"https://jira.atlassian.com/images/icons/statuses/closed.png",
                     "name":"Closed",
                     "id":"6",
                     "statusCategory":{  
                        "self":"https://jira.atlassian.com/rest/api/2/statuscategory/3",
                        "id":3,
                        "key":"done",
                        "colorName":"green",
                        "name":"Done"
                     }
                  },
                  "issuetype":{  
                     "self":"https://jira.atlassian.com/rest/api/2/issuetype/10000",
                     "id":"10000",
                     "description":"",
                     "iconUrl":"https://jira.atlassian.com/secure/viewavatar?size=xsmall&avatarId=51505&avatarType=issuetype",
                     "name":"Suggestion",
                     "subtask":false,
                     "avatarId":51505
                  }
               }
            }
         },
         {  
            "id":"37511",
            "self":"https://jira.atlassian.com/rest/api/2/issueLink/37511",
            "type":{  
               "id":"10050",
               "name":"Cause",
               "inward":"causes",
               "outward":"is caused by",
               "self":"https://jira.atlassian.com/rest/api/2/issueLinkType/10050"
            },
            "inwardIssue":{  
               "id":"85626",
               "key":"JRASERVER-17359",
               "self":"https://jira.atlassian.com/rest/api/2/issue/85626",
               "fields":{  
                  "summary":"Add timezone support to JQL dates.",
                  "status":{  
                     "self":"https://jira.atlassian.com/rest/api/2/status/6",
                     "description":"The issue is considered finished, the resolution is correct. Issues which are not closed can be reopened.",
                     "iconUrl":"https://jira.atlassian.com/images/icons/statuses/closed.png",
                     "name":"Closed",
                     "id":"6",
                     "statusCategory":{  
                        "self":"https://jira.atlassian.com/rest/api/2/statuscategory/3",
                        "id":3,
                        "key":"done",
                        "colorName":"green",
                        "name":"Done"
                     }
                  },
                  "issuetype":{  
                     "self":"https://jira.atlassian.com/rest/api/2/issuetype/10000",
                     "id":"10000",
                     "description":"",
                     "iconUrl":"https://jira.atlassian.com/secure/viewavatar?size=xsmall&avatarId=51505&avatarType=issuetype",
                     "name":"Suggestion",
                     "subtask":false,
                     "avatarId":51505
                  }
               }
            }
         }
      ],
      "assignee":null,
      "status":{  
         "self":"https://jira.atlassian.com/rest/api/2/status/6",
         "description":"The issue is considered finished, the resolution is correct. Issues which are not closed can be reopened.",
         "iconUrl":"https://jira.atlassian.com/images/icons/statuses/closed.png",
         "name":"Closed",
         "id":"6",
         "statusCategory":{  
            "self":"https://jira.atlassian.com/rest/api/2/statuscategory/3",
            "id":3,
            "key":"done",
            "colorName":"green",
            "name":"Done"
         }
      },
      "customfield_16030":null,
      "components":[  
         {  
            "self":"https://jira.atlassian.com/rest/api/2/component/10125",
            "id":"10125",
            "name":"User Management - Others"
         }
      ],
      "customfield_14130":null,
      "customfield_14532":null,
      "customfield_10571":null,
      "customfield_12630":null,
      "customfield_10575":null,
      "customfield_14930":"<div class=\"aui-message\">\r\n    <p>JIRA feedback is collected from a number of different sources and is evaluated when planning the product roadmap. If you would like to know more about how JIRA Product Management uses customer input during the planning process, please see <a href = \"https://answers.atlassian.com/questions/110373/how-does-the-jira-team-use-jira-atlassian-com\">our post on Atlassian Answers</a>.\r\n</p>\r\n<span class=\"aui-icon icon-info\"></span>\r\n</div>",
      "customfield_11930":null,
      "customfield_10723":null,
      "aggregatetimeestimate":2160000,
      "customfield_17230":null,
      "creator":{  
         "self":"https://jira.atlassian.com/rest/api/2/user?username=mike%40atlassian.com",
         "name":"mike@atlassian.com",
         "key":"mike@atlassian.com",
         "emailAddress":"mike at atlassian dot com",
         "avatarUrls":{  
            "48x48":"https://avatar-cdn.atlassian.com/b1a3871fb560dc486b6636467e9802fe?d=mm&s=48",
            "24x24":"https://avatar-cdn.atlassian.com/b1a3871fb560dc486b6636467e9802fe?d=mm&s=24",
            "16x16":"https://avatar-cdn.atlassian.com/b1a3871fb560dc486b6636467e9802fe?d=mm&s=16",
            "32x32":"https://avatar-cdn.atlassian.com/b1a3871fb560dc486b6636467e9802fe?d=mm&s=32"
         },
         "displayName":"Mike Cannon-Brookes",
         "active":true,
         "timeZone":"Australia/Sydney"
      },
      "customfield_17630":null,
      "customfield_15331":null,
      "subtasks":[  
 
      ],
      "customfield_10160":"26179200",
      "customfield_10161":"true",
      "customfield_13430":null,
      "customfield_11130":"4514",
      "customfield_10680":null,
      "reporter":{  
         "self":"https://jira.atlassian.com/rest/api/2/user?username=mike%40atlassian.com",
         "name":"mike@atlassian.com",
         "key":"mike@atlassian.com",
         "emailAddress":"mike at atlassian dot com",
         "avatarUrls":{  
            "48x48":"https://avatar-cdn.atlassian.com/b1a3871fb560dc486b6636467e9802fe?d=mm&s=48",
            "24x24":"https://avatar-cdn.atlassian.com/b1a3871fb560dc486b6636467e9802fe?d=mm&s=24",
            "16x16":"https://avatar-cdn.atlassian.com/b1a3871fb560dc486b6636467e9802fe?d=mm&s=16",
            "32x32":"https://avatar-cdn.atlassian.com/b1a3871fb560dc486b6636467e9802fe?d=mm&s=32"
         },
         "displayName":"Mike Cannon-Brookes",
         "active":true,
         "timeZone":"Australia/Sydney"
      },
      "customfield_10681":null,
      "customfield_17631":null,
      "aggregateprogress":{  
         "progress":1500,
         "total":2161500,
         "percent":0
      },
      "progress":{  
         "progress":1500,
         "total":2161500,
         "percent":0
      },
      "votes":{  
         "self":"https://jira.atlassian.com/rest/api/2/issue/JRASERVER-9/votes",
         "votes":454,
         "hasVoted":false
      },
      "worklog":{  
         "startAt":0,
         "maxResults":20,
         "total":1,
         "worklogs":[  
            {  
               "self":"https://jira.atlassian.com/rest/api/2/issue/10148/worklog/96804",
               "author":{  
                  "self":"https://jira.atlassian.com/rest/api/2/user?username=tim%40atlassian.com",
                  "name":"tim@atlassian.com",
                  "key":"tim@atlassian.com",
                  "emailAddress":"tim at atlassian dot com",
                  "avatarUrls":{  
                     "48x48":"https://avatar-cdn.atlassian.com/fd7b5dd1d45d737f66ba75b12c4cafcd?d=mm&s=48",
                     "24x24":"https://avatar-cdn.atlassian.com/fd7b5dd1d45d737f66ba75b12c4cafcd?d=mm&s=24",
                     "16x16":"https://avatar-cdn.atlassian.com/fd7b5dd1d45d737f66ba75b12c4cafcd?d=mm&s=16",
                     "32x32":"https://avatar-cdn.atlassian.com/fd7b5dd1d45d737f66ba75b12c4cafcd?d=mm&s=32"
                  },
                  "displayName":"Tim Pettersen",
                  "active":true,
                  "timeZone":"America/Los_Angeles"
               },
               "updateAuthor":{  
                  "self":"https://jira.atlassian.com/rest/api/2/user?username=tim%40atlassian.com",
                  "name":"tim@atlassian.com",
                  "key":"tim@atlassian.com",
                  "emailAddress":"tim at atlassian dot com",
                  "avatarUrls":{  
                     "48x48":"https://avatar-cdn.atlassian.com/fd7b5dd1d45d737f66ba75b12c4cafcd?d=mm&s=48",
                     "24x24":"https://avatar-cdn.atlassian.com/fd7b5dd1d45d737f66ba75b12c4cafcd?d=mm&s=24",
                     "16x16":"https://avatar-cdn.atlassian.com/fd7b5dd1d45d737f66ba75b12c4cafcd?d=mm&s=16",
                     "32x32":"https://avatar-cdn.atlassian.com/fd7b5dd1d45d737f66ba75b12c4cafcd?d=mm&s=32"
                  },
                  "displayName":"Tim Pettersen",
                  "active":true,
                  "timeZone":"America/Los_Angeles"
               },
               "comment":"Time submitted by matt for review CR-2",
               "created":"2011-05-20T05:55:22.952+0000",
               "updated":"2011-05-20T05:55:22.952+0000",
               "started":"2011-05-20T05:55:18.528+0000",
               "timeSpent":"25m",
               "timeSpentSeconds":1500,
               "id":"96804",
               "issueId":"10148"
            }
         ]
      },
      "customfield_18430":null,
      "issuetype":{  
         "self":"https://jira.atlassian.com/rest/api/2/issuetype/10000",
         "id":"10000",
         "description":"",
         "iconUrl":"https://jira.atlassian.com/secure/viewavatar?size=xsmall&avatarId=51505&avatarType=issuetype",
         "name":"Suggestion",
         "subtask":false,
         "avatarId":51505
      },
      "customfield_16530":null,
      "customfield_18030":null,
      "customfield_18031":null,
      "timespent":1500,
      "customfield_10150":[  
         "asaintprix(asaintprix)",
         "afahmi@icomm.co.id(afahmi@icomm.co.id)",
         "paf(paf)",
         "alvincura(alvincura)",
         "akhalilian(akhalilian)",
         "acarrucan@morpheum.com.au(acarrucan@morpheum.com.au)",
         "afeury(afeury)",
         "azhdanov(azhdanov)",
         "aphan(aphan)",
         "anton@atlassian.com(anton@atlassian.com)",
         "anton_nix(anton_nix)",
         "armenb(armenb)",
         "becket_hackett@playstation.sony.com(becket_hackett@playstation.sony.com)",
         "bcfletcher(bcfletcher)",
         "br.bill(br.bill)",
         "bobjohnston(bobjohnston)",
         "blane(blane)",
         "bryanfe(bryanfe)",
         "brollins(brollins)",
         "cambromley(cambromley)",
         "cedricdj(cedricdj)",
         "cburke(cburke)",
         "surfichris(surfichris)",
         "cbecker(cbecker)",
         "nowaker(nowaker)",
         "rporfirio(rporfirio)",
         "darbystewart(darbystewart)",
         "dbfraser(dbfraser)",
         "dtollefson(dtollefson)",
         "david.birch@dstintl.com(david.birch@dstintl.com)",
         "dallardyce(dallardyce)",
         "david_n_m_bond(david_n_m_bond)",
         "heavydawson(heavydawson)",
         "arsenale(arsenale)",
         "abunetta(abunetta)",
         "eirik(eirik)",
         "erik.d.gustavson(erik.d.gustavson)",
         "fherelle(fherelle)",
         "sanmi(sanmi)",
         "gchattan(gchattan)",
         "gbrauer1(gbrauer1)",
         "grant.holland@db.com(grant.holland@db.com)",
         "grant.mckenzie@morganstanley.com(grant.mckenzie@morganstanley.com)",
         "gregory.kneller(gregory.kneller)",
         "ian.wells@trimble.co.nz(ian.wells@trimble.co.nz)",
         "ivar.sonstabo@bouvet.no(ivar.sonstabo@bouvet.no)",
         "jkleist(jkleist)",
         "jan_carlin(jan_carlin)",
         "jbcatte(jbcatte)",
         "jed(jed)",
         "jcadow(jcadow)",
         "op99323(op99323)",
         "jschnitter(jschnitter)",
         "jelam(jelam)",
         "jgarrido(jgarrido)",
         "jheller(jheller)",
         "john.price@peopleclick.com(john.price@peopleclick.com)",
         "jsloat(jsloat)",
         "jordan@ooyala.com(jordan@ooyala.com)",
         "joshua maciel(joshua maciel)",
         "woodj(woodj)",
         "ksilz(ksilz)",
         "lagoni(lagoni)",
         "radicsl(radicsl)",
         "lmiranda(lmiranda)",
         "jyrinx(jyrinx)",
         "m27315(m27315)",
         "konqi(konqi)",
         "mmazza(mmazza)",
         "sfinks(sfinks)",
         "mmoskovitz@shoplocal.com(mmoskovitz@shoplocal.com)",
         "mdoar(mdoar)",
         "igx89(igx89)",
         "micah(micah)",
         "firewater(firewater)",
         "michaelst(michaelst)",
         "atlassian@quartalfs.com(atlassian@quartalfs.com)",
         "chellejohn(chellejohn)",
         "mike@atlassian.com(mike@atlassian.com)",
         "mike.murphy(mike.murphy)",
         "nkutub(nkutub)",
         "napplebaum(napplebaum)",
         "nick.bence@bencefamily.com(nick.bence@bencefamily.com)",
         "nick.menere(nick.menere)",
         "nikhilg(nikhilg)",
         "oleksii.gnatkevych(oleksii.gnatkevych)",
         "oliver.b.fischer(oliver.b.fischer)",
         "owen@atlassian.com(owen@atlassian.com)",
         "pkwok(pkwok)",
         "wolfertp(wolfertp)",
         "paulw@outblaze.com(paulw@outblaze.com)",
         "dakisan(dakisan)",
         "pdarrow(pdarrow)",
         "redawson(redawson)",
         "rasawyer(rasawyer)",
         "mromain(mromain)",
         "rdonasco(rdonasco)",
         "rkrishna(rkrishna)",
         "scottb@oberon-media.com(scottb@oberon-media.com)",
         "sebbapache(sebbapache)",
         "lizenko(lizenko)",
         "shinta(shinta)",
         "rmsrini(rmsrini)",
         "stafford@customware.net(stafford@customware.net)",
         "stephan.born(stephan.born)",
         "stuartd(stuartd)",
         "dehli(dehli)",
         "timbos(timbos)",
         "wjohnston(wjohnston)",
         "voitek(voitek)",
         "ykhatri(ykhatri)",
         "zcraven(zcraven)"
      ],
      "customfield_16930":null,
      "project":{  
         "self":"https://jira.atlassian.com/rest/api/2/project/10240",
         "id":"10240",
         "key":"JRASERVER",
         "name":"JIRA Server (including JIRA Core)",
         "avatarUrls":{  
            "48x48":"https://jira.atlassian.com/secure/projectavatar?pid=10240&avatarId=105190",
            "24x24":"https://jira.atlassian.com/secure/projectavatar?size=small&pid=10240&avatarId=105190",
            "16x16":"https://jira.atlassian.com/secure/projectavatar?size=xsmall&pid=10240&avatarId=105190",
            "32x32":"https://jira.atlassian.com/secure/projectavatar?size=medium&pid=10240&avatarId=105190"
         },
         "projectCategory":{  
            "self":"https://jira.atlassian.com/rest/api/2/projectCategory/10031",
            "id":"10031",
            "description":"",
            "name":"Atlassian Products"
         }
      },
      "customfield_16532":null,
      "customfield_16531":null,
      "customfield_12730":null,
      "aggregatetimespent":1500,
      "customfield_16933":null,
      "customfield_16932":null,
      "customfield_16535":null,
      "resolutiondate":"2011-06-07T15:31:26.782+0000",
      "workratio":0,
      "watches":{  
         "self":"https://jira.atlassian.com/rest/api/2/issue/JRASERVER-9/watchers",
         "watchCount":213,
         "isWatching":false
      },
      "customfield_17333":null,
      "customfield_15430":null,
      "created":"2002-02-08T05:08:00.000+0000",
      "customfield_15830":null,
      "customfield_17335":null,
      "customfield_17334":null,
      "customfield_11631":null,
      "customfield_12833":null,
      "customfield_12832":null,
      "customfield_14735":"0|100hc0:",
      "updated":"2017-03-29T04:49:41.515+0000",
      "customfield_14330":null,
      "customfield_18130":null,
      "timeoriginalestimate":2160000,
      "customfield_10250":"<div class=\"aui-message aui-message-info\">\r\n<b>Help us fix bugs faster</b>\r\n<br>\r\n<br>To help us get the right details and fix bugs as quickly as possible, submit your bug report in our <a href=\"http://support.atlassian.com/\" class=\"bugfix-support\">support system</a>.\r\n<br>\r\n<br>You can still view, watch, comment on, and vote for existing bugs by\r\n  \r\n<a href=\"https://jira.atlassian.com/issues/?jql=issuetype%20%3D%20Bug\" class=\"bugfix-search\">searching</a> for them in this project.\r\n<br><br>\r\n<a class=\"aui-button aui-button-primary bugfix-support\" href=\"https://support.atlassian.com/jiracore/\">Report a new bug</a> <a class=\"aui-button bugfix-search\" href=\"https://jira.atlassian.com/issues/?jql=issuetype=Bug+AND+project=JRA\">Search for an existing bug</a>\r\n<br>\r\n</div>",
      "description":"Add time zones to user profile.     That way the dates displayed to a user are always contiguous with their local time zone, rather than the server''s time zone.",
      "customfield_16632":null,
      "customfield_12431":null,
      "customfield_12430":null,
      "customfield_16630":null,
      "customfield_14733":"1|hyba9z:",
      "customfield_10650":null,
      "customfield_12433":null,
      "customfield_10651":null,
      "customfield_14734":"0|100g6g:",
      "customfield_12432":null,
      "customfield_12435":null,
      "customfield_12831":null,
      "customfield_14731":"0|i09onz:",
      "timetracking":{  
         "originalEstimate":"600h",
         "remainingEstimate":"600h",
         "timeSpent":"25m",
         "originalEstimateSeconds":2160000,
         "remainingEstimateSeconds":2160000,
         "timeSpentSeconds":1500
      },
      "customfield_14732":"0|1068ls:",
      "customfield_12830":null,
      "customfield_12434":null,
      "customfield_10653":null,
      "customfield_10401":null,
      "attachment":[  
         {  
            "self":"https://jira.atlassian.com/rest/api/2/attachment/45565",
            "id":"45565",
            "filename":"log_110314_115720______.csv",
            "author":{  
               "self":"https://jira.atlassian.com/rest/api/2/user?username=jennis",
               "name":"jennis",
               "key":"jennis",
               "emailAddress":"jennis at infoblox dot com",
               "avatarUrls":{  
                  "48x48":"https://avatar-cdn.atlassian.com/508bfb45b6fbd9f3930a394f8e3b979b?d=mm&s=48",
                  "24x24":"https://avatar-cdn.atlassian.com/508bfb45b6fbd9f3930a394f8e3b979b?d=mm&s=24",
                  "16x16":"https://avatar-cdn.atlassian.com/508bfb45b6fbd9f3930a394f8e3b979b?d=mm&s=16",
                  "32x32":"https://avatar-cdn.atlassian.com/508bfb45b6fbd9f3930a394f8e3b979b?d=mm&s=32"
               },
               "displayName":"Jay Ennis",
               "active":true,
               "timeZone":"Etc/UTC"
            },
            "created":"2011-03-26T15:38:11.072+0000",
            "size":24576,
            "mimeType":"text/csv",
            "content":"https://jira.atlassian.com/secure/attachment/45565/log_110314_115720______.csv"
         },
         {  
            "self":"https://jira.atlassian.com/rest/api/2/attachment/40875",
            "id":"40875",
            "filename":"times.png",
            "author":{  
               "self":"https://jira.atlassian.com/rest/api/2/user?username=rkrishna",
               "name":"rkrishna",
               "key":"rkrishna",
               "emailAddress":"rkrishna at atlassian dot com",
               "avatarUrls":{  
                  "48x48":"https://jira.atlassian.com/secure/useravatar?ownerId=rkrishna&avatarId=66525",
                  "24x24":"https://jira.atlassian.com/secure/useravatar?size=small&ownerId=rkrishna&avatarId=66525",
                  "16x16":"https://jira.atlassian.com/secure/useravatar?size=xsmall&ownerId=rkrishna&avatarId=66525",
                  "32x32":"https://jira.atlassian.com/secure/useravatar?size=medium&ownerId=rkrishna&avatarId=66525"
               },
               "displayName":"Roy Krishna",
               "active":true,
               "timeZone":"Australia/Sydney"
            },
            "created":"2010-09-21T05:31:32.198+0000",
            "size":39226,
            "mimeType":"image/png",
            "content":"https://jira.atlassian.com/secure/attachment/40875/times.png",
            "thumbnail":"https://jira.atlassian.com/secure/thumbnail/40875/_thumb_40875.png"
         }
      ],
      "summary":"User Preference: User Time Zones",
      "customfield_17432":null,
      "customfield_17431":null,
      "customfield_13230":"2003-11-17 01:55:10.76",
      "customfield_15532":null,
      "customfield_13231":"1_*:*_2_*:*_244284876561_*|*_5_*:*_1_*:*_0_*|*_10032_*:*_3_*:*_27387070956_*|*_10031_*:*_2_*:*_22643859294",
      "customfield_17831":null,
      "customfield_15530":null,
      "customfield_17830":null,
      "customfield_17433":null,
      "customfield_15932":null,
      "customfield_15930":null,
      "customfield_11730":null,
      "customfield_15931":null,
      "customfield_12931":null,
      "environment":null,
      "duedate":null,
      "comment":{  
         "comments":[  
            {  
               "self":"https://jira.atlassian.com/rest/api/2/issue/10148/comment/17800",
               "id":"17800",
               "author":{  
                  "self":"https://jira.atlassian.com/rest/api/2/user?username=owen%40atlassian.com",
                  "name":"owen@atlassian.com",
                  "key":"owen@atlassian.com",
                  "emailAddress":"owen at atlassian dot com",
                  "avatarUrls":{  
                     "48x48":"https://avatar-cdn.atlassian.com/5aa96f52989b58639779e66325f190a0?d=mm&s=48",
                     "24x24":"https://avatar-cdn.atlassian.com/5aa96f52989b58639779e66325f190a0?d=mm&s=24",
                     "16x16":"https://avatar-cdn.atlassian.com/5aa96f52989b58639779e66325f190a0?d=mm&s=16",
                     "32x32":"https://avatar-cdn.atlassian.com/5aa96f52989b58639779e66325f190a0?d=mm&s=32"
                  },
                  "displayName":"Owen Fellows",
                  "active":true,
                  "timeZone":"Etc/UTC"
               },
               "body":"This is due to users not having a timezone set, see linked Issue.",
               "updateAuthor":{  
                  "self":"https://jira.atlassian.com/rest/api/2/user?username=owen%40atlassian.com",
                  "name":"owen@atlassian.com",
                  "key":"owen@atlassian.com",
                  "emailAddress":"owen at atlassian dot com",
                  "avatarUrls":{  
                     "48x48":"https://avatar-cdn.atlassian.com/5aa96f52989b58639779e66325f190a0?d=mm&s=48",
                     "24x24":"https://avatar-cdn.atlassian.com/5aa96f52989b58639779e66325f190a0?d=mm&s=24",
                     "16x16":"https://avatar-cdn.atlassian.com/5aa96f52989b58639779e66325f190a0?d=mm&s=16",
                     "32x32":"https://avatar-cdn.atlassian.com/5aa96f52989b58639779e66325f190a0?d=mm&s=32"
                  },
                  "displayName":"Owen Fellows",
                  "active":true,
                  "timeZone":"Etc/UTC"
               },
               "created":"2003-11-17T01:55:10.760+0000",
               "updated":"2003-11-17T01:55:10.760+0000"
            },
            {  
               "self":"https://jira.atlassian.com/rest/api/2/issue/10148/comment/19723",
               "id":"19723",
               "author":{  
                  "self":"https://jira.atlassian.com/rest/api/2/user?username=firewater",
                  "name":"firewater",
                  "key":"firewater",
                  "emailAddress":"michael dot phillimore-brown at rbs dot com",
                  "avatarUrls":{  
                     "48x48":"https://avatar-cdn.atlassian.com/541855c634ee3b570bc13deb97d5923b?d=mm&s=48",
                     "24x24":"https://avatar-cdn.atlassian.com/541855c634ee3b570bc13deb97d5923b?d=mm&s=24",
                     "16x16":"https://avatar-cdn.atlassian.com/541855c634ee3b570bc13deb97d5923b?d=mm&s=16",
                     "32x32":"https://avatar-cdn.atlassian.com/541855c634ee3b570bc13deb97d5923b?d=mm&s=32"
                  },
                  "displayName":"Michael Phillimore-Brown",
                  "active":true,
                  "timeZone":"Europe/London"
               },
               "body":"You could presumably identify the user''s locale from the request too...",
               "updateAuthor":{  
                  "self":"https://jira.atlassian.com/rest/api/2/user?username=firewater",
                  "name":"firewater",
                  "key":"firewater",
                  "emailAddress":"michael dot phillimore-brown at rbs dot com",
                  "avatarUrls":{  
                     "48x48":"https://avatar-cdn.atlassian.com/541855c634ee3b570bc13deb97d5923b?d=mm&s=48",
                     "24x24":"https://avatar-cdn.atlassian.com/541855c634ee3b570bc13deb97d5923b?d=mm&s=24",
                     "16x16":"https://avatar-cdn.atlassian.com/541855c634ee3b570bc13deb97d5923b?d=mm&s=16",
                     "32x32":"https://avatar-cdn.atlassian.com/541855c634ee3b570bc13deb97d5923b?d=mm&s=32"
                  },
                  "displayName":"Michael Phillimore-Brown",
                  "active":true,
                  "timeZone":"Europe/London"
               },
               "created":"2004-02-20T09:23:01.680+0000",
               "updated":"2004-02-20T09:23:01.680+0000"
            },
            {  
               "self":"https://jira.atlassian.com/rest/api/2/issue/10148/comment/28861",
               "id":"28861",
               "author":{  
                  "self":"https://jira.atlassian.com/rest/api/2/user?username=grant.mckenzie%40morganstanley.com",
                  "name":"grant.mckenzie@morganstanley.com",
                  "key":"grant.mckenzie@morganstanley.com",
                  "emailAddress":"grant dot mckenzie at morganstanley dot com",
                  "avatarUrls":{  
                     "48x48":"https://avatar-cdn.atlassian.com/aef7a23c6701e190b93a6fa14f972f85?d=mm&s=48",
                     "24x24":"https://avatar-cdn.atlassian.com/aef7a23c6701e190b93a6fa14f972f85?d=mm&s=24",
                     "16x16":"https://avatar-cdn.atlassian.com/aef7a23c6701e190b93a6fa14f972f85?d=mm&s=16",
                     "32x32":"https://avatar-cdn.atlassian.com/aef7a23c6701e190b93a6fa14f972f85?d=mm&s=32"
                  },
                  "displayName":"Grant McKenzie",
                  "active":true,
                  "timeZone":"Etc/UTC"
               },
               "body":"I''d like to see this feature aswell. In a centralized server that is shared internationally, people want to see local times not EST or GMT.\n\nThis cannot be inferred from the locale in my opinion. I might live in the US and have a timezone of EST but want to display the German language interface.\n",
               "updateAuthor":{  
                  "self":"https://jira.atlassian.com/rest/api/2/user?username=grant.mckenzie%40morganstanley.com",
                  "name":"grant.mckenzie@morganstanley.com",
                  "key":"grant.mckenzie@morganstanley.com",
                  "emailAddress":"grant dot mckenzie at morganstanley dot com",
                  "avatarUrls":{  
                     "48x48":"https://avatar-cdn.atlassian.com/aef7a23c6701e190b93a6fa14f972f85?d=mm&s=48",
                     "24x24":"https://avatar-cdn.atlassian.com/aef7a23c6701e190b93a6fa14f972f85?d=mm&s=24",
                     "16x16":"https://avatar-cdn.atlassian.com/aef7a23c6701e190b93a6fa14f972f85?d=mm&s=16",
                     "32x32":"https://avatar-cdn.atlassian.com/aef7a23c6701e190b93a6fa14f972f85?d=mm&s=32"
                  },
                  "displayName":"Grant McKenzie",
                  "active":true,
                  "timeZone":"Etc/UTC"
               },
               "created":"2004-12-21T22:40:09.316+0000",
               "updated":"2004-12-21T22:40:09.316+0000"
            }
         ],
         "maxResults":141,
         "total":141,
         "startAt":0
      }
   }
}'
 
DECLARE @i INT = 0
 
WHILE @i < 3
BEGIN
 
  INSERT INTO dbo.IssueHistory_STAGED (
    RawJson
  ) VALUES (
    @json
  )
 
  SET @i += 1
 
  WAITFOR DELAY '00:00:01'
 
END
 
SELECT * FROM dbo.uvw_IssueHistory
SELECT * FROM dbo.uvw_Issue
SELECT * FROM dbo.uvw_IssueComponent
SELECT * FROM dbo.uvw_IssueComment
SELECT * FROM dbo.uvw_IssueLabel