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:
Public APIs (e.g., Twitter)
NoSQL databases as a document format (e.g., MongoDB)
Relational databases as a new column type (e.g., PostgreSQL)
Tidyjson provides a grammar for turning complex JSON data into tidy data frames that are easy to work with in the tidyverse.
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:
Work on a single JSON document, or on a collection of related documents
Create pipelines with %>%, producing code that can be read from left to right
Guarantee the structure of the data produced, even if the input JSON structure changes (with the exception of spread_all)
Work with arbitrarily nested arrays or objects
Handle ‘ragged’ arrays and / or objects (varying lengths by document)
Allow for extraction of data in values or object names
Ensure edge cases are handled correctly (especially empty data)
Integrate seamlessly with dplyr, allowing tbl_json objects to pipe in and out of dplyr verbs where reasonable
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 JonesThis 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.
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 rowsBut 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 500It 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 rowsand 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 rowsWe 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 rowsBy 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 rowsTidyjson 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.
The following are common patterns for manipulating JSON data with tidyjson.
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 rowsIdentify the names under an object, and their type and frequency.
gather_object %>% json_types %>% count(name, type)
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 rowsFilter 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 rowscompanies[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 attributionUseful when data is stored in object names as well as values
gather_object %>% append_values_X
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:
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 rowsIf 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.
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:..." 1A 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 rowsIf 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:
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:
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.
The tidyjson package comes with several JSON example datasets:
commits: commit data for the dplyr repo from github API
issues: issue data for the dplyr repo from github API
worldbank: world bank funded projects from jsonstudio
companies: startup company data from jsonstudio
Each dataset has some example tidyjson queries in help(commits), help(issues), help(worldbank) and help(companies).