R: Parsing, Modifying and Saving a Json File

Question

I am not at all an expert about Json files and I am struggling to complete a simple task. Consider the json file below stored in test.json

{
  "entry_1": {
    "AT": null,
    "BE": null,
    "BG": null,
    "CY": null,
    "CZ": null,
    "DE": null,
    "DK": null,
    "EE": null,
    "EL": null,
    "ES": null,
    "FI": null,
    "FR": null,
    "HR": null,
    "HU": null,
    "IE": null,
    "IT": null,
    "LT": null,
    "LU": null,
    "LV": null,
    "MT": null,
    "NL": null,
    "PL": null,
    "PT": null,
    "RO": null,
    "SI": null,
    "SK": null
  },
  "entry_2": {
    "AT": null,
    "BE": null,
    "BG": null,
    "CY": null,
    "CZ": null,
    "DE": null,
    "DK": null,
    "EE": null,
    "EL": null,
    "ES": null,
    "FI": null,
    "FR": null,
    "HR": null,
    "HU": null,
    "IE": null,
    "IT": null,
    "LT": null,
    "LU": null,
    "LV": null,
    "MT": null,
    "NL": null,
    "PL": null,
    "PT": null,
    "RO": null,
    "SI": null,
    "SK": null
  },
  "entry_3": {
    "AT": null,
    "BE": null,
    "BG": null,
    "CY": null,
    "CZ": null,
    "DE": null,
    "DK": null,
    "EE": null,
    "EL": null,
    "ES": null,
    "FI": null,
    "FR": null,
    "HR": null,
    "HU": null,
    "IE": null,
    "IT": null,
    "LT": null,
    "LU": null,
    "LV": null,
    "MT": null,
    "NL": null,
    "PL": null,
    "PT": null,
    "RO": null,
    "SI": null,
    "SK": null
  },
  "entry_4": {
    "AT": null,
    "BE": null,
    "BG": null,
    "CY": null,
    "CZ": null,
    "DE": null,
    "DK": null,
    "EE": null,
    "EL": null,
    "ES": null,
    "FI": null,
    "FR": null,
    "HR": null,
    "HU": null,
    "IE": null,
    "IT": null,
    "LT": null,
    "LU": null,
    "LV": null,
    "MT": null,
    "NL": null,
    "PL": null,
    "PT": null,
    "RO": null,
    "SI": null,
    "SK": null
  },
  "entry_5": {
    "AT": null,
    "BE": null,
    "BG": null,
    "CY": null,
    "CZ": null,
    "DE": null,
    "DK": null,
    "EE": null,
    "EL": null,
    "ES": null,
    "FI": null,
    "FR": null,
    "HR": null,
    "HU": null,
    "IE": null,
    "IT": null,
    "LT": null,
    "LU": null,
    "LV": null,
    "MT": null,
    "NL": null,
    "PL": null,
    "PT": null,
    "RO": null,
    "SI": null,
    "SK": null
  }
}

I would like to read it in my R session and then modify its entries and save it back as a new Json file. Based on the suggestions I received, I paste below a revised reprex

library(tidyverse)
library(jsonlite)
#> 
#> Attaching package: 'jsonlite'
#> The following object is masked from 'package:purrr':
#> 
#>     flatten


entry_1 <- tibble(x=c("AT", "IT", "HU"), value=c(12, 0.9, 4)) |>
    pivot_wider(names_from= x, values_from=value)


entry_2 <- tibble(x=c("FR", "IE", "RO"), value=c(1.2, 0.9, 4.8))|>
    pivot_wider(names_from= x, values_from=value)

entry_3 <- tibble(x=c("DE", "FI", "EL"), value=c(1.7, 0.09, 4.7))|>
    pivot_wider(names_from= x, values_from=value)

entry_4 <- tibble(x=c("SK", "LT", "BG"), value=c(1.8, 0.967, 4.6))|>
    pivot_wider(names_from= x, values_from=value)

entry_5 <- tibble(x=c("FR", "IT", "IE"), value=c(129, 9.4, 4.3))|>
    pivot_wider(names_from= x, values_from=value)



newdata <- list(entry_1, entry_2, entry_3, entry_4, entry_5)
names(newdata) <- c("entry_1", "entry_2", "entry_3", "entry_4", "entry_5")



newdataJSON<-toJSON(newdata, pretty=TRUE, auto_unbox = TRUE)

newdataJSON
#> {
#>   "entry_1": [
#>     {
#>       "AT": 12,
#>       "IT": 0.9,
#>       "HU": 4
#>     }
#>   ],
#>   "entry_2": [
#>     {
#>       "FR": 1.2,
#>       "IE": 0.9,
#>       "RO": 4.8
#>     }
#>   ],
#>   "entry_3": [
#>     {
#>       "DE": 1.7,
#>       "FI": 0.09,
#>       "EL": 4.7
#>     }
#>   ],
#>   "entry_4": [
#>     {
#>       "SK": 1.8,
#>       "LT": 0.967,
#>       "BG": 4.6
#>     }
#>   ],
#>   "entry_5": [
#>     {
#>       "FR": 129,
#>       "IT": 9.4,
#>       "IE": 4.3
#>     }
#>   ]
#> }

^{Created on 2022-09-27 by the reprex package (v2.0.1)}

which comes very close to what I need. One thing only bothers me: is there any way to get rid of all the square brackets in the final Json file?

What are you keeping from the old dataframe? What is your expected output? It sounds like you just want to replace, so why not make a new JSON? — dcsuka, Sep 26 '22 at 21:24
I'm with dcsuka: if you wanted to _replace_ the original values with your new numbers _and keep the remaining fields unchanged_, then I can see wanting to modify the source data in-place and writing it back to file. However, since you want to discard the unchanged fields, why not form your local `entry_1` the way you need it (as a named list, not a tibble) and write directly? — r2evans, Sep 26 '22 at 22:06
Thanks a lot for your suggestions! It is actually simpler to construct a new Json froms scratch. Do you know how to get rid of the square brackets in the updated new Json file I generate above (I updated the reprex)? — larry77, Sep 27 '22 at 21:12

score 0 · Answer 1 · answered Sep 26 '22 at 23:03

Maybe this will work for you.
Your original data is a series of named lists in a named list. The following code creates a list of list for the new data. Easier than trying to delete out NULL elements. The second part will merge the newly created name list and update the values in the original named list.
See comments for details:

library(jsonlite)
#read in the JSON file
originaldata <- fromJSON(..your JSON data goes here..)

#Input new data (named list)
entry_1<- setNames(list(12, 0.9, 4), c("AT", "IT", "HU"))
entry_2<- setNames(list(1.2, 0.9, 4.8), c("FR", "IE", "RO"))
entry_3<- setNames(list(1.7, 0.09, 4.7), c("DE", "FI", "EL"))
entry_4<- setNames(list(1.8, 0.967, 4.6), c("SK", "LT", "BG"))
entry_5<- setNames(list(129, 9.4, 4.3), c("FR", "IT", "IE"))

#named list of list
newdata <- list(entry_1, entry_2, entry_3, entry_4, entry_5)
names(newdata) <- c("entry_1", "entry_2", "entry_3", "entry_4", "entry_5")

#Create JSON file with just the new data
newdataJSON<-toJSON(newdata, pretty=TRUE, auto_unbox = TRUE)

#or
#update original JSON data
out<- lapply(names(newdata), function(i) {  #get names of list elements
   print(i)  #debugging
   lapply(names(newdata[[i]]), function(j) {  #get names of list-list elemnts
      #update orginaldata named list
      originaldata[[i]][[j]] <<- newdata[[i]][[j]]
   })
})

#convert back to JSON
toJSON(originaldata, pretty=TRUE, auto_unbox = TRUE)

score 0 · Answer 2 · answered Sep 28 '22 at 17:19

Thanks for your suggestions. The snippet below gets the job done.

library(tidyverse)
library(jsonlite)
#> 
#> Attaching package: 'jsonlite'
#> The following object is masked from 'package:purrr':
#> 
#>     flatten

#See https://stackoverflow.com/questions/73859986/r-parsing-modifying-and-saving-a-json-file

entry_1 <- tibble(x=c("AT", "IT", "HU"), value=c(12, 0.9, 4)) |>
    pivot_wider(names_from= x, values_from=value)


entry_2 <- tibble(x=c("FR", "IE", "RO"), value=c(1.2, 0.9, 4.8))|>
    pivot_wider(names_from= x, values_from=value)

entry_3 <- tibble(x=c("DE", "FI", "EL"), value=c(1.7, 0.09, 4.7))|>
    pivot_wider(names_from= x, values_from=value)

entry_4 <- tibble(x=c("SK", "LT", "BG"), value=c(1.8, 0.967, 4.6))|>
    pivot_wider(names_from= x, values_from=value)

entry_5 <- tibble(x=c("FR", "IT", "IE"), value=c(129, 9.4, 4.3))|>
    pivot_wider(names_from= x, values_from=value)



newdata <- list(entry_1, entry_2, entry_3, entry_4, entry_5)
names(newdata) <- c("entry_1", "entry_2", "entry_3", "entry_4", "entry_5")



newdata <- list(entry_1, entry_2, entry_3, entry_4, entry_5)
names(newdata) <- c("entry_1", "entry_2", "entry_3", "entry_4", "entry_5")


## convert to Json and remove square brackets

## see https://stackoverflow.com/a/7195889

newdataJSON<-toJSON(newdata, pretty=TRUE, auto_unbox = TRUE) |>
    (\(x) gsub("\\[|\\]", "", x))()

newdataJSON
#> {
#>   "entry_1": 
#>     {
#>       "AT": 12,
#>       "IT": 0.9,
#>       "HU": 4
#>     }
#>   ,
#>   "entry_2": 
#>     {
#>       "FR": 1.2,
#>       "IE": 0.9,
#>       "RO": 4.8
#>     }
#>   ,
#>   "entry_3": 
#>     {
#>       "DE": 1.7,
#>       "FI": 0.09,
#>       "EL": 4.7
#>     }
#>   ,
#>   "entry_4": 
#>     {
#>       "SK": 1.8,
#>       "LT": 0.967,
#>       "BG": 4.6
#>     }
#>   ,
#>   "entry_5": 
#>     {
#>       "FR": 129,
#>       "IT": 9.4,
#>       "IE": 4.3
#>     }
#>   
#> }

write(newdataJSON, "myfile.json")

^{Created on 2022-09-28 by the reprex package (v2.0.1)}

R: Parsing, Modifying and Saving a Json File

2 Answers2