Support for nested database fields

[javierluraschi]

It is increasingly common for databases to support unstructured data, for instance, in Apache Spark or Apache Drill; however, to my knowledge this might not be supported in dbplyr, is it?

A common case is to selected nested fields from within a field, two examples:

• https://stackoverflow.com/questions/35982947/unnesting-nested-json-structures-in-apache-drill
• Full example follows with sparklyr.

writeLines('[{"a":1,"b":{"a":10,"b":100}},{"a":2,"b":{"a":20,"b":200}}]', "test.json")

library(sparklyr)
sc <- spark_connect(master = "local")

nested_tbl <- spark_read_json(sc, "nested", "test.json")

# SQL query is supported
DBI::dbGetQuery(sc, "SELECT b.a FROM nested")

   a
1 10
2 20

# Query in dplyr...
nested_tbl %>% select(b.a)

 Error in .f(.x[[i]], ...) : object 'b.a' not found 

[hadley]

I think we could add support for something like this:

nested_tbl %>% mutate(a = b$a)

If you're interested in this feature, the most helpful think would be to provide a bulleted for each database linking to the docs for nested fields

[hadley]

• Postgres: https://www.postgresql.org/docs/9.6/rowtypes.html — b.a
• BigQuery: https://cloud.google.com/bigquery/docs/nested-repeated — b.a
• Presto: https://prestodb.io/docs/current/functions/map.html — b.a (grep for row)

I think that's enough to suggest that we can translate $ to ..

[roboton]

There are a number of JSON operators in Postgres that also query nested structures: https://www.postgresql.org/docs/12/functions-json.html

Is this something that could be considered for dbplyr?