json_join(), unjson(), dm_json_tbl() and dm_unjson_tbl()

[krlmlr]

For dataframes, json_join() can be implemented as a combination of nesting an jsonlite::toJSON() . For databases it's the other way round: some databases (e.g. SQL Server) have a syntax to create JSON from a table but don't know how to work with nested tables.

Let's create sub-issues and prioritize.

nest() is to nest_join() as unnest() is to ...? (Is there an inverse of nest_join() ?)

library(tidyverse)
library(dm)

dm_nycflights13() %>%
  # dm_wrap_tbl(airlines) %>%
  # mutate(flights = map_chr(flights, jsonlite::toJSON)) %>%
  dm_wrap_tbl(airlines, wrapper = "json") %>%
  dm_zoom_to(airlines) %>%
#> # Zoomed table: airlines
#> # A tibble:     15 × 3
#>    carrier name                        flights                                  
#>    <chr>   <chr>                       <chr>                                    
#>  1 9E      Endeavor Air Inc.           "[{\"year\":2013,\"month\":1,\"day\":10,…
#>  2 AA      American Airlines Inc.      "[{\"year\":2013,\"month\":1,\"day\":10,…
#>  3 AS      Alaska Airlines Inc.        "[{\"year\":2013,\"month\":1,\"day\":10,…
#>  4 B6      JetBlue Airways             "[{\"year\":2013,\"month\":1,\"day\":10,…
#>  5 DL      Delta Air Lines Inc.        "[{\"year\":2013,\"month\":1,\"day\":10,…
#>  6 EV      ExpressJet Airlines Inc.    "[{\"year\":2013,\"month\":1,\"day\":10,…
#>  7 F9      Frontier Airlines Inc.      "[{\"year\":2013,\"month\":1,\"day\":10,…
#>  8 FL      AirTran Airways Corporation "[{\"year\":2013,\"month\":1,\"day\":10,…
#>  9 HA      Hawaiian Airlines Inc.      "[{\"year\":2013,\"month\":1,\"day\":10,…
#> 10 MQ      Envoy Air                   "[{\"year\":2013,\"month\":1,\"day\":10,…
#> 11 UA      United Air Lines Inc.       "[{\"year\":2013,\"month\":1,\"day\":10,…
#> 12 US      US Airways Inc.             "[{\"year\":2013,\"month\":1,\"day\":10,…
#> 13 VX      Virgin America              "[{\"year\":2013,\"month\":1,\"day\":10,…
#> 14 WN      Southwest Airlines Co.      "[{\"year\":2013,\"month\":1,\"day\":10,…
#> 15 YV      Mesa Airlines Inc.          "[{\"year\":2013,\"month\":1,\"day\":10,…

^{Created on 2022-04-07 by the reprex package (v2.0.1)}

[moodymudskipper]

Is there an inverse of nest_join() ?

No, we have only dm_unnest_tbl().

We can implement unnest_unjoin(), it might return a list of 2 tables :

• "parent" : select id cols and nested col, then unnest
• "{nested_col}" : select all except nested col

Is it useful ?

[krlmlr]

dm_unnest_tbl(), dm_unpack_tbl() and a similar operation for the JSON variants is probably fine for now.

I think, as a rule, if an operation returns more than one table it is most useful as an operation on a dm.