0

I am not able to parse the following complex json output from JIRA in to a dataframe using R. I am able to partially parse the data into dataframe using PowerShell. But wish to accomplish this in R. Can someone please suggest how to proceed? The solutions listed in this forum using Rjson or jsonlite or RJSONIO did not work with this example.

  [
        {
            "expand": [
                "operations,versionedRepresentations,editmeta,changelog,renderedFields"
            ],
            "id": [
                "22513"
            ],
            "self": [
                "https://somecompany.atlassian.net/rest/api/latest/issue/22513"
            ],
            "key": [
                "DATA-1314"
            ],
            "fields": {
                "issuetype": {
                    "self": [
                        "https://somecompany.atlassian.net/rest/api/2/issuetype/10001"
                    ],
                    "id": [
                        "10001"
                    ],
                    "description": [
                        "A task that needs to be done."
                    ],
                    "iconUrl": [
                        "https://somecompany.atlassian.net/secure/viewavatar?size=xsmall&avatarId=10318&avatarType=issuetype"
                    ],
                    "name": [
                        "Task"
                    ],
                    "subtask": [
                        false
                    ],
                    "avatarId": [
                        10318
                    ]
                },
                "timespent": {},
                "project": {
                    "self": [
                        "https://somecompany.atlassian.net/rest/api/2/project/10300"
                    ],
                    "id": [
                        "10300"
                    ],
                    "key": [
                        "DATA"
                    ],
                    "name": [
                        "Data"
                    ],
                    "avatarUrls": {
                        "48x48": [
                            "https://somecompany.atlassian.net/secure/projectavatar?avatarId=10324"
                        ],
                        "24x24": [
                            "https://somecompany.atlassian.net/secure/projectavatar?size=small&avatarId=10324"
                        ],
                        "16x16": [
                            "https://somecompany.atlassian.net/secure/projectavatar?size=xsmall&avatarId=10324"
                        ],
                        "32x32": [
                            "https://somecompany.atlassian.net/secure/projectavatar?size=medium&avatarId=10324"
                        ]
                    }
                },
                "fixVersions": [],
                "aggregatetimespent": {},
                "resolution": {},
                "customfield_10500": {},
                "customfield_10700": {},
                "resolutiondate": {},
                "workratio": [
                    -1
                ],
                "lastViewed": [
                    "2017-07-14T15:33:40.422-0700"
                ],
                "watches": {
                    "self": [
                        "https://somecompany.atlassian.net/rest/api/2/issue/DATA-1314/watchers"
                    ],
                    "watchCount": [
                        1
                    ],
                    "isWatching": [
                        true
                    ]
                },
                "created": [
                    "2017-07-12T11:56:12.000-0700"
                ],
                "customfield_10022": {},
                "customfield_10023": [
                    "1|i001zz:"
                ],
                "priority": {
                    "self": [
                        "https://somecompany.atlassian.net/rest/api/2/priority/3"
                    ],
                    "iconUrl": [
                        "https://somecompany.atlassian.net/images/icons/priorities/medium.svg"
                    ],
                    "name": [
                        "Medium"
                    ],
                    "id": [
                        "3"
                    ]
                },
                "customfield_10300": {},
                "labels": [
                    [
                        "data"
                    ]
                ],
                "customfield_10017": {},
                "customfield_10018": {},
                "timeestimate": {},
                "aggregatetimeoriginalestimate": {},
                "versions": [],
                "issuelinks": [],
                "assignee": {
                    "self": [
                        "https://somecompany.atlassian.net/rest/api/2/user?username=UserName"
                    ],
                    "name": [
                        "UserName"
                    ],
                    "key": [
                        "UserName"
                    ],
                    "accountId": [
                        "1234567890"
                    ],
                    "emailAddress": [
                        "[email protected]"
                    ],
                    "avatarUrls": {
                        "48x48": [
                            "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=48&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%12345678901215406a6830d189deda8cc%3Fd%3Dmm%26s%3D48%26noRedirect%3Dtrue"
                        ],
                        "24x24": [
                            "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=24&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%12345678901215406a6830d189deda8cc%3Fd%3Dmm%26s%3D24%26noRedirect%3Dtrue"
                        ],
                        "16x16": [
                            "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=16&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%12345678901215406a6830d189deda8cc%3Fd%3Dmm%26s%3D16%26noRedirect%3Dtrue"
                        ],
                        "32x32": [
                            "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%12345678901215406a6830d189deda8cc%3Fd%3Dmm%26s%3D32%26noRedirect%3Dtrue"
                        ]
                    },
                    "displayName": [
                        "FName LName"
                    ],
                    "active": [
                        true
                    ],
                    "timeZone": [
                        "America/Los_Angeles"
                    ]
                },
                "updated": [
                    "2017-07-12T13:07:51.000-0700"
                ],
                "status": {
                    "self": [
                        "https://somecompany.atlassian.net/rest/api/2/status/1"
                    ],
                    "description": [
                        "The issue is open and ready for the assignee to start work on it."
                    ],
                    "iconUrl": [
                        "https://somecompany.atlassian.net/images/icons/statuses/open.png"
                    ],
                    "name": [
                        "Open"
                    ],
                    "id": [
                        "1"
                    ],
                    "statusCategory": {
                        "self": [
                            "https://somecompany.atlassian.net/rest/api/2/statuscategory/2"
                        ],
                        "id": [
                            2
                        ],
                        "key": [
                            "new"
                        ],
                        "colorName": [
                            "blue-gray"
                        ],
                        "name": [
                            "To Do"
                        ]
                    }
                },
                "components": [],
                "timeoriginalestimate": {},
                "description": {},
                "customfield_10010": {},
                "customfield_10011": {},
                "customfield_10012": {},
                "customfield_10013": {},
                "customfield_10014": {},
                "customfield_10015": {},
                "customfield_10401": [
                    "FName"
                ],
                "customfield_10600": {},
                "customfield_10402": [
                    "TESTING DATA DOWNLOAD USING API"
                ],
                "customfield_10006": {},
                "customfield_10403": [
                    {
                        "self": [
                            "https://somecompany.atlassian.net/rest/api/2/customFieldOption/10100"
                        ],
                        "value": [
                            "i_file"
                        ],
                        "id": [
                            "10100"
                        ]
                    }
                ],
                "customfield_10007": {},
                "customfield_10800": {},
                "customfield_10008": {},
                "aggregatetimeestimate": {},
                "customfield_10009": {},
                "summary": [
                    "testing"
                ],
                "creator": {
                    "self": [
                        "https://somecompany.atlassian.net/rest/api/2/user?username=UserName"
                    ],
                    "name": [
                        "UserName"
                    ],
                    "key": [
                        "UserName"
                    ],
                    "accountId": [
                        "1234567890"
                    ],
                    "emailAddress": [
                        "[email protected]"
                    ],
                    "avatarUrls": {
                        "48x48": [
                            "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=48&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%12345678901215406a6830d189deda8cc%3Fd%3Dmm%26s%3D48%26noRedirect%3Dtrue"
                        ],
                        "24x24": [
                            "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=24&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%12345678901215406a6830d189deda8cc%3Fd%3Dmm%26s%3D24%26noRedirect%3Dtrue"
                        ],
                        "16x16": [
                            "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=16&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%12345678901215406a6830d189deda8cc%3Fd%3Dmm%26s%3D16%26noRedirect%3Dtrue"
                        ],
                        "32x32": [
                            "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%12345678901215406a6830d189deda8cc%3Fd%3Dmm%26s%3D32%26noRedirect%3Dtrue"
                        ]
                    },
                    "displayName": [
                        "FName LName"
                    ],
                    "active": [
                        true
                    ],
                    "timeZone": [
                        "America/Los_Angeles"
                    ]
                },
                "subtasks": [],
                "reporter": {
                    "self": [
                        "https://somecompany.atlassian.net/rest/api/2/user?username=UserName"
                    ],
                    "name": [
                        "UserName"
                    ],
                    "key": [
                        "UserName"
                    ],
                    "accountId": [
                        "1234567890"
                    ],
                    "emailAddress": [
                        "[email protected]"
                    ],
                    "avatarUrls": {
                        "48x48": [
                            "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=48&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%12345678901215406a6830d189deda8cc%3Fd%3Dmm%26s%3D48%26noRedirect%3Dtrue"
                        ],
                        "24x24": [
                            "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=24&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%12345678901215406a6830d189deda8cc%3Fd%3Dmm%26s%3D24%26noRedirect%3Dtrue"
                        ],
                        "16x16": [
                            "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=16&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%12345678901215406a6830d189deda8cc%3Fd%3Dmm%26s%3D16%26noRedirect%3Dtrue"
                        ],
                        "32x32": [
                            "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%12345678901215406a6830d189deda8cc%3Fd%3Dmm%26s%3D32%26noRedirect%3Dtrue"
                        ]
                    },
                    "displayName": [
                        "FName LName"
                    ],
                    "active": [
                        true
                    ],
                    "timeZone": [
                        "America/Los_Angeles"
                    ]
                },
                "aggregateprogress": {
                    "progress": [
                        0
                    ],
                    "total": [
                        0
                    ]
                },
                "customfield_10000": {},
                "customfield_10001": {},
                "customfield_10002": {},
                "customfield_10004": [
                    "{}"
                ],
                "customfield_10400": [
                    "DA-000000"
                ],
                "environment": {},
                "duedate": [
                    "2017-07-13"
                ],
                "progress": {
                    "progress": [
                        0
                    ],
                    "total": [
                        0
                    ]
                },
                "votes": {
                    "self": [
                        "https://somecompany.atlassian.net/rest/api/2/issue/DATA-1314/votes"
                    ],
                    "votes": [
                        0
                    ],
                    "hasVoted": [
                        false
                    ]
                }
            }
        }
    ]
Improve this question
2
  • Check out the package jsonlite? Commented Jul 17, 2017 at 20:43
  • 1
    This doesn't look very rectangular. What exactly do you want the final table to look like? Commented Jul 17, 2017 at 20:44

2 Answers 2

Reset to default
1

this is something i often have to do as well. for me, i usually save the json as a list in R, then create a dataframe from there based on what i want the dataframe to look like. here is some code to illustrate:

library(tidyverse)
jira <- fromJSON(.....) #this is whatever code you used to get the data from JIRA in R
df <- data_frame(id = jira$id, fielddescription=jira$fields$description)

basically you use the elements within the jira object you created to build your own dataframe. sometimes you'll need to use multiple $ to access nested items, as illustrated above by jira$fields$description

you may also want to explore the tidyjson package. it was meant for problems like this. i found it to be simpler to construct my own dataframe as illustrated here, but for certain use cases or programming styles that may be preferable, depending your taste and situation.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Very useful answer.
Can you pl help me with how to authenticate JIRA access when using fromJSON?
1

Wrap all of that in a set of '' and assign it to a variable json. Then, 

library(jsonlite)
l <- fromJSON(json, simplifyDataFrame = FALSE)
str(l)

As others commented, this won't parse down easily to a data.frame, but here it is as a list.

List of 1
 $ :List of 5
  ..$ expand: chr "operations,versionedRepresentations,editmeta,changelog,renderedFields"
  ..$ id    : chr "22513"
  ..$ self  : chr "https://somecompany.atlassian.net/rest/api/latest/issue/22513"
  ..$ key   : chr "DATA-1314"
  ..$ fields:List of 60
  .. ..$ issuetype                    :List of 7
  .. .. ..$ self       : chr "https://somecompany.atlassian.net/rest/api/2/issuetype/10001"
  .. .. ..$ id         : chr "10001"
  .. .. ..$ description: chr "A task that needs to be done."
  .. .. ..$ iconUrl    : chr "https://somecompany.atlassian.net/secure/viewavatar?size=xsmall&avatarId=10318&avatarType=issuetype"
  .. .. ..$ name       : chr "Task"
  .. .. ..$ subtask    : logi FALSE
  .. .. ..$ avatarId   : int 10318
  .. ..$ timespent                    : Named list()
  .. ..$ project                      :List of 5
  .. .. ..$ self      : chr "https://somecompany.atlassian.net/rest/api/2/project/10300"
  .. .. ..$ id        : chr "10300"
  .. .. ..$ key       : chr "DATA"
  .. .. ..$ name      : chr "Data"
  .. .. ..$ avatarUrls:List of 4
  .. .. .. ..$ 48x48: chr "https://somecompany.atlassian.net/secure/projectavatar?avatarId=10324"
  .. .. .. ..$ 24x24: chr "https://somecompany.atlassian.net/secure/projectavatar?size=small&avatarId=10324"
  .. .. .. ..$ 16x16: chr "https://somecompany.atlassian.net/secure/projectavatar?size=xsmall&avatarId=10324"
  .. .. .. ..$ 32x32: chr "https://somecompany.atlassian.net/secure/projectavatar?size=medium&avatarId=10324"
  .. ..$ fixVersions                  : list()
  .. ..$ aggregatetimespent           : Named list()
  .. ..$ resolution                   : Named list()
  .. ..$ customfield_10500            : Named list()
  .. ..$ customfield_10700            : Named list()
  .. ..$ resolutiondate               : Named list()
  .. ..$ workratio                    : int -1
  .. ..$ lastViewed                   : chr "2017-07-14T15:33:40.422-0700"
  .. ..$ watches                      :List of 3
  .. .. ..$ self      : chr "https://somecompany.atlassian.net/rest/api/2/issue/DATA-1314/watchers"
  .. .. ..$ watchCount: int 1
  .. .. ..$ isWatching: logi TRUE
  .. ..$ created                      : chr "2017-07-12T11:56:12.000-0700"
  .. ..$ customfield_10022            : Named list()
  .. ..$ customfield_10023            : chr "1|i001zz:"
  .. ..$ priority                     :List of 4
  .. .. ..$ self   : chr "https://somecompany.atlassian.net/rest/api/2/priority/3"
  .. .. ..$ iconUrl: chr "https://somecompany.atlassian.net/images/icons/priorities/medium.svg"
  .. .. ..$ name   : chr "Medium"
  .. .. ..$ id     : chr "3"
  .. ..$ customfield_10300            : Named list()
  .. ..$ labels                       : chr [1, 1] "data"
  .. ..$ customfield_10017            : Named list()
  .. ..$ customfield_10018            : Named list()
  .. ..$ timeestimate                 : Named list()
  .. ..$ aggregatetimeoriginalestimate: Named list()
  .. ..$ versions                     : list()
  .. ..$ issuelinks                   : list()
  .. ..$ assignee                     :List of 9
  .. .. ..$ self        : chr "https://somecompany.atlassian.net/rest/api/2/user?username=UserName"
  .. .. ..$ name        : chr "UserName"
  .. .. ..$ key         : chr "UserName"
  .. .. ..$ accountId   : chr "1234567890"
  .. .. ..$ emailAddress: chr "[email protected]"
  .. .. ..$ avatarUrls  :List of 4
  .. .. .. ..$ 48x48: chr "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=48&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%1234567890121"| __truncated__
  .. .. .. ..$ 24x24: chr "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=24&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%1234567890121"| __truncated__
  .. .. .. ..$ 16x16: chr "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=16&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%1234567890121"| __truncated__
  .. .. .. ..$ 32x32: chr "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%1234567890121"| __truncated__
  .. .. ..$ displayName : chr "FName LName"
  .. .. ..$ active      : logi TRUE
  .. .. ..$ timeZone    : chr "America/Los_Angeles"
  .. ..$ updated                      : chr "2017-07-12T13:07:51.000-0700"
  .. ..$ status                       :List of 6
  .. .. ..$ self          : chr "https://somecompany.atlassian.net/rest/api/2/status/1"
  .. .. ..$ description   : chr "The issue is open and ready for the assignee to start work on it."
  .. .. ..$ iconUrl       : chr "https://somecompany.atlassian.net/images/icons/statuses/open.png"
  .. .. ..$ name          : chr "Open"
  .. .. ..$ id            : chr "1"
  .. .. ..$ statusCategory:List of 5
  .. .. .. ..$ self     : chr "https://somecompany.atlassian.net/rest/api/2/statuscategory/2"
  .. .. .. ..$ id       : int 2
  .. .. .. ..$ key      : chr "new"
  .. .. .. ..$ colorName: chr "blue-gray"
  .. .. .. ..$ name     : chr "To Do"
  .. ..$ components                   : list()
  .. ..$ timeoriginalestimate         : Named list()
  .. ..$ description                  : Named list()
  .. ..$ customfield_10010            : Named list()
  .. ..$ customfield_10011            : Named list()
  .. ..$ customfield_10012            : Named list()
  .. ..$ customfield_10013            : Named list()
  .. ..$ customfield_10014            : Named list()
  .. ..$ customfield_10015            : Named list()
  .. ..$ customfield_10401            : chr "FName"
  .. ..$ customfield_10600            : Named list()
  .. ..$ customfield_10402            : chr "TESTING DATA DOWNLOAD USING API"
  .. ..$ customfield_10006            : Named list()
  .. ..$ customfield_10403            :List of 1
  .. .. ..$ :List of 3
  .. .. .. ..$ self : chr "https://somecompany.atlassian.net/rest/api/2/customFieldOption/10100"
  .. .. .. ..$ value: chr "i_file"
  .. .. .. ..$ id   : chr "10100"
  .. ..$ customfield_10007            : Named list()
  .. ..$ customfield_10800            : Named list()
  .. ..$ customfield_10008            : Named list()
  .. ..$ aggregatetimeestimate        : Named list()
  .. ..$ customfield_10009            : Named list()
  .. ..$ summary                      : chr "testing"
  .. ..$ creator                      :List of 9
  .. .. ..$ self        : chr "https://somecompany.atlassian.net/rest/api/2/user?username=UserName"
  .. .. ..$ name        : chr "UserName"
  .. .. ..$ key         : chr "UserName"
  .. .. ..$ accountId   : chr "1234567890"
  .. .. ..$ emailAddress: chr "[email protected]"
  .. .. ..$ avatarUrls  :List of 4
  .. .. .. ..$ 48x48: chr "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=48&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%1234567890121"| __truncated__
  .. .. .. ..$ 24x24: chr "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=24&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%1234567890121"| __truncated__
  .. .. .. ..$ 16x16: chr "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=16&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%1234567890121"| __truncated__
  .. .. .. ..$ 32x32: chr "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%1234567890121"| __truncated__
  .. .. ..$ displayName : chr "FName LName"
  .. .. ..$ active      : logi TRUE
  .. .. ..$ timeZone    : chr "America/Los_Angeles"
  .. ..$ subtasks                     : list()
  .. ..$ reporter                     :List of 9
  .. .. ..$ self        : chr "https://somecompany.atlassian.net/rest/api/2/user?username=UserName"
  .. .. ..$ name        : chr "UserName"
  .. .. ..$ key         : chr "UserName"
  .. .. ..$ accountId   : chr "1234567890"
  .. .. ..$ emailAddress: chr "[email protected]"
  .. .. ..$ avatarUrls  :List of 4
  .. .. .. ..$ 48x48: chr "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=48&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%1234567890121"| __truncated__
  .. .. .. ..$ 24x24: chr "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=24&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%1234567890121"| __truncated__
  .. .. .. ..$ 16x16: chr "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=16&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%1234567890121"| __truncated__
  .. .. .. ..$ 32x32: chr "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%1234567890121"| __truncated__
  .. .. ..$ displayName : chr "FName LName"
  .. .. ..$ active      : logi TRUE
  .. .. ..$ timeZone    : chr "America/Los_Angeles"
  .. ..$ aggregateprogress            :List of 2
  .. .. ..$ progress: int 0
  .. .. ..$ total   : int 0
  .. ..$ customfield_10000            : Named list()
  .. ..$ customfield_10001            : Named list()
  .. ..$ customfield_10002            : Named list()
  .. ..$ customfield_10004            : chr "{}"
  .. ..$ customfield_10400            : chr "DA-000000"
  .. ..$ environment                  : Named list()
  .. ..$ duedate                      : chr "2017-07-13"
  .. ..$ progress                     :List of 2
  .. .. ..$ progress: int 0
  .. .. ..$ total   : int 0
  .. ..$ votes                        :List of 3
  .. .. ..$ self    : chr "https://somecompany.atlassian.net/rest/api/2/issue/DATA-1314/votes"
  .. .. ..$ votes   : int 0
  .. .. ..$ hasVoted: logi FALSE
Improve this answer

1 Comment

The list is what I was getting processing the json output. I couldn't proceed beyond this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.