Handle keys that have mixed values, array and other types - src_sqlite()

[rfhb]

Some JSON datasets have mixed types of values of the same key in different records. Consider for example these two records: [{"a_key": "a text value"},{"a_key": ["text one", "text two"]}]. This scenario was not handled well by docdb_query.src_sqlite so far.

Description

The edits concern how the SQL statement is built and in particular, how SQLite's JSON1's functions are used to extract objects. Depending on the type of the value, the object is wrapped into an json_array call. This was added for mixed-type-values to the existing escaping and extracting code. At this occasion, I noticed that docdb_create.src_sqlite() was not robust for data where the json string does not include an _id. The method now is robust after changing to a simpler list handling.

Example

dbc <- nodbi::src_sqlite(
  key = "myTable")

nodbi::docdb_create(
  src = dbc, 
  key = 'myTable', 
  value = data.frame(
    "_id" = c("01", "02"),
    "json" = c('{"a_key": "a text value"}', '{"a_key": ["text one", "text two"]}'),
    stringsAsFactors = FALSE,
    check.names = FALSE)
    )

nodbi::docdb_get(
  src = dbc, 
  key = 'myTable'
)

nodbi::docdb_query(
  src = dbc, 
  key = 'myTable', 
  query = '{}',
  fields = '{"a_key": 1}')

[sckott]

thanks, having a look

[sckott]

looks good to me