constructing json in R with jsonlite - too many brackets

Question

Not a json expert, but I need what I think is referred to as "nested objects" and I am getting instead what I think is referred to as "nested arrays". In other words, some extra brackets. I'm trying to convert a dataframe into json data using jsonlite in R. Reproducible code and results below. Can anyone point me to how to get the data in the proper format (rows as nested objects)?

library(jsonlite)

testdat <- data.frame(locationColumn = c("US", "US"),
                      nameColumn = c("General Motors", "Walmart"), 
                      zipColumn = c(19890, 72712) )


jsl <- jsonlite::toJSON(
  list(
    config = list(
      item1 = list("country",
                   "city"),
      item2 = "true",
      item3 = "false",
      item4 = 3
    ),
    rows = split(testdat, 1:nrow(testdat))
  ), 
  auto_unbox = TRUE,
  pretty = TRUE,
  dataframe = "rows",
  simplifyDataFrame = TRUE
)

jsl

Output:

{
  "config": {
    "item1": [
      "country",
      "city"
    ],
    "item2": "true",
    "item3": "false",
    "item4": 3
  },
  "rows": {
    "1": [
      {
        "locationColumn": "US",
        "nameColumn": "General Motors",
        "zipColumn": 19890
      }
    ],
    "2": [
      {
        "locationColumn": "US",
        "nameColumn": "Walmart",
        "zipColumn": 72712
      }
    ]
  }
}

What I need: (EDIT: I added some more complexity to the json. I need to keep the brackets in 'config', but not have brackets in 'rows'.

{
  "config": {
    "item1": [
      "country",
      "city"
    ],
    "item2": "true",
    "item3": "false",
    "item4": 3
  },
  "rows": {
    "1":
      {
        "locationColumn": "US",
        "nameColumn": "General Motors",
        "zipColumn": 19890
      },
    "2":
      {
        "locationColumn": "US",
        "nameColumn": "Walmart",
        "zipColumn": 72712
      }
  }
}

Dave2e · Accepted Answer · 2019-10-25T19:14:36.637

1

Here is a possible solution:

library(jsonlite)

testdat <- data.frame(locationColumn = c("US", "US"),
                      nameColumn = c("General Motors", "Walmart"), 
                      zipColumn = c(19890, 72712) )

jsl <- jsonlite::toJSON(
  list(
    rows = split(testdat, 1:nrow(testdat))
  ), 
  auto_unbox = TRUE,
  pretty = TRUE,
  dataframe = "columns",  #change from rows (moves brackets from row level to value level)
  simplifyDataFrame = TRUE
)

#removed the backets if desired
#jsl<-gsub("\\[|\\]", "", jsl)

all.equal(testcase, fromJSON(jsl))
testcase<-fromJSON('{
  "rows": {
    "1":{
         "locationColumn": "US",
         "nameColumn": "General Motors",
         "zipColumn": 19890
        },
    "2":{
         "locationColumn": "US",
         "nameColumn": "Walmart",
         "zipColumn": 72712
        }
  }
}')

all.equal(testcase, fromJSON(jsl))
#[1] TRUE

EDIT Here is an approved version that manually edits the list of list in order to obtain the correct format.

#create a list of the data
top<-list(
  config = list(
    item1 = list("country",
                 "city"),
    item2 = "true",
    item3 = "false",
    item4 = 3
  ),
  rows = split(testdat, 1:nrow(testdat))
)

#edit the data frames store as part of rows
#lapply - lapply loops will parse each column in each row to create a new list
rows<-lapply(top$rows, function(x){
  tempdf<-x
  #collist<-lapply(names(tempdf), function(y){print(tempdf[ , y, drop=T])})
  collist<-lapply(names(tempdf), function(y){tempdf[, y, drop=T]})
  names(collist)<-names(tempdf)
  collist
})

#update the list with the list of list
top$rows<-rows

#make the JSON
jsl <- jsonlite::toJSON(
  top, 
  auto_unbox = TRUE,
  pretty = TRUE,
  dataframe = "columns",
  simplifyDataFrame = TRUE
)

edited Oct 25 '19 at 19:14

answered Oct 25 '19 at 13:32

Dave2e

22,192
18
42
50

My simple example was maybe too simple, I will edit my original question. The problem is that there is actually more to this JSON and some brackets that need to be retained. So a solution that strips out all brackets will not work for me, however if I could focus it on the "rows" item then it would work. – bikeactuary Oct 25 '19 at 14:23
@mb158127, if you use `dataframe = "columns"` instead of "rows" does that provide an acceptable solution? It still have extra brackets but at a different level now. – Dave2e Oct 25 '19 at 15:09
this moves the brackets to around the "values" in the key-value pair, like so: ``` { "config": { "item1": [ "country", "city" ], "item2": "true", "item3": "false", "item4": 3 }, "rows": { "1": { "locationColumn": ["US"], "nameColumn": ["General Motors"], "zipColumn": [19890] }, "2": { "locationColumn": ["US"], "nameColumn": ["The Hartford"], "zipColumn": [6105] } } } ``` That also results in a data error though in the API, so I need to get ride of these brackets then. – bikeactuary Oct 25 '19 at 15:38
I'm currently trying to figure out how to remove all patterns occurring AFTER a particular keyword/pattern. In other words, if I could gsub "" for "[" and "]" but only occurrences coming after the pattern "rows:" – bikeactuary Oct 25 '19 at 15:43
In other words, if I can get this to only remove the brackets *after* "ef", kkeeps those preceding "ef", then I've solved the original problem.. gsub("\\[|\\]", "", "ab[ cd] ef [ gh ] ij ", ) – bikeactuary Oct 25 '19 at 16:10
I'm marking your answer accepted (and thanks) because it is essentially the solution I needed (to gsub the json itself). I'm opening a new question to answer how to gsub only after a keyword separately: https://stackoverflow.com/questions/58562449/gsub-replace-only-those-occurrences-following-a-keyword-occurrence – bikeactuary Oct 25 '19 at 16:35
@mb158127, thank you for the vote. Please see my edit above, The trick is to manually convert each dataframe row into a list. Good luck. – Dave2e Oct 25 '19 at 17:30

constructing json in R with jsonlite - too many brackets

1 Answers1