Create multilevel .json file from dataframe-CodePudding

I have a dataframe which I want to convert to a .json file. The .json files has 4 levels which contain information, beautified it should like this:


 {
    "message": {
        "message_reference": "Something",
        "message_receiver": "Something",
        "client_name": "Something",
        "debtor_name": "Something",
        "booking_reference": "B123456",
        "invoice_reference": "I123456",
        "transport_direction": "E"
    },
    "container": {
        "shippingcompany_name": "billy",
        "container_isotype": "40DV"
    },
    "inland": {
        "destination_name": "companyB",
        "loading_discharge": "L"
    },
    "delivery": {
        "terminal_code": "123",
        "terminal_name": "123RTM"
    }
}

I split the original dataframe containing al the information in 4 seperate dataframes and make 4 .json files which I store in a new dataframe

dfTEST <- data.frame("message_reference" = character(1),
                     "message_reciever" = character(1),
                     "client_name" = character(1),
                     "debtor_name" = character(1),
                     "booking_reference" = character(1),
                     "invoice_reference"  = character(1),
                     "transport_direction" = character(1),
                     "shipingcompany_name" = character(1),
                     "container_isotype" = character(1),
                     "destination_name" = character(1),
                     "loading_dishcarge" = character(1),
                     "terminal_code" = character(1),
                     "terminal_name" = character(1))


dfTEST$message_reference <- "Something"
dfTEST$message_reciever <- "Something"
dfTEST$client_name <- "Something"
dfTEST$debtor_name <- "Something"
dfTEST$booking_reference <- "B123456"
dfTEST$invoice_reference <- "I123456"
dfTEST$transport_direction <- "E"
dfTEST$shipingcompany_name <- "billy"
dfTEST$container_isotype <- "40DV"
dfTEST$destination_name <- "Company B"
dfTEST$loading_dishcarge <- "L"
dfTEST$terminal_code <- "123"
dfTEST$terminal_name <- "123RTM"

dfMESSAGE <- dfTEST[c(1,2,3,4,5,6,7)]
dfCONTAINER <- dfTEST[c(8,9)]
dfINLAND <- dfTEST[c(10,11)]
dfDEL <- dfTEST[c(12,13)]

jsMESSAGE <- gsub("\\[|\\]", "", toJSON(dfMESSAGE))
jsCONTAINER <- gsub("\\[|\\]", "", toJSON(dfCONTAINER))
jsINLAND <- gsub("\\[|\\]", "", toJSON(dfINLAND))
jsDEL <- gsub("\\[|\\]", "", toJSON(dfDEL))


dfTOT <- data.frame(message = character(1),
                    container = character(1),
                    inland = character(1),
                    delivery = character(1))

dfTOT$message <- jsMESSAGE
dfTOT$container <- jsCONTAINER
dfTOT$inland <- jsINLAND
dfTOT$delivery <- jsDEL

When I convert the new dataframe to a json file, it looks pretty much the same, but the seperate json files get quotation marks around them which makes the final json file corrupt;

jsTOT <- gsub("\\[|\\]", "", toJSON(dfTOT))
jsTOT <- gsub(pattern = ('\\\\'), replacement = '', x = jsTOT)

the resulting jsTOT looks like the first line, while it should look like the second one;

{"message":"{"message_reference":"MESSAGEREFERENCE123","message_reciever":"VanBerkelLogistics","client_name":"Modality Software solutions","debtor_name":"Modality Software solutions"}","container":"{"booking_reference":"B123456","invoice_reference":"I123456"}","inland":"{"transport_direction":"E","shipingcompany_name":"MAERSK"}","delivery":"{"container_isotype":"40DV","destination_name":"Friesland Campina Nutrifeed"}"}

{"message":{"message_reference":"MESSAGEREFERENCE123","message_reciever":"VanBerkelLogistics","client_name":"Modality Software solutions","debtor_name":"Modality Software solutions"},"container":{"booking_reference":"B123456","invoice_reference":"I123456"},"inland":{"transport_direction":"E","shipingcompany_name":"MAERSK"},"delivery":{"container_isotype":"40DV","destination_name":"Friesland Campina Nutrifeed"}}

So basicly my question is; how to remove the quotation marks within the .json file? Maybe I should build it otherwise, but I have no clue on how to do this. I use the jsonlite package, I have also tried the rjson package but this does not work. Any help in the good direction would be highly appreciated.

CodePudding user response：

Don't early-convert to JSON. I suggest your dfTOT should be a list instead:

newTOT <- list(message = dfMESSAGE,
                    container = dfCONTAINER,
                    inland = dfINLAND,
                    delivery = dfDEL)
toJSON(newTOT)
# {"message":[{"message_reference":"Something","message_reciever":"Something","client_name":"Something","debtor_name":"Something","booking_reference":"B123456","invoice_reference":"I123456","transport_direction":"E"}],"container":[{"shipingcompany_name":"billy","container_isotype":"40DV"}],"inland":[{"destination_name":"Company B","loading_dishcarge":"L"}],"delivery":[{"terminal_code":"123","terminal_name":"123RTM"}]}