Inserting json objects in postgres table

dplyr
postgres
json
#1

Hi all,

Anyone got any tips on how to handle JSON in postgres using R?

It is hard to create a reproducible example but this outlines my issue

Example

library(dplyr)
library(dbplyr)
library(DBI)
library(jsonlite)

proposal_table

proposal_id - character
created_by - character
proposal - jsonb

Data

my_id<-1
my_name<-'john'
my_proposal<-tibble(a="test",b="test_b")%>%toJSON()

Works (simple case, no json)

build_sql("insert into proposal_table (proposal_id,created_by)
values (",my_id,",",my_name,")")

Doesn't work

build_sql("insert into proposal_table (proposal_id,created_by)
values (",my_id,",",my_name,",",my_proposal,")")
due to an error
Error in UseMethod("escape") :
no applicable method for 'escape' applied to an object of class "json"

Any tips greatly appreciated

Thanks Iain

#2

Try adding as.character to the end of your toJSON pipe:

my_proposal <- tibble(a = "foo", b = "bar") %>%
  jsonlite::toJSON() %>%
  as.character()

Two other notes:

  1. Your SQL statement is also missing the proposal colname name in the INSERT list of columns.
  2. This seems like a good use of PostgreSQL parameterization, in which case you'd write something like:
dbSendStatement(conn, "INSERT INTO proposal_table (prop_id, created_by, prop) VALUES ($1, $2, $3)", list(my_id, my_name, my_prop)
#3

Thanks!

The parameterization approach solved the problem and there was no need for the as.character()

I was getting confused by the two different representations of a json object
{"a": "test", "b": "test_b"} - in the postgres docs
[{"a": "foo1", "b": "bar1"}] - output by jsonlite

It seems that both of these can be stored in a postgres jsonb column and be retrieved.

I had gone down a rabbit hole of creating a json object using the json_object which wasn't working because special characters weren't always being escaped properly

 my_id<-1
 my_name<-'john'
 my_proposal<-tibble(a="test",b="test_b",c="Super long complictated")


a<-gather(my_proposal)%>%
    mutate(z=paste0("{",key,",",value,"}"))%>%
    pull(z)%>%paste0(collapse=",")
    variable_for_json<-paste0("json_object('{",a,"}')")

sql<-"insert into proposals (proposal_id,created_by,proposal) values ("
my_name<-paste0("'",my_name,"'")
after_sql<-")"

final_sql<-paste0(sql,my_id,",",my_name,",",variable_for_json,after_sql)

dbSendQuery(x$con,final_sql)
#4

Actually there is a difference between these two representations. The postgres form is needed to use the postgres json functions

A slight modification of your code to use the json_object command fixes the issue:

a<-gather(my_proposal)
a$value[a$value==""]<-"NA"  
a<-a%>%
mutate(z=paste0("{",key,",",value,"}"))%>%
pull(z)%>%paste0(collapse=",")

variable_for_json<-paste0("{",a,"}")
rs<-dbSendStatement(my_con, "INSERT INTO proposals (proposal_id,created_by,proposal) VALUES ($1, $2, json_object($3))", list(my_id, my_name, variable_for_json))

Thanks again!