want to parse nested json in R studio, I have tried several codes but it didn't help me in solving my issue. I made a connection between R studio and API and is able to get data in JSON format. It's a nested JSON and now I have to parse it. When I am trying to parse it this is what I am getting attaching the image below.
This is the most important columns that I want to parse. This is my code, from the start. I'll be really thankful if anyone can help me solve this issue, as I have wasted so much time to figure this out. Below is the image of my R studio code.
#Connection code
path <- "URL of my API"
r <- GET(path)
status_code(r)
str(content(r))
r <- content(r, as = "text", encoding = "UTF-8")
#Storing data in a variable
res <- jsonlite::fromJSON(r)
res
#Write and read it in Excel file
write.xlsx2(res, file='JSONData.xlsx')
read.xlsx2("JSONData.xlsx", sheetName = "Sheet1")
#Storing un-parsed data in a dataframe
df <- read_excel("JSONData.xlsx")
df
View(df)
#Parse Nested JSON
list_json1 <- fromJSON(r)
list_json1$paging <- drop
glimpse(list_json1)
print(list_json1)
df_json1 <- as_tibble(list_json1, validate = F)
print(df_json1)
glimpse(df_json1)
list_json1 <- map_if(list_json1, is.data.frame, list)
glimpse(list_json1)
df_json1 <- as_tibble(list_json1)
print(df_json1)
df_json1 <- unnest(df_json1)
print(df_json1)
View(df_json1)
df_json1 %>%
unnest() %>%
unnest()
df_json1 %>%
unnest(items.implements_logical_data_models) %>%
unnest(items.implements_logical_data_models)
I don't know if it's the right way to parse or not.
This is how my JSON file looks like:
{
"paging": {
"numTotal": 2070,
"next": "",
"pageSize": 1,
"end": 0,
"begin": 0
},
"items": [
{
"_name": "AC",
"_context": [
{
"_name": "Mainframe",
"_type": "host",
"_id": "",
"_url": ""
},
{
"_name": "DS",
"_type": "database",
"_id": "",
"_url": ""
}
],
"_type": "database_schema",
"_id": "",
"_url": "",
"implements_physical_data_models": {
"paging": {
"numTotal": 0,
"pageSize": 0,
"end": -1,
"begin": 0
},
"items": []
},
"implements_logical_data_models": {
"paging": {
"numTotal": 0,
"pageSize": 0,
"end": -1,
"begin": 0
},
"items": []
}
}
]
}
I want implements_physical_data_models and implements_logical_data_models to be parsed properly