RPostgreSQL does not have support for JSON data type

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Use Postgresql 9.3
2. Create a table with a json column
3. Read it using RPostgresql

In psql
create table jsontest (id char(32) primary key, data json);

drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname="postgres")
dbSendQuery(con,"select * from jsontest")

What is the expected output? What do you see instead?
NULL,  but i get

In postgresqlExecStatement(conn, statement, ...) :
  RS-DBI driver warning: (unrecognized PostgreSQL field type json (id:114) in column 1)


What version of the product are you using? On what operating system?


Please provide any additional information below.

Original issue reported on code.google.com by [email protected] on 7 Nov 2013 at 7:33

[GoogleCodeExporter]

However, it does return rows but throws the warning message(everything else 
seems to work). Would anything change if support were added? 

Original comment by [email protected] on 7 Nov 2013 at 7:38

[GoogleCodeExporter]

One option would have JSON converted to text. Not sure whether it makes a 
difference having RPostgreSQL do this versus doing it by an explicit cast to 
text in PostgreSQL (may matter more with the new [9.4] jsonb data type.

(PS. I've found the RJSONIO package to work nicely with JSON returned as text. 
The function `word_count` below is a PL/Python function that accepts text and 
returns JSON derived from a Python dictionary.

    library(RPostgreSQL)
    pg <- dbConnect(PostgreSQL())

    # Get count data. Data is JSON converted to text.
    count_raw <- dbGetQuery(pg, "
        SELECT key_id, word_count(some_text)::text AS word_counts
        FROM some_table")

    # Convert JSON-as-text to records where each key becomes a column
    require(RJSONIO)
    count_data <- as.data.frame(do.call(rbind,
                                       lapply(count_raw$word_counts, fromJSON)))

    # Combine converted JSON data with key field
    count_data <- cbind(count_raw$key_id, count_data)
)

Original comment by [email protected] on 17 Jul 2014 at 3:35