Nested R dataframe to JSON with objects instead of arrays

Question

I need to convert a dataframe to JSON. There are several nested dataframes as variables in the dataframe to convert to JSON.

But, when converting to JSON, I need the data for Values1 described below to be an object (enclosed in {} only) instead of an array (enclosed in []).

The code below is a reprex to show my current workflow and problem.

library(dplyr)
library(tidyr)
library(jsonlite)

df1 <- data.frame(name = c("a", "b", "c"),
                 v = c(1, 2, 3),
                 w = c(10, 20, 30)) %>%
  group_by(name) %>%
  nest_legacy(.key = "Values1")

df2 <- data.frame(name = c("a", "b", "c"),
                  x = c(5, 10, 15),
                  y = c(100, 200, 300),
                  z = c(1000, 2000, 3000)) %>%
  group_by(name) %>%
  nest_legacy(.key = "Values2")

df3 <- df1 %>%
  left_join(df2)

json <- toJSON(df3, dataframe = "rows", pretty = TRUE)

json

This is what the json from the above looks like:

> json
[
  {
    "name": "a",
    "Values1": [
      {
        "v": 1,
        "w": 10
      }
    ],
    "Values2": [
      {
        "x": 5,
        "y": 100,
        "z": 1000
      }
    ]
  },
  {
    "name": "b",
    "Values1": [
      {
        "v": 2,
        "w": 20
      }
    ],
    "Values2": [
      {
        "x": 10,
        "y": 200,
        "z": 2000
      }
    ]
  },
  {
    "name": "c",
    "Values1": [
      {
        "v": 3,
        "w": 30
      }
    ],
    "Values2": [
      {
        "x": 15,
        "y": 300,
        "z": 3000
      }
    ]
  }
] 

But, this is what I need it to look like:

> json
[
  {
    "name": "a",
    "Values1": {
        "v": 1,
        "w": 10
      },
    "Values2": [
      {
        "x": 5,
        "y": 100,
        "z": 1000
      }
    ]
  },
  {
    "name": "b",
    "Values1": {
        "v": 2,
        "w": 20
      },
    "Values2": [
      {
        "x": 10,
        "y": 200,
        "z": 2000
      }
    ]
  },
  {
    "name": "c",
    "Values1": {
        "v": 3,
        "w": 30
      },
    "Values2": [
      {
        "x": 15,
        "y": 300,
        "z": 3000
      }
    ]
  }
] 

Any ideas how to convert Values1 from arrays to objects? They cannot be in arrays as the API does not accept the [.

I have looked at using unbox() from jsonlite but this only works on singletons. I have also tried to construct my final dataframe in various ways.

Any tips/ideas greatly appreciated!

Felton

Answer 1

This should return what you desire:

...

df3 <- left_join(df1, df2, by = "name") 

json <- mutate(df3, Values1 = purrr::map(Values1, as.list)) %>% 
  jsonlite::toJSON(auto_unbox = TRUE, pretty = TRUE)

Can you confirm?

Nubbin

Yes! Yes! Thank you :) I've tested on my actual data, which is a lot more involved than the reprex, and it works like a charm. I knew I had to somehow convert the nested dfs to lists and use the unbox functionality, but just couldn't figure it out. Thanks for the speedy, succinct reply. – Felton

Yey, cool! I find the solution a little bit hacky, but couldn't figure out a cleaner way. (by the way, I got the inspiration from this semi-related temporary fix on tidyr v1.0.0 nested structures & jsonlite: github.com/jeroen/jsonlite/issues/292#issue-494791462) – Nubbin