0

I have trouble reading a CSV file I exported from a mysql database which contains a column with a JSON string. More concretely, I want to get access to all values in the JSON string. I created a simple example to visualize my problem:

This is my CSV file (test.csv):

"id","code","values"
1,"12b222a","{\"first\": 5, \"second\": 5}"

This is how I read it in R:

library(data.table)
library(jsonlite)
test_data<-fread("test.csv")

When I try 

rd <- fromJSON(test_data[,"values"])

I reveive the following error message:

Error: Argument 'txt' must be a JSON string, URL or file.

The problem is that when I run 

test_data[,"values"]

I receive the following content which contains double backslashes as escape characters: 

                              values
1: {\\"first\\": 5, \\"second\\": 5}

How can I avoid having two backslashes that cause the trouble with fromJSON?

Daniel
  • 502
  • 6
  • 17
  • 1
    remove the quotes in `test_data[,"values"]`, so do `fromJSON( test_data[,values] )` – SymbolixAU Feb 07 '19 at 21:11
  • 1
    Then, when you've sorted that, and you want to do it on multiple rows, you need to use `fromJSON` on each row of the data.table `test_data[ , .( json = jsonlite::fromJSON( values ) ) , by = id ]` – SymbolixAU Feb 07 '19 at 21:14
  • fromJSON without the quotes throws a "Error: lexical error: invalid char in json text.". The problem are still the backslashes. – Daniel Feb 07 '19 at 21:46
  • 3
    I'm not getting this error, but maybe because I don't have your .csv file. Can you make your question reproducible so other people can simply copy & paste your data and code? At the moment all I can do is `test_data <- data.table(id = 1, code = "12b221", values = c("{\"first\": 5, \"second\": 5}")`, which doesn't seem to produce an error. – SymbolixAU Feb 07 '19 at 21:49
  • I can't provide such an example, the problem only occurs if you use an external CSV file as the one in my initial post since this adds the second bachslashes which cause the problem with jsonlite – Daniel Feb 08 '19 at 08:12
  • 1
    Then you should write the code to create this external csv using `write.csv()` or similar. – SymbolixAU Feb 08 '19 at 09:20

0 Answers0