JSON type not supported

[artemklevtsov]

Hi,

I can't create table with JSON data type. Code to reproduce:

library(DBI)
library(MonetDBLite)

con <- dbConnect(MonetDBLite(), "/tmp/db")
dbExecute(con, 'CREATE TABLE json_data (id BIGINT, data JSON)')
dbDisconnect(con, shutdown=TRUE)

With this code I got the following error message:

Error in .local(conn, statement, ...) :
Unable to execute statement 'CREATE TABLE json_data (id BIGINT, data JSON)'.
Server says 'type (json) unknown in: "create table json_data (id bigint, data json)" ' [#22000].

dbGetInfo(con) output:

$gdk_dbpath
[1] "/tmp/db"

$gdk_debug
[1] "0"

$gdk_vmtrim
[1] "no"

$monet_prompt
[1] ">"

$monet_daemon
[1] "no"

$mapi_port
[1] "50000"

$mapi_open
[1] "false"

$mapi_autosense
[1] "false"

$sql_optimizer
[1] "default_pipe"

$sql_debug
[1] "0"

$gdk_dbname
[1] "db"

$gdk_vm_maxsize
[1] "4398046511104"

$gdk_mem_maxsize
[1] "54965874176"

$gdk_mmap_minsize_persistent
[1] "262144"

$gdk_mmap_minsize_transient
[1] "3435367136"

$gdk_mmap_pagesize
[1] "65536"

$monet_pid
[1] "19567"

$monet_mod_path
[1] ""

$max_clients
[1] "256"

$mapi_disable
[1] "true"

$recovery
[1] "finished"

$name
[1] "MonetDBConnection"

$db.version
[1] '0.4.1'

$dbname
[1] "db"

$username
[1] NA

$host
[1] NA

$port
[1] NA

sessioninfo::session_info() output:

─ Session info ──────────────────────────────────────────────────────────────────────────────────────────────────────
 setting  value                       
 version  R version 3.4.1 (2017-06-30)
 os       Arch Linux                  
 system   x86_64, linux-gnu           
 ui       RStudio                     
 language ru:en_US                    
 collate  C                           
 tz       Asia/Novosibirsk            
 date     2017-08-23                  

─ Packages ──────────────────────────────────────────────────────────────────────────────────────────────────────────
 package     * version date       source        
 assertthat    0.2.0   2017-04-11 CRAN (R 3.4.0)
 bindr         0.1     2016-11-13 CRAN (R 3.4.0)
 bindrcpp      0.2     2017-06-17 CRAN (R 3.4.0)
 clisymbols    1.2.0   2017-05-21 CRAN (R 3.4.1)
 codetools     0.2-15  2016-10-05 CRAN (R 3.4.1)
 DBI         * 0.7     2017-06-18 CRAN (R 3.4.0)
 digest        0.6.12  2017-01-27 CRAN (R 3.4.0)
 dplyr         0.7.2   2017-07-20 CRAN (R 3.4.1)
 glue          1.1.1   2017-06-21 CRAN (R 3.4.1)
 magrittr      1.5     2014-11-22 CRAN (R 3.4.0)
 MonetDBLite * 0.4.1   2017-08-02 CRAN (R 3.4.1)
 pkgconfig     2.0.1   2017-03-21 CRAN (R 3.4.0)
 R6            2.2.2   2017-06-17 CRAN (R 3.4.0)
 Rcpp          0.12.12 2017-07-15 CRAN (R 3.4.1)
 rlang         0.1.2   2017-08-09 CRAN (R 3.4.1)
 sessioninfo   1.0.0   2017-06-21 CRAN (R 3.4.1)
 tibble        1.3.4   2017-08-22 CRAN (R 3.4.1)
 withr         2.0.0   2017-07-28 CRAN (R 3.4.1)
 yaml          2.1.14  2016-11-12 CRAN (R 3.4.0)

[hannes]

JSON support is one of the things we dropped when creating MonetDBLite. We could add it back. What would you like to use it for?

[artemklevtsov]

JSON support is one of the things we dropped when creating MonetDBLite.

I did not find any mention of this.

What would you like to use it for?

I have a huge amount of JSON data with different structure saved as string. My task to extract some fields from this data. Load and parse this data each time is not a good idea.

Although, I can move this data on the standalone MonetDB server.