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