Support SQL translations

[mgirlich]

Add dbplyr like translations.

1. Make it work for tbl_lazy

• json_unnest_longer() and json_unnest_wider() have to be made generics and need to add methods for lazy tables
• json_extract() etc. are more difficult. Options:
  • Wait for dbplyr to implement Way to register user supplied translations tidyverse/dbplyr#309: Way to register user supplied translations. This would allow

lazy_frame(json = 1, con = simulate_postgres()) %>% 
  mutate(x = json_extract(x, "a", 1))

• add json_extract_sql(..., con)

con <- simulate_postgres()
lazy_frame(json = 1, con = con) %>% 
  mutate(x = !!json_extract_sql(x, "a", 1, con = con))

• Add directly in dbplyr, currently rather unrealistic.

• This branch https://github.com/mgirlich/jsontools/tree/dbplyr-translations currently uses a rather hacky way to register custom dbplyr translations.

2. Some arguments are not so nice to translate or restricted, in json_flatten() for example ptype is rather restricted and allow_scalars and wrap_scalars require quite a bit of extra SQL.

3. In PostgreSQL there is no nice and easy way to translate JSON true and false to integer.

Example for a quick and dirty Postgres translation:

library(dplyr, warn.conflicts = FALSE)
library(dbplyr)
devtools::load_all("~/GitHub/jsontools/")

lazy_frame(json = 1, con = simulate_postgres()) %>% 
  mutate(x = json_extract(x, "a", 1))
#> <SQL>
#> SELECT `json`, CAST((`x` #>> 'a') AS NUMERIC) AS `x`
#> FROM `df`
lazy_frame(json = 1, con = simulate_postgres()) %>% 
  mutate(x = json_extract(x, "a", !!new_json_array(), wrap_scalars = TRUE))
#> <SQL>
#> SELECT `json`, CASE WHEN (type_of(`x` #>> 'a') IN ('object', 'array')) THEN (`x` #>> 'a') WHEN NOT(type_of(`x` #>> 'a') IN ('object', 'array')) THEN (json_build_array(`x` #>> 'a')) END AS `x`
#> FROM `df`

lazy_frame(json = 1, con = simulate_postgres()) %>% 
  mutate(x = json_flatten(x, "a", 1))
#> <SQL>
#> SELECT `json`, json_array_elements_text(`x`) AS `x`
#> FROM `df`
lazy_frame(json = 1, con = simulate_postgres()) %>% 
  mutate(x = json_flatten(x, 1, 1))
#> <SQL>
#> SELECT `json`, CAST((json_array_elements_text(`x`)) AS NUMERIC) AS `x`
#> FROM `df`

^{Created on 2021-03-26 by the reprex package (v1.0.0)}

[Soccerama]

I'm not sure I understand your question here. Can you please detail the issue?

[mgirlich]

Sorry, this was just a quick reminder for myself that it might be interesting to offer dbplyr like translations. I updated the issue to make it a bit more clear. Also I am not entirely sure how interesting/relevant this is, in particular as the function design is still a bit experimental.

PS: Thanks for your offer to help! As the package is currently still rather experimental I am happy about any feedback on it!

[Soccerama]

Reading through the code I'm feeling very out of my depth. Will try to learn to the point where I can at least ask intelligent questions.
Have never used dbplyr so will read up on that as well.