Converting JSON file to SQLite file

Question

I am writing a function to convert JSON file to SQLite file by representing it in a table, but I am having a little trouble with indexing objects without keys. For example, I am converting this JSON file but not sure how to index it correctly.

[ [ "A1", "A2", "A3" ],
  [ 1.1, 2.2, 3.3],
  [ 4.4, 5.5, 6.6] ]

So far this is all I have

library(rjson)
library(sqldf)
library(RSQLite)

convert = function (infile,outfile,name = "test"){
  data = fromJSON(file = infile)
  cols = length(data$header) #data[1]
  rows = length(data$data) #data[2]
  temp = matrix(NA, rows, cols) #create an empty dataframe and populate it

  for (i in 1:rows){
    for (j in 1:cols){
      temp[i,j] = ((data$data)[[i]])[[j]]
    }
  }

  #Save data into the dataframe
  temp = as.data.frame(temp)
  colnames(temp) = data$header

  #Save to sqlite
  db = dbConnect(SQLite(), dbname = outfile)
  dbWriteTable(conn = db, name = name, value = result, row.names = FALSE, overwrite = TRUE)

}

infile is the JSON file containing a list. The first item of the list is a list of strings specifying column names. Subsequent items of the list are lists of numerical values. outfile is an SQLite database with a table representing the data in infile with 3 columns A1 A2 A3.

@krlmlr I want to convert that JSON file into a table representing that data — warmsoda, Apr 15 '20 at 02:58
The literal answer to "_how to index it correctly?_" is: not all tables require indexes. That data could be JSON _values_, but where's the _keys_? — DinoCoderSaurus, Apr 15 '20 at 09:35

score 2 · Answer 1 · answered Apr 15 '20 at 17:23

Converting from JSON to a frame is a bit more straight-forward than you have:

infile <- '
[ [ "A1", "A2", "A3" ],
  [ 1.1, 2.2, 3.3],
  [ 4.4, 5.5, 6.6] ]'

convert <- function(infile, outfile, name = "test") {
  dat <- jsonlite::fromJSON(infile)
  colnames(dat) <- dat[1,]
  dat <- as.data.frame(dat[-1,], stringsAsFactors = FALSE)
  con <- DBI::dbConnect(RSQLite::SQLite(), outfile)
  on.exit({ DBI::dbDisconnect(con) }, add = TRUE)
  DBI::dbWriteTable(con, name, dat, row.names = FALSE, overwrite = TRUE)
  return(dat)
}
convert(infile, "~/Downloads/quux.sqlite")
#    A1  A2  A3
# 1 1.1 2.2 3.3
# 2 4.4 5.5 6.6

con <- DBI::dbConnect(RSQLite::SQLite(), "~/Downloads/quux.sqlite")
str(DBI::dbGetQuery(con, "select * from test"))
# 'data.frame': 2 obs. of  3 variables:
#  $ A1: chr  "1.1" "4.4"
#  $ A2: chr  "2.2" "5.5"
#  $ A3: chr  "3.3" "6.6"

(I did str to demonstrate that it inserted it literally; if you need to convert to numeric, that's another easy step.)

I used jsonlite for this, I believe (without verifying) that it will work similarly with rjson.

Converting JSON file to SQLite file

1 Answers1