Introduction to tidyjson

2020-05-31

JSON (JavaScript Object Notation) is a lightweight and flexible data format that is easy for humans to read and for machines to parse. JSON has become a common format used in:

Tidyjson provides a grammar for turning complex JSON data into tidy data frames that are easy to work with in the tidyverse.

Why use tidyjson?

Several libraries exist for working with JSON data in R, such as rjson, rjsonio and jsonlite. These libraries transform JSON data automatically into nested R lists or complex data frames. However, working with these complex objects can be difficult.

The tidyjson package takes a different approach to structuring JSON data into tidy data frames. Similar to tidyr, tidyjson builds a grammar for manipulating JSON into a tidy table structure.

Tidyjson is based on the following principles:

A simple example

A simple example of how tidyjson works is as follows:

library(dplyr)

# Define a simple people JSON collection
people <- c('{"age": 32, "name": {"first": "Bob",   "last": "Smith"}}',
            '{"age": 54, "name": {"first": "Susan", "last": "Doe"}}',
            '{"age": 18, "name": {"first": "Ann",   "last": "Jones"}}')

# Tidy the JSON data
people %>% spread_all
#> # A tbl_json: 3 x 5 tibble with a "JSON" attribute
#>   ..JSON                  document.id   age name.first name.last
#>   <chr>                         <int> <dbl> <chr>      <chr>    
#> 1 "{\"age\":32,\"name..."           1    32 Bob        Smith    
#> 2 "{\"age\":54,\"name..."           2    54 Susan      Doe      
#> 3 "{\"age\":18,\"name..."           3    18 Ann        Jones

This produces a tbl_json object, where each row corresponds to an element of the people vector (a “document” in tidyjson). The JSON attribute of the tbl_json object is shown first, then the columns of the tibble are shown - a document.id indicating which document the row originated in, and then the age and name columns that spread_all created.

A more complex example

The tidyjson package really shines in a more complex example. Consider the worldbank data included in the tidyjson package.

worldbank %>% str
#>  chr [1:500] "{\"_id\":{\"$oid\":\"52b213b38594d8a2be17c780\"},\"boardapprovaldate\":\"2013-11-12T00:00:00Z\",\"closingdate\""| __truncated__ ...

It is a 500 length character vector of projects funded by the world bank. We can quickly expand all simple columns using spread_all

worldbank %>% spread_all
#> # A tbl_json: 500 x 9 tibble with a "JSON" attribute
#>   ..JSON document.id boardapprovalda… closingdate countryshortname project_name
#>   <chr>        <int> <chr>            <chr>       <chr>            <chr>       
#> 1 "{\"_…           1 2013-11-12T00:0… 2018-07-07… Ethiopia         Ethiopia Ge…
#> 2 "{\"_…           2 2013-11-04T00:0… <NA>        Tunisia          TN: DTF Soc…
#> 3 "{\"_…           3 2013-11-01T00:0… <NA>        Tuvalu           Tuvalu Avia…
#> 4 "{\"_…           4 2013-10-31T00:0… <NA>        Yemen, Republic… Gov't and C…
#> # … with 496 more rows, and 3 more variables: regionname <chr>, totalamt <dbl>,
#> #   `_id.$oid` <chr>

And we can limit the coluns produced by calling dplyr::select after

worldbank %>% spread_all %>% select(regionname, totalamt)
#> # A tbl_json: 500 x 3 tibble with a "JSON" attribute
#>   ..JSON                   regionname                    totalamt
#>   <chr>                    <chr>                            <dbl>
#> 1 "{\"_id\":{\"$oid\":..." Africa                       130000000
#> 2 "{\"_id\":{\"$oid\":..." Middle East and North Africa         0
#> 3 "{\"_id\":{\"$oid\":..." East Asia and Pacific          6060000
#> 4 "{\"_id\":{\"$oid\":..." Middle East and North Africa         0
#> # … with 496 more rows

But worldbank also contains arrays, which cannot be naively spread into new columns. We can use gather_object to gather all name-value paris by name, and then json_types to identify the type of JSON stored under each value, and dplyr::count to aggregate across documents:

worldbank %>% gather_object %>% json_types %>% count(name, type)
#> # A tibble: 8 x 3
#>   name                type       n
#>   <chr>               <fct>  <int>
#> 1 _id                 object   500
#> 2 boardapprovaldate   string   500
#> 3 closingdate         string   370
#> 4 countryshortname    string   500
#> 5 majorsector_percent array    500
#> 6 project_name        string   500
#> 7 regionname          string   500
#> 8 totalamt            number   500

It appears that majorsector_percent is an array, and so we can use enter_object to enter into it:

worldbank %>% enter_object(majorsector_percent)
#> # A tbl_json: 500 x 2 tibble with a "JSON" attribute
#>   ..JSON                  document.id
#>   <chr>                         <int>
#> 1 "[{\"Name\":\"Educa..."           1
#> 2 "[{\"Name\":\"Publi..."           2
#> 3 "[{\"Name\":\"Trans..."           3
#> 4 "[{\"Name\":\"Healt..."           4
#> # … with 496 more rows

and gather_array to gather it by index

worldbank %>% enter_object(majorsector_percent) %>% gather_array
#> # A tbl_json: 1,405 x 3 tibble with a "JSON" attribute
#>   ..JSON                  document.id array.index
#>   <chr>                         <int>       <int>
#> 1 "{\"Name\":\"Educat..."           1           1
#> 2 "{\"Name\":\"Educat..."           1           2
#> 3 "{\"Name\":\"Public..."           1           3
#> 4 "{\"Name\":\"Educat..."           1           4
#> # … with 1,401 more rows

We can then spread_all again to capture the name-value pairs stored in each object

worldbank %>% 
  enter_object(majorsector_percent) %>% gather_array %>% spread_all
#> # A tbl_json: 1,405 x 5 tibble with a "JSON" attribute
#>   ..JSON             document.id array.index Name                        Percent
#>   <chr>                    <int>       <int> <chr>                         <dbl>
#> 1 "{\"Name\":\"Educ…           1           1 Education                        46
#> 2 "{\"Name\":\"Educ…           1           2 Education                        26
#> 3 "{\"Name\":\"Publ…           1           3 Public Administration, Law…      16
#> 4 "{\"Name\":\"Educ…           1           4 Education                        12
#> # … with 1,401 more rows

By combining with our initial top-level spread_all, we can aggregate funding dollars by sector by region:

worldbank %>%
  spread_all %>% select(region = regionname, funding = totalamt) %>%
  enter_object(majorsector_percent) %>% gather_array %>% 
  spread_all %>% rename(sector = Name, percent = Percent) %>%
  group_by(region, sector) %>%
  summarize(funding = sum(funding * percent))
#> `summarise()` regrouping output by 'region' (override with `.groups` argument)
#> # A tibble: 63 x 3
#> # Groups:   region [7]
#>   region sector                                 funding
#>   <chr>  <chr>                                    <dbl>
#> 1 Africa Agriculture, fishing, and forestry 96648500000
#> 2 Africa Education                          83370000000
#> 3 Africa Energy and mining                  86709960000
#> 4 Africa Finance                            30761000000
#> # … with 59 more rows

Tidyjson functions

Tidyjson provides the following core functions:

Function Use Row Effect Column Effect JSON Effect
spread_all Spread all object values add many columns
spread_values Spread specific columns specific columns
gather_array Gather a JSON array Duplicates rows index column enter array
gather_object Gather a JSON object Duplicates rows name column enter object
append_values_X Append all values of a type column of type X
enter_object Enter into an object by name Keeps rows with name enter object
json_types Identify JSON type type column
json_lengths Identify JSON length length column

These functions can be composed into pipelines using the %>% operator, as each takes a tbl_json object and returns a tbl_json object.

Common patterns

The following are common patterns for manipulating JSON data with tidyjson.

Spread selected

Spread all object values and select a subset to continue working with.

spread_all %>% select

worldbank %>% spread_all %>% select(regionname, totalamt)
#> # A tbl_json: 500 x 3 tibble with a "JSON" attribute
#>   ..JSON                   regionname                    totalamt
#>   <chr>                    <chr>                            <dbl>
#> 1 "{\"_id\":{\"$oid\":..." Africa                       130000000
#> 2 "{\"_id\":{\"$oid\":..." Middle East and North Africa         0
#> 3 "{\"_id\":{\"$oid\":..." East Asia and Pacific          6060000
#> 4 "{\"_id\":{\"$oid\":..." Middle East and North Africa         0
#> # … with 496 more rows

Object structure

Identify the names under an object, and their type and frequency.

gather_object %>% json_types %>% count(name, type)

worldbank %>% gather_object %>% json_types %>% count(name, type)
#> # A tibble: 8 x 3
#>   name              type       n
#>   <chr>             <fct>  <int>
#> 1 _id               object   500
#> 2 boardapprovaldate string   500
#> 3 closingdate       string   370
#> 4 countryshortname  string   500
#> # … with 4 more rows

Gather nested array

Enter into an array nested under an object, and gather it

enter_object %>% gather_array

worldbank %>% enter_object(majorsector_percent) %>% gather_array
#> # A tbl_json: 1,405 x 3 tibble with a "JSON" attribute
#>   ..JSON                  document.id array.index
#>   <chr>                         <int>       <int>
#> 1 "{\"Name\":\"Educat..."           1           1
#> 2 "{\"Name\":\"Educat..."           1           2
#> 3 "{\"Name\":\"Public..."           1           3
#> 4 "{\"Name\":\"Educat..."           1           4
#> # … with 1,401 more rows

Filter for a specific type

Filter to just objects / arrays and then gather them

filter(is_json_X(.)) %>% gather_X

companies[1] %>% gather_object %>% 
  filter(is_json_array(.)) %>% gather_array
#> # A tbl_json: 8 x 4 tibble with a "JSON" attribute
#>   ..JSON                  document.id name           array.index
#>   <chr>                         <int> <chr>                <int>
#> 1 "{\"is_past\":fals..."            1 relationships            1
#> 2 "{\"id\":6252,\"rou..."           1 funding_rounds           1
#> 3 "{\"id\":15229,\"ro..."           1 funding_rounds           2
#> 4 "{\"id\":22565,\"ro..."           1 funding_rounds           3
#> # … with 4 more rows
companies[1] %>% gather_object %>% 
  filter(is_json_object(.)) %>% gather_object
#> Warning in gather_object(.): name column name already exists, changing to name.2
#> # A tbl_json: 3 x 4 tibble with a "JSON" attribute
#>   ..JSON                document.id name  name.2         
#>   <chr>                       <int> <chr> <chr>          
#> 1 "\"52cdef7e4bab8b..."           1 _id   $oid           
#> 2 "[[[150,22],\"ass..."           1 image available_sizes
#> 3 "null"                          1 image attribution

Gather and append

Useful when data is stored in object names as well as values

gather_object %>% append_values_X

json <- '{"2015": 5, "2016": 10}'
json %>% gather_object("year") %>% append_values_number("count")
#> # A tbl_json: 2 x 4 tibble with a "JSON" attribute
#>   ..JSON document.id year  count
#>   <chr>        <int> <chr> <dbl>
#> 1 5                1 2015      5
#> 2 10               1 2016     10

Getting started with JSON data

The first step in using tidyjson is to get your data into a tbl_json object. All tidyjson functions automatically coerce objects into tbl_json if they are not already, so you may be able to just start manipulating your data directly.

But if not, you can use as.tbl_json directly. Here are examples for common scenarios:

Character vector

The simplest case is when your JSON data is already in R as a character vector, like the worldbank data:

worldbank %>% as.tbl_json
#> # A tbl_json: 500 x 2 tibble with a "JSON" attribute
#>   ..JSON                   document.id
#>   <chr>                          <int>
#> 1 "{\"_id\":{\"$oid\":..."           1
#> 2 "{\"_id\":{\"$oid\":..."           2
#> 3 "{\"_id\":{\"$oid\":..."           3
#> 4 "{\"_id\":{\"$oid\":..."           4
#> # … with 496 more rows

If this generates errors, then likely your JSON data is malformed:

bad_json <- '{"key": "value"'
bad_json %>% as.tbl_json
#> Error: parse error: premature EOF
#>                                        {"key": "value"
#>                      (right here) ------^

tidyjson uses jsonlite::fromJSON to parse the JSON, and so will print out a useful error message.

Single array

Many APIs will return multiple documents in a single array, like the issues data from github.

issues %>% as.tbl_json
#> # A tbl_json: 1 x 2 tibble with a "JSON" attribute
#>   ..JSON                  document.id
#>   <chr>                         <int>
#> 1 "[{\"url\":\"https:..."           1

A single call to gather_array makes this data look like the worldbank data:

issues %>% as.tbl_json %>% gather_array
#> # A tbl_json: 30 x 3 tibble with a "JSON" attribute
#>   ..JSON                  document.id array.index
#>   <chr>                         <int>       <int>
#> 1 "{\"url\":\"https:/..."           1           1
#> 2 "{\"url\":\"https:/..."           1           2
#> 3 "{\"url\":\"https:/..."           1           3
#> 4 "{\"url\":\"https:/..."           1           4
#> # … with 26 more rows

List

If your JSON is a list of character strings, you can use `purrr::flatten_chr’ to flatten it into a character vector and then proceed as usual:

library(purrr)
list('1', '2') %>% flatten_chr %>% as.tbl_json
#> # A tbl_json: 2 x 2 tibble with a "JSON" attribute
#>   ..JSON document.id
#>   <chr>        <int>
#> 1 1                1
#> 2 2                2

Data frame

If you extracted JSON from a table in a database into a data frame, then likely you already have other columns in the data frame you would like to retain. You can use then json.column argument to as.tbl_json to specify which column contains the JSON of interest:

df <- tibble(id = 1:2, json = list('[1, 2]', '[3, 4]'))
df %>% as.tbl_json(json.column = "json")
#> # A tbl_json: 2 x 2 tibble with a "JSON" attribute
#>   ..JSON          id
#>   <chr>        <int>
#> 1 "\"[1, 2]\""     1
#> 2 "\"[3, 4]\""     2

File

If your JSON is in a file, like in the jsonlines format, then you can use read_json to read the file into a tbl_json object directly.

JSON included in the package

The tidyjson package comes with several JSON example datasets:

Each dataset has some example tidyjson queries in help(commits), help(issues), help(worldbank) and help(companies).