Hi,
You did not provide the original JSON (tenable_json), so I worked from the data frame you already have after first changes (tenable.df) by recreating it:
library(tidyverse)
library(jsonlite)
#Example of a JSON string
string = '[{"id":"age_of_vuln","name":"Vulnerability Age",
"type":"string","value":"730 days +"},
{"id":"cvssV3_impactScore","name":"CVSS v3 Impact Score"}]'
#Generate data frame as input
myData = tibble(
pluginID = 1:5, name = paste0("test", 1:5),
vprContext = c("[]", "[]", string,"[]", string)
)
myData
#> # A tibble: 5 x 3
#> pluginID name vprContext
#> <int> <chr> <chr>
#> 1 1 test1 "[]"
#> 2 2 test2 "[]"
#> 3 3 test3 "[{\"id\":\"age_of_vuln\",\"name\":\"Vulnerability Age\",\n\"t~
#> 4 4 test4 "[]"
#> 5 5 test5 "[{\"id\":\"age_of_vuln\",\"name\":\"Vulnerability Age\",\n\"t~
#COnvert the json part to a new data frame
vprDF = apply(myData, 1, function(x){
y = fromJSON(x["vprContext"])
y$pluginID = x["pluginID"]
y
})
vprDF = bind_rows(vprDF, row.names = NULL) %>%
mutate(pluginID = as.integer(pluginID))
vprDF
#> # A tibble: 7 x 5
#> pluginID id name type value
#> <int> <chr> <chr> <chr> <chr>
#> 1 1 <NA> <NA> <NA> <NA>
#> 2 2 <NA> <NA> <NA> <NA>
#> 3 3 age_of_vuln Vulnerability Age string 730 days +
#> 4 3 cvssV3_impactScore CVSS v3 Impact Score <NA> <NA>
#> 5 4 <NA> <NA> <NA> <NA>
#> 6 5 age_of_vuln Vulnerability Age string 730 days +
#> 7 5 cvssV3_impactScore CVSS v3 Impact Score <NA> <NA>
#Join it with rest of the data
myData = myData %>% select(-vprContext) %>%
left_join(vprDF, by = "pluginID")
myData
#> # A tibble: 7 x 6
#> pluginID name.x id name.y type value
#> <int> <chr> <chr> <chr> <chr> <chr>
#> 1 1 test1 <NA> <NA> <NA> <NA>
#> 2 2 test2 <NA> <NA> <NA> <NA>
#> 3 3 test3 age_of_vuln Vulnerability Age string 730 days +
#> 4 3 test3 cvssV3_impactScore CVSS v3 Impact Score <NA> <NA>
#> 5 4 test4 <NA> <NA> <NA> <NA>
#> 6 5 test5 age_of_vuln Vulnerability Age string 730 days +
#> 7 5 test5 cvssV3_impactScore CVSS v3 Impact Score <NA> <NA>
Created on 2021-03-18 by the reprex package (v1.0.0)
Hope this helps,
PJ