Can I use the jsonlite:toJSON method to convert a data frame or tibble into this specific format of deeply nested JSON?

user_2766558 0
Viewed 55

I'm working with the Orbitly API, and their API requires a JSON string in this specific format:

{
    "requests": [
        {
            "params": {
                "email": ["steve@woz.org"]
            }
        },
        {
            "params": {
                "profile": ["https://www.linkedin.com/in/brianchesky/"]
            }
        },
        {
            "params": {
                "phone": ["17186893174"]
            }
        },
        {
            "params": {
                "name": ["marc benioff"],
                "company": ["salesforce"]
            }
        }
    ],
    "required": "profiles.network:linkedin AND (phone_numbers OR emails)"
}

My data is currently in this format (imported from a spreadsheet):

> df
# A tibble: 4 x 5
  email         profile                                  phone       name         company   
  <chr>         <chr>                                    <chr>       <chr>        <chr>     
1 steve@woz.org NA                                       NA          NA           NA        
2 NA            https://www.linkedin.com/in/brianchesky/ NA          NA           NA        
3 NA            NA                                       17186893174 NA           NA        
4 NA            NA                                       NA          marc benioff salesforce

If I call the jsonlite::toJSON method, I can convert the data frame/tibble into a format that is sort of close, but understandably, it doesn't bundle anything into lists, and as far as I can tell, there's no way to force toJSON to use the somewhat unnatural bundling with params in front of everything:

> print(toJSON(df, simplifyVector=FALSE, pretty=TRUE))
[
  {
    "email": "steve@woz.org"
  },
  {
    "profile": "https://www.linkedin.com/in/brianchesky/"
  },
  {
    "phone": "17186893174"
  },
  {
    "name": "marc benioff",
    "company": "salesforce"
  }
]

Is my only hope to get this data frame into the right format some manually string appending? Or is there a more clever way to apply the toJSON method?

dataframe r json
1 Answers
jsonlite::toJSON(list(
    requests=unname(do.call(Map, c(list(f=function(...) list(params = Filter(Negate(is.na), list(...)))), df))), 
    required="profiles.network:linkedin AND (phone_numbers OR emails)"), 
  pretty = TRUE)
# {
#   "requests": [
#     {
#       "params": {
#         "email": ["steve@woz.org"]
#       }
#     },
#     {
#       "params": {
#         "profile": ["https://www.linkedin.com/in/brianchesky/"]
#       }
#     },
#     {
#       "params": {
#         "phone": ["17186893174"]
#       }
#     },
#     {
#       "params": {
#         "name": ["marc benioff"],
#         "company": ["salesforce"]
#       }
#     }
#   ],
#   "required": ["profiles.network:linkedin AND (phone_numbers OR emails)"]
# }

Data

df <- structure(list(email = c("steve@woz.org", NA, NA, NA), profile = c(NA, "https://www.linkedin.com/in/brianchesky/", NA, NA), phone = c(NA, NA, "17186893174", NA), name = c(NA, NA, NA, "marc benioff"), company = c(NA, NA, NA, "salesforce")), class = "data.frame", row.names = c(NA, 4L))
user_33582720
Related