Issue 58 in rpostgresql: RPostgreSQL does not have support for JSON data type

[rpost...@googlecode.com]

Status: New
Owner: ----
Labels: Type-Defect Priority-Medium

New issue 58 by saptarsh...@gmail.com: RPostgreSQL does not have support
for JSON data type
http://code.google.com/p/rpostgresql/issues/detail?id=58

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.



--
You received this message because this project is configured to send all
issue notifications to this address.
You may adjust your notification preferences at:
https://code.google.com/hosting/settings

[rpost...@googlecode.com]

Comment #1 on issue 58 by saptarsh...@gmail.com: RPostgreSQL does not have

support for JSON data type
http://code.google.com/p/rpostgresql/issues/detail?id=58

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

[rpost...@googlecode.com]

Comment #2 on issue 58 by iand...@gmail.com: RPostgreSQL does not have

support for JSON data type
http://code.google.com/p/rpostgresql/issues/detail?id=58

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)
)