How to merge data in to a pre-existing JSON structure in R?

Question

First time poster, long time lurker. Be gentle. Moderate R user. I am sure there is a better, functional way to do what I need, but felt like I have researched do death with no insight.

I am trying to merge a data set to a pre-existing JSON structure. Where one row of records per JSON structure for many serialized JSON requests.

I load the data set to data which is 13 variables and change the column headers to match how they appear in the JSON structure

library(jsonlite)
#### Map Column headers to their respective names in the JSON Structure
colnames(data) <- c("default.A",
                    "default.B",
                    "default.C",
                    "items.A",
                    "items.B.1",
                    "items.B.2",
                    "items.B.3",
                    "items.B.4",
)

Create the blank JSON Structure. This is the format for which the JSON requests need to be handled. Simple nested structure.

sample <- '{
      "default": {
           "A": "",
           "B": "",
           "C": "",
            },
      "items": [{
           "A": "",
           "B": {
                "1": "",
                "2": "",
                "3": "",
                "4": "",
                     }
                }]
           }'

jsonstructure <- fromJSON(sample)

set everything as a DF. merge them. Fill NAs with Blanks

x <- as.data.frame(data)
y <- as.data.frame(jsonstructure)
Z <- merge(x, y, all = TRUE)
Z[is.na(Z)] <- ""

Convert to JSON

jsonZ <- toJSON(unname(split(Z, 1:nrow(Z))), pretty=TRUE)
cat(jsonZ)

Current output which does not match

[
  [
    {
   "default.A": "",
      "default.B": "1234567890",
      "default.C": "",
      "items.A": "1234567890",
      "items.B.1": "1234",
      "items.B.2": "1234",
      "items.B.3": "1234",
      "items.B.4": "1234",
    }
  ],
  [
    {
   "default.A": "",
      "default.B": "0987654321",
      "default.C": "",
      "items.A": "0987654321",
      "items.B.1": "4321",
      "items.B.2": "4321",
      "items.B.3": "4321",
      "items.B.4": "4321",
    }
  ]
]

score 1 · Answer 1 · answered Dec 06 '16 at 17:17

If you aren't dead set on jsonlite package, you could try the rjson package

library(rjson)

value = c("", "1234690","")
names(value) = c("A","B","C")


value2 = c("","0987654321","","0987654321")
names(value2) = c("1","2","3","4") 

test <- toJSON(list( "default" = value, "items" =  list(c("A" = "", "B" = list(value2))) ))
cat(test)
writeLines(test, "test.json")

cryo111 · Accepted Answer · 2016-12-06T18:57:36.197

1

Could not reproduce your results - but here is my guess of what you want to achieve. See comments for help with the code.

library(jsonlite)

#data.frame with data - you have probably more than 2 rows
data=data.frame(rbind(t(c(NA,1234567890,NA,1234567890,1234,1234,1234,1234)),
                      t(c(1,NA,2,3,1,1000,NA,1234))))

cn=c("default.A",
      "default.B",
      "default.C",
      "items.A",
      "items.B.1",
      "items.B.2",
      "items.B.3",
      "items.B.4")

colnames(data)=cn

#assuming that "." represents structure
mapping=strsplit(cn,"\\.")

#template JSON
jsonstructure <- fromJSON('{"default": {"A": "","B": "","C": ""},
                          "items": [{"A": "",
                                     "B": {"1": "","2": "","3": "","4": ""}}]}')

#now loop through all rows in your data.frame and store them in JSON format
#this will give you a list with JSON objects (i.e., a list of lists)
json_list=lapply(split(data,1:nrow(data)),function(data_row) {
  for (i in seq_along(mapping)) jsonstructure[[mapping[[i]]]]<-data_row[,cn[i]]
  jsonstructure
})

Result:

toJSON(json_list[[2]],pretty = TRUE, auto_unbox=TRUE)
#{
#  "default": {
#    "A": 1,
#    "B": "NA",
#    "C": 2
#  },
#  "items": [
#    {
#      "A": 3,
#      "B": {
#        "1": 1,
#        "2": 1000,
#        "4": 1234
#      }
#    }
#  ]
#}

Just another comment. My approach makes use of recursive subsetting of lists as described in the help to the [ operator:

[[ can be applied recursively to lists, so that if the single index i is a vector of length p, alist[[i]] is equivalent to alist[[i1]]...[[ip]] providing all but the final indexing results in a list.

edited Dec 06 '16 at 18:57

answered Dec 06 '16 at 18:43

cryo111

4,444
1
15
37

BTW: I did not do the `NA` handling but I guess you can figure that out yourself. ;) – cryo111 Dec 06 '16 at 18:46
I have been experimenting with this, but hitting some issues. On running your loop, I receive: `Error in 1:nrow(data) : argument of length 0` I can run your code as copied directly from your post though. Finally, looking at the final output (and reproducible from my end as well) I see that for some reason the data which is being crammed in to the JSON structure sometimes has quotes around NA values and not for the remaining values. – p0wd3rd Dec 07 '16 at 14:18
ad error) You haven't specified `data` but I was assuming that this is a `data.frame` where each row has to be transformed into a `JSON` object. If this is the case, this error that you are reporting should not show up. ad quotes around `NA`) I don't know how your actual input is formatted, but you might get closer to what you want with `jsonstructure[[mapping[[i]]]]<-ifelse(is.na(data_row[,cn[i]]),"",as.character(data_row[,cn[i]]))` – cryo111 Dec 07 '16 at 14:43
Original `data` (factors, ints, numbers): `str(data) data.frame:6 obs. of 13 variables` Original `jsonstructure` `str(jsonstructure) List of 2 $ default:List of 11 $ items :'data.frame': 1 obs. of 2 variables: ..$ id : chr "" ..$ dimensions:'data.frame': 1 obs. of 25 variables:` When running `mapping=strsplit(data,"\\.")`, would encounter "_Error in strsplit(data, "\\.") : non-character argument_". I was adding `data<-as.character(data) statement`, to proceed. Solving NAs after `colnames(data) = cn` with`shortdata[is.na(shortdata)] <- " "` – p0wd3rd Dec 07 '16 at 19:18
Why are you applying `strsplit` to `data`? In my answer I was applying it to the `colnames` of `data` (i.e., `cn`). So I guess you should use `strsplit(colnames(data),"\\.")`. – cryo111 Dec 07 '16 at 20:16
Why the hell was I doing that?! This worked like a champ and I learned a lot along the way. Thank you very much! +many Internets to you – p0wd3rd Dec 07 '16 at 20:50

How to merge data in to a pre-existing JSON structure in R?

2 Answers2