Feature addition: handle arrays and nested content with RSQLite backend

[rfhb]

The purpose is to extend and essentially complete handling json data with the RSQLite backend.

Description

So far docdb_query.src_sqlite would not return values from all records those fields that are arrays, but only selected records such as field[1].subfield. This was because json_extract() was used which requires a full json path (https://www.sqlite.org/json1.html#jex). Handling this situation was not included in my initial implementation of nodbi functions for the RSQLite backend. The PR intends to cover this important use case.

Example

The contacts are a json string that is part of the nodbi package and its friends field has subitems with keys name and id. With this PR, friends in the returned data.frame is column that is a list with one data.frame per row (or list item).

con <- src_sqlite()

docdb_create(con, "contacts", value = data.frame(contacts, stringsAsFactors = FALSE)))

docdb_query(con, "contacts", fields = '{"friends": 1}', query = '{}')
#                                                     friends                      _id
# 1 0, 1, 2, Wooten Goodwin, Brandie Woodward, Angelique Britt 5cd67853f841025e65ce0ce2
# 2                                0, 1, Yang Yates, Lacy Chen 5cd678531b423d5f04cfb0a1
# 3      0, 1, 2, Coleen Dunn, Doris Phillips, Concetta Turner 5cd6785335b63cb19dfa8347
# 4      0, 1, 2, Baird Keller, Francesca Reese, Dona Bartlett 5cd6785325ce3a94dfc54096
# 5                                               0, Pace Bell 5cd678530df22d3625ed8375

str(docdb_query(con, "contacts", fields = '{"friends": 1}', query = '{}'))
# 'data.frame': 5 obs. of  2 variables:
#  $ friends:List of 5
#   ..$ :'data.frame':  3 obs. of  2 variables:
#   .. ..$ id  : int  0 1 2
#   .. ..$ name: chr  "Wooten Goodwin" "Brandie Woodward" "Angelique Britt"
#   ..$ :'data.frame':  2 obs. of  2 variables:

[sckott]

thanks, I'll have a look.

(p.s. after this PR is merged, are you interested in having commit access to the repo? I can add you and you can still do contributions through PR's, but also direct commits - up to you - you can be trusted at this point 😸 )

[rfhb]

Many thanks @sckott for suggestion, am honoured! Happy to help with nodbi.

As regards this PR, I've been considering my aim to handle nested fields and am not fully satisfied with my solution so far. Thus am closing the PR so that I can come back with more simple and complete proposal. Sorry if this caused any work on your side.

[sckott]

No worries, sounds good.

Invitation to the repo sent.