Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Generate JSON from R data, tidyverse style

Is there a way to generate a JSON string from data that is contained in an R Tidyverse tibble such like this:

library(tidyverse)
d <- tribble(
  ~last_name,  ~first_name, ~role,          ~admin,
  "Gale",      "Dorothy",   "board member", FALSE,
  "Man",       "Tin",       "member",       TRUE
)

d |>
  mutate(json_data = generate_some_json(role, admin)) |> 
  select(last_name, first_name, json_data)
#> Error in `mutate()` at dplyr/R/select.R:54:3:
#> ℹ In argument: `json_data = generate_some_json(role, admin)`.
#> Caused by error in `generate_some_json()`:
#> ! could not find function "generate_some_json"

Created on 2024-10-16 with reprex v2.1.1

Where generate_some_json is the function that I am looking for, but which may not even exist?

Desired, but fabricated result:

# A tibble: 2 x 3
  last_name first_name json_data
  <chr>     <chr>      <chr>        
1 Gale      Dorothy    {"role": "board member", "admin": "FALSE"}
2 Man       Tin        {"role": "member", "admin": "TRUE"}

I’ve experimented with the jsonlite and tidyjson packages but either wasn’t doing it right or (what I suspect) these libraries do not support this kind of operation.

I do get the desired result with a custom function (below), but I would prefer to have something more sustainable for future tasks (e.g., with a larger number of variables).

make_json <- function(role, admin) {
  str_glue(
    '{{',
    '"role": "{role}"',
    ', ',
    '"admin": "{admin}"',
    '}}'
  )
}
# and then use something like
# mutate(json_data = make_json(role, admin))

Created on 2024-10-16 with reprex v2.1.1

Tags:

3

Answers


  1. 0

    It’s actually a bit annoying to get your desired output exactly using jsonlite::toJSON(), because it expects dataframe input, and purr/rowwise approaches will pass it the data directly. Here’s a way to use group_map to pass toJSON one-row dataframes (so you keep the keys). Note that using last_name and first_name as grouping variables excludes them from the JSON as your desired output shows.

    library(tidyverse)
d <- tribble(
  ~last_name,  ~first_name, ~role,          ~admin,
  "Gale",      "Dorothy",   "board member", FALSE,
  "Man",       "Tin",       "member",       TRUE
)

json_data <- d %>%
  group_by(last_name, first_name) %>%
  group_map(~ jsonlite::toJSON(.x)) %>%
  as.character()

d %>%
  mutate(json_data = json_data) %>%
  select(-role, -admin)
#> # A tibble: 2 × 3
#>   last_name first_name json_data                                      
#>   <chr>     <chr>      <chr>                                          
#> 1 Gale      Dorothy    "[{"role":"board member","admin":false}]"
#> 2 Man       Tin        "[{"role":"member","admin":true}]"

    Created on 2024-10-16 with reprex v2.1.1

    Login or Signup to reply.
  2. 0

    To get that exact format with jsonlite, we could use rowwise groping and pick() to generate single-row frames, converting those to lists will avoid creating JSON array:

    library(tidyverse)

d <- tribble(
  ~last_name,  ~first_name, ~role,          ~admin,
  "Gale",      "Dorothy",   "board member", FALSE,
  "Man",       "Tin",       "member",       TRUE
)

d |> 
  rowwise() |> 
  mutate(
    json_data = pick(role,admin) |> as.list() |> jsonlite::toJSON(auto_unbox = TRUE),
    .keep = "unused"
  ) |> 
  ungroup()
#> # A tibble: 2 × 3
#>   last_name first_name json_data                                    
#>   <chr>     <chr>      <json>                                       
#> 1 Gale      Dorothy    "{"role":"board member","admin":false}"
#> 2 Man       Tin        "{"role":"member","admin":true}"

    Another alternative would be rjson, in this particular case it happens to generate desired output with less steps & arguments:

    d |> 
  rowwise() |> 
  mutate(json_data = pick(role,admin) |> rjson::toJSON(), .keep = "unused") |> 
  ungroup()
#> # A tibble: 2 × 3
#>   last_name first_name json_data                                    
#>   <chr>     <chr>      <chr>                                        
#> 1 Gale      Dorothy    "{"role":"board member","admin":false}"
#> 2 Man       Tin        "{"role":"member","admin":true}"
    Login or Signup to reply.
  3. 0 
    d %>%
   group_by(last_name, first_name) %>%
   summarise(json_data = jsonlite::toJSON(pick(everything())), .groups = 'drop')

# A tibble: 2 × 3
# Groups:   last_name [2]
  last_name first_name json_data                                      
  <chr>     <chr>      <json>                                         
1 Gale      Dorothy    "[{"role":"board member","admin":false}]"
2 Man       Tin        "[{"role":"member","admin":true}]"
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search