interfacing R to PostgreSQL 9.4 JSONB data type

Question

With PostgreSQL 9.4 a new datatype JSONB has become available. According to the performance tests made available on a single node the performance is better than with mongodb (which understandably is not the sweet spot for mongodb).

Is it possible to read JSONB in R, ideally with jsonlite?

how?

Womanize

Answer 1

This is where I got to, but I suspect there are better ways (hence the post):

  dbGetQuery(conn,"SELECT * FROM justjsonb;")
  id                                                                                         doc
1  1 {"name": "fred", "address": {"line1": "52 The Elms", "line2": "Elmstreet", "postcode": "ES1 1ES"}}
Warning message:
In postgresqlExecStatement(conn, statement, ...) :
  RS-DBI driver warning: (unrecognized PostgreSQL field type jsonb (id:3802) in column 1)

(note the warning)

This is the json string I get:

{"name": "fred", "address": {"line1": "52 The Elms", "line2": "Elmstreet", "postcode": "ES1 1ES"}}

To get the result I do:

unlist(fromJSON(dj$doc))
            name    address.line1    address.line2 address.postcode 
          "fred"    "52 The Elms"      "Elmstreet"        "ES1 1ES" 

Are there better ways?

Womanize

where does your dj variable come from ? Does it work if your jsonb input table has several rows ? – Sniffy