How to write this json object in Postgresql with R

Question

I have this table

CREATE TABLE example (
ID serial NOT NULL PRIMARY KEY,
    name varchar(1024),
    coords json
);

And I want to write this json in this table:

[
    {
            "name": "first",
        "coords":{
            "lat": 3.14,
            "lon": 2.5
        }
    }
]

I am trying with jsonlite and Rpostgresql, but error

It [has been suggested](https://github.com/r-dbi/odbc/issues/202#issuecomment-409972935) that the `odbc` package does not support JSON data types, so it would be converted to `varchar`. With that, it should be as simple as inserting the `character` representation of that object post-jsonification. Is your question about how to post structured (non-char) data into a JSON field, or is it how to insert data in general? — r2evans, Jul 22 '19 at 16:41
stby, I was looking at the wrong package ... it appears from [tomoakin/RPostgreSQL#58](https://github.com/tomoakin/RPostgreSQL/issues/58) (opened in 2013) states a similar limitation and workaround. Though I did not do an exhaustive search, I didn't find "json" anywhere else in the repo, suggesting the issue is still relevant and unresolved. — r2evans, Jul 22 '19 at 16:43

score 1 · Accepted Answer · answered Jul 22 '19 at 20:36

I have and application where I have JSON data and uploading it to postgres as it. In that case, I am converting JSON to character and writing it to database.

upload =  data.frame(name = name, coords = c(JSONCoords))
dbWriteTable(con, c("table"), value=upload, append=TRUE, row.names=FALSE)

score 1 · Answer 2 · answered Nov 20 '20 at 08:59

I used a small workaround. I just write the table to database as usual. At last, I cast the datatype of the json column from text to jsonb or json. In the following is an example.

library(jsonlite)
library(DBI)

# generate the database connection.
postgres_conn <- dbConnect(RPostgreSQL::PostgreSQL(), 
                           dbname=db, 
                           host=host_db, 
                           port=db_port, 
                           user=db_user, 
                           password=db_password)

# generate the test data.frame.
df <- data.frame(id = 1, name = "username")
df$coords <- toJSON(list(name = "first",
                         coords = list(lat= "3.14",
                                       lon = "2.5")),
                    auto_unbox = TRUE)

# write the test dataframe to database
dbWriteTable(postgres_conn, "tbl", df, row.names = FALSE)
# change the format of the column from text to jsonb. 
dbSendQuery(postgres_conn, "ALTER TABLE tbl ALTER COLUMN coords TYPE jsonb using coords::jsonb")

In the database it looks like this. Hopefully this is what you want.

How to write this json object in Postgresql with R

2 Answers2