Register custom types/adapters with odbc/dbplyr



This discussion is related to odbc issue #202 which notes that binary objects are truncated to 255 characters.

The reprex in the example shows that JSON objects are converted to VARCHAR(255). A solution is to cast it to VARCHAR or TEXT prior to collecting the result. You can do this using dplyr with mutate(x = as.character(json_column)). Unfortunately this means you have to remember to cast your JSON to text every time or risk truncating it.

JSON is not (yet) part of the ANSI SQL standard and is implemented differently (or not at all) across different databases.

I'm wondering if it is possible to register custom type adapters (similar to psycopg2 in Python).

Users could write their own type adapter as needed. As simple adapter might cast all JSON columns to text and a more complicated one might pass the text to jsonlite::fromJSON.


It occurred to me this is done in the sf package to handle spatial types in PostGIS.

I'm not sure how much work it would be to generalize it.