convert tibble/dataframe to nested JSON with arrays

Question

If got tons of tibbles with a structure like the following:

library(tidyverse)
library(jsonlite)

data <- tibble(Type = c(rep("A", 10), rep("B", 5)),
               SubType = c(rep("a", 2), rep("b", 3), rep("c", 3), rep("d", 2),
                           rep("e", 2), rep("f", 3)),
               SubTypeNr = c(rep(1, 2), rep(2, 3), rep(3, 3), rep(4, 2),
                           rep(5, 2), rep(6, 3)),
               SubTypeVal1 = c(seq(5, 41, by = 4), seq(2, 26, by = 5)),
               SubTypeVal2 = SubTypeVal1 + 1)

UPDATE Following Nad Pads hint, I tried:

data %>% group_by(Type, SubType, SubTypeNr) %>%
  summarise(SubTypeVal1 = list(SubTypeVal1),
            SubTypeVal2 = list(SubTypeVal2)) %>%
  toJSON(pretty = TRUE)

This gets me quite close to what I want (see below). How do I have to group the data to achieve something like this (values nested in SubTypes nested in Types):

{"Type" : "A", {
  "SubType" : "a",
  "SubTypeNr" : "1", {
    "SubTypeVal1" : [5, 9],
    "SubTypeVal1" : [6, 10],
  }
  "SubType" : "b",
  "SubTypeNr" : "2", {
    "SubTypeVal1" : [13, 17, 21],
    "SubTypeVal1" : [14, 18, 22],
  }
  "SubType" : "c",
  "SubTypeNr" : "3", {
    "SubTypeVal1" : [25, 29, 33],
    "SubTypeVal1" : [26, 30, 34],
  }
  "SubType" : "d",
  "SubTypeNr" : "4", {
    "SubTypeVal1" : [37, 41],
    "SubTypeVal1" : [38, 42],
  }
}
}
{"Type" : "B", {
  "SubType" : "e",
  "SubTypeNr" : "5", {
    "SubTypeVal1" : [2, 7],
    "SubTypeVal1" : [3, 8],
  }
  "SubType" : "f",
  "SubTypeNr" : "6", {
    "SubTypeVal1" : [12, 17, 22],
    "SubTypeVal1" : [13, 18, 23],
  }
}
}

Refer https://stackoverflow.com/questions/25550711/r-convert-dataframe-to-json, https://stackoverflow.com/questions/40665269/r-convert-data-frame-to-json — Nad Pat, Dec 03 '21 at 14:02
ok, thanks, the second link gets me quite close. I'll update my question. — Rumpl, Dec 03 '21 at 14:32

Aurèle · Accepted Answer · 2021-12-03T15:15:09.150

data %>% 
  nest(TypeData = !Type) %>% 
  mutate(across(TypeData, map, nest, SubTypeData = !c(SubType, SubTypeNr))) %>% 
  mutate(across(TypeData, map, mutate, across(SubTypeData, map, summarise, across(everything(), list)))) %>%
  toJSON(pretty = TRUE)

Alternatively, based on OP's update:

data %>% 
  group_by(Type, SubType, SubTypeNr) %>%
  summarise(across(everything(), list), .groups = "drop") %>% 
  nest(SubTypeData = !c(Type, SubType, SubTypeNr)) %>% 
  nest(TypeData = !Type) %>% 
  toJSON(pretty = TRUE)

[
  {
    "Type": "A",
    "TypeData": [
      {
        "SubType": "a",
        "SubTypeNr": 1,
        "SubTypeData": [
          {
            "SubTypeVal1": [5, 9],
            "SubTypeVal2": [6, 10]
          }
        ]
      },
      {
        "SubType": "b",
        "SubTypeNr": 2,
        "SubTypeData": [
          {
            "SubTypeVal1": [13, 17, 21],
            "SubTypeVal2": [14, 18, 22]
          }
        ]
      },
      {
        "SubType": "c",
        "SubTypeNr": 3,
        "SubTypeData": [
          {
            "SubTypeVal1": [25, 29, 33],
            "SubTypeVal2": [26, 30, 34]
          }
        ]
      },
      {
        "SubType": "d",
        "SubTypeNr": 4,
        "SubTypeData": [
          {
            "SubTypeVal1": [37, 41],
            "SubTypeVal2": [38, 42]
          }
        ]
      }
    ]
  },
  {
    "Type": "B",
    "TypeData": [
      {
        "SubType": "e",
        "SubTypeNr": 5,
        "SubTypeData": [
          {
            "SubTypeVal1": [2, 7],
            "SubTypeVal2": [3, 8]
          }
        ]
      },
      {
        "SubType": "f",
        "SubTypeNr": 6,
        "SubTypeData": [
          {
            "SubTypeVal1": [12, 17, 22],
            "SubTypeVal2": [13, 18, 23]
          }
        ]
      }
    ]
  }
]

Thanks for hinting me to `nest`. Nesting from back to front seems to be easier and yields almost the same result: `data %>% nest(vals = c(SubTypeVal1, SubTypeVal2)) %>% nest(sub = c(SubType, SubTypeNr, vals)) %>% toJSON(pretty = TRUE)` — Rumpl, Dec 03 '21 at 15:13

convert tibble/dataframe to nested JSON with arrays

1 Answers1

Linked