R convert data.frame to json

Question

I'm trying to convert a data.frame into json format

my data.frame has the following structure

a   <- rep(c("Mario", "Luigi"), each = 3)
b   <- sample(34:57, size = length(a))
df  <- data.frame(a,b)
> df
      a  b
1 Mario 43
2 Mario 34
3 Mario 36
4 Luigi 45
5 Luigi 52
6 Luigi 35

What I want to create is something like this (to finally print it to a .json file)

[
  {
    "a": "Mario",
    "b": [43, 34, 36]
  },
  {
    "a": "Luigi",
    "b": [45, 52, 35]
  }
]

I've tried different packages handling json format but so far failed to produce this kind of output. I usually end up with something like this

[
  {
   "a":"Mario",
   "b":43
  },
  {
   "a":"Mario",
   "b":34
  },
  {
   "a":"Mario",
   "b":36
  },
  {
   "a":"Luigi",
   "b":45
  },
  {
   "a":"Luigi",
   "b":52
  },
  {
   "a":"Luigi",
   "b":35
  }
]

Answer 1

If you nest b as a list column, it will convert correctly:

library(jsonlite)

# converts b to nested list column
df2 <- aggregate(b ~ a, df, list)

df2
##       a          b
## 1 Luigi 49, 42, 37
## 2 Mario 46, 50, 45

toJSON(df2, pretty = TRUE)
## [
##   {
##     "a": "Luigi",
##     "b": [49, 42, 37]
##   },
##   {
##     "a": "Mario",
##     "b": [46, 50, 45]
##   }
## ] 

or if you prefer dplyr:

library(dplyr)

df %>% group_by(a) %>% 
    summarise(b = list(b)) %>% 
    toJSON(pretty = TRUE)

or data.table:

library(data.table)

toJSON(setDT(df)[, .(b = list(b)), by = a], pretty = TRUE)

which both return the same thing.