0

as the title states I am trying to update a jsonb field from inside R. I have several changes to apply. The original dataset is created by a third party application but needs to be corrected for several rows.

The following statement is working fine as a database statement:

UPDATE histories set meta=jsonb_set(meta,'{product}','"55-AB"') WHERE id = 17983;

Now, I need to update the "product" field for several different ids. Assume the following dataframe as an example:

df<-data.frame(product=c("55-AB","567-C","UTG-98"),
               id=c(17983,54388,20000))

Usually I would use sql_glue from the glue package but I run out of quotes generating the above queries dynamically.

sql_glue("UPDATE histories set meta=jsonb_set(meta,'{product}','"{`df$product`}"') WHERE id = {`df$id`};")

Error: unexpected '{' in "sql_glue("UPDATE histories set meta=jsonb_set(meta,'{pesticide}','"{"

I am running into problems with the quotation. Any idea how to get around this?

Zoe
  • 27,060
  • 21
  • 118
  • 148
Johannes42
  • 29
  • 4
  • 1
    I'm unfamiliar with R, but postgres supports using double-dollars as string delimiters. See https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING – AdamKG Apr 23 '20 at 20:11
  • Thanks, that was a perfect hint. I had to do some adaptations to create dynamic SQL-Statements but the double-dollars where the solution. – Johannes42 Apr 24 '20 at 10:09

0 Answers0