We can read DataFrames from JSON, but since JSON doesn't have any types, DataFrame has make its best guess as to which objects have which attributes and how they relate to each other. While this works in most simple cases, for more complex JSON APIs, this might break down. One of the cases where normal JSON reading breaks down is in the API of apis.guru, a website which collects OpenAPI APIs.

As you can see, since this API provides its data in a key/value fashion, we get a DataFrame of 2000+ columns, this is very difficult to work with. Actually, it's impossible. Assigning this result to a variable results in OOM errors, as we simply don't have the memory to generate all accessors for all columns.

To solve this, we could perform a

This converts the DataFrame such that it has a column "key" containing the previous column names and a column "value" containing, well, the values. One downside, however, is that gather {} fills in the "missing" values with null which gives each versions column all the version numbers of all APIs, filled in with empty values. This again is difficult to work with...

Specifically for this case, we provide a new feature for reading JSON: Key/Value paths! In this data, two paths can be seen as key/value objects: The top level object (represented as '$'), and the versions (represented as '$[*]["versions"]').

Key/value objects are wrapped in a dataframe (to have a working hierarchy). Since we convert the top-level object to key/value we need to unpack it to see the result.

Now, for this specific example it works, but admittedly it was a bit of a hassle, don't you think? Plus, columns like "added" are still just Strings, while clearly, it should be Date objects.

Now, you could write your own @DataSchema and read methods, but as it happens, ApiGuru has an OpenAPI declaration which includes all the type schemas the JSON will adhere to!

This OpenAPI declaration contains all URLs the API provides, as well as the types of data those calls will return. OpenAPI declarations can be explored often on the API provider or, for instance, in IntelliJ. From exploring the OpenAPI spec from Apis.guru we see that /list.json returns an APIs type and other types include API, ApiVersion, and Metrics.

In Jupyter we can generate these types easily:

And then we can directly read the JSON as one of the types:

As you can see, we've successfully read and converted the JSON to the correct types (including key/values and correct nullability!) using a foolproof method. Go ahead and explore the data a bit more if you feel like it!