tidyjson v0.2.4


Monthly downloads



Tidy Complex 'JSON'

Turn complex 'JSON' data into tidy data frames.



CRAN\_Status\_Badge Build
Status AppVeyor Build

Status CRAN
Activity CRAN


tidyjson provides tools for turning complex json into tidy data.


Get the released version from CRAN:


or the development version from github:



The following example takes a character vector of 500 documents in the worldbank dataset and spreads out all objects.
Every JSON object key gets its own column with types inferred, so long as the key does not represent an array. When recursive=TRUE (the default behavior), spread_all does this recursively for nested objects and creates column names using the sep parameter (i.e. {"a":{"b":1}} with sep='.' would generate a single column: a.b).


worldbank %>% spread_all
#> # A tbl_json: 500 x 8 tibble with a "JSON" attribute
#>    `attr(., "JSON"… document.id boardapprovalda… closingdate
#>    <chr>                  <int> <chr>            <chr>      
#>  1 "{\"_id\":{\"$o…           1 2013-11-12T00:0… 2018-07-07…
#>  2 "{\"_id\":{\"$o…           2 2013-11-04T00:0… <NA>       
#>  3 "{\"_id\":{\"$o…           3 2013-11-01T00:0… <NA>       
#>  4 "{\"_id\":{\"$o…           4 2013-10-31T00:0… <NA>       
#>  5 "{\"_id\":{\"$o…           5 2013-10-31T00:0… 2019-04-30…
#>  6 "{\"_id\":{\"$o…           6 2013-10-31T00:0… <NA>       
#>  7 "{\"_id\":{\"$o…           7 2013-10-29T00:0… 2019-06-30…
#>  8 "{\"_id\":{\"$o…           8 2013-10-29T00:0… <NA>       
#>  9 "{\"_id\":{\"$o…           9 2013-10-29T00:0… 2018-12-31…
#> 10 "{\"_id\":{\"$o…          10 2013-10-29T00:0… 2014-12-31…
#> # ... with 490 more rows, and 5 more variables: countryshortname <chr>,
#> #   project_name <chr>, regionname <chr>, totalamt <dbl>, `_id.$oid` <chr>

Some objects in worldbank are arrays, which are not handled by spread_all. This example shows how to quickly summarize the top level structure of a JSON collection

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

In order to capture the data in the majorsector_percent array, we can use enter_object to enter into that object, gather_array to stack the array and spread_all to capture the object items under the array.

worldbank %>%
  enter_object(majorsector_percent) %>%
  gather_array %>%
  spread_all %>%
  select(-document.id, -array.index)
#> # A tbl_json: 1,405 x 2 tibble with a "JSON" attribute
#>    `attr(., "JSON")`       Name                                    Percent
#>    <chr>                   <chr>                                     <dbl>
#>  1 "{\"Name\":\"Educat..." Education                                    46
#>  2 "{\"Name\":\"Educat..." Education                                    26
#>  3 "{\"Name\":\"Public..." Public Administration, Law, and Justice      16
#>  4 "{\"Name\":\"Educat..." Education                                    12
#>  5 "{\"Name\":\"Public..." Public Administration, Law, and Justice      70
#>  6 "{\"Name\":\"Public..." Public Administration, Law, and Justice      30
#>  7 "{\"Name\":\"Transp..." Transportation                              100
#>  8 "{\"Name\":\"Health..." Health and other social services            100
#>  9 "{\"Name\":\"Indust..." Industry and trade                           50
#> 10 "{\"Name\":\"Indust..." Industry and trade                           40
#> # ... with 1,395 more rows


Spreading objects into columns

  • spread_all() for spreading all object values into new columns, with nested objects having concatenated names

  • spread_values() for specifying a subset of object values to spread into new columns using the json_chr(), json_dbl() and json_lgl() functions. It is possible to specify multiple parameters to extract data from nested objects (i.e. json_chr('a','b')).

Object navigation

  • enter_object() for entering into an object by name, discarding all other JSON (and rows without the corresponding object name) and allowing further operations on the object value

  • gather_object() for stacking all object name-value pairs by name, expanding the rows of the tbl_json object accordingly

Array navigation

  • gather_array() for stacking all array values by index, expanding the rows of the tbl_json object accordingly

JSON inspection

  • json_types() for identifying JSON data types

  • json_length() for computing the length of JSON data (can be larger than 1 for objects and arrays)

  • json_complexity() for computing the length of the unnested JSON, i.e., how many terminal leaves there are in a complex JSON structure

  • is_json family of functions for testing the type of JSON data

JSON summarization

  • json_structure() for creating a single fixed column data.frame that recursively structures arbitrary JSON data

  • json_schema() for representing the schema of complex JSON, unioned across disparate JSON documents, and collapsing arrays to their most complex type representation

Creating tbl_json objects

  • as.tbl_json() for converting a string or character vector into a tbl_json object, or for converting a data.frame with a JSON column using the json.column argument

  • tbl_json() for combining a data.frame and associated list derived from JSON data into a tbl_json object

  • read_json() for reading JSON data from a file

Converting tbl_json objects

  • as.character.tbl_json for converting the JSON attribute of a tbl_json object back into a JSON character string

Included JSON data

  • 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


The goal is to turn complex JSON data, which is often represented as nested lists, into tidy data frames that can be more easily manipulated.

  • 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

Tidyjson depends upon

  • magrritr for the %>% pipe operator
  • jsonlite for converting JSON strings into nested lists
  • purrr for list operators
  • tidyr for unnesting and spreading

Further, there are other R packages that can be used to better understand JSON data

Functions in tidyjson

Name Description
determine_types Determines the types of a list of parsed JSON
gather_array Gather a JSON array into index-value pairs
json_schema Create a schema for a JSON document or collection
json_lengths Compute the length of JSON data
filter Objects exported from other packages
is_data_list List Check
has_names Check for Names
as_tibble Objects exported from other packages
append_values_factory Creates the append_values_* functions
read_json Reads JSON from an input uri (file, url, ...) and returns a tbl_json object
list_or_dots List or Dots
my_unlist Unlists while preserving NULLs and only unlisting lists with one value
spread_all Spreads all scalar values of a JSON object into new columns
tbl_json Combines structured JSON (as a data.frame) with remaining JSON
enter_object Enter into a specific object and discard all other JSON data
json_complexity Compute the complexity (recursively unlisted length) of JSON data
is_json_factory Factory to create is_json functions
issues Issue data for the dplyr repo from github API
is_json Predicates to test for specific JSON types in tbl_json objects
print.tbl_json Print a tbl_json object
json_types Add a column that tells the 'type' of the JSON data
json_structure Recursively structures arbitrary JSON data into a single data frame
tidyjson tidyjson.
path Create a JSON path with a minimum of typing
%>% Objects exported from other packages
append_values_type get list of values from json
gather_object Gather a JSON object into name-value pairs
gather_factory Factory to create gather functions
json_factory Factory that creates the j* functions below
[.tbl_json Extract subsets of a tbl_json object (not replace)
spread_values Spreads specific scalar values of a JSON object into new columns
json_functions Navigates nested objects to get at names of a specific type, to be used as arguments to spread_values
rbind_tbl_json Bind two tbl_json objects together and preserve JSON attribute
worldbank Projects funded by the World Bank
wrap_dplyr_verb Wrapper for extending dplyr verbs to tbl_json objects
bind_rows Bind Rows (tidyjson)
as_tibble.tbl_json Convert a tbl_json back to a tbl_df
append_values Appends all JSON values with a specified type as a new column
commits Commit data for the dplyr repo from github API
as.character.tbl_json Convert the JSON in an tbl_json object back to a JSON string
allowed_json_types Fundamental JSON types from http://json.org/, where I collapse 'true' and 'false' into 'logical'
companies Startup company information for 1,000 companies
No Results!

Vignettes of tidyjson

No Results!

Last month downloads


License MIT + file LICENSE
LazyData true
VignetteBuilder knitr
URL https://github.com/colearendt/tidyjson
BugReports https://github.com/colearendt/tidyjson/issues
RoxygenNote 6.1.1
NeedsCompilation no
Packaged 2019-12-02 20:39:30 UTC; ligges
Repository CRAN
Date/Publication 2019-12-02 21:39:30

Include our badge in your README