Parsing a Package Registry Metadata File Where Keys Are Data

Most JSON a data person meets has keys that name fields: name, price, created_at. A package registry's metadata file has a different structure. The object versions has one member per released version, and the key is the version number. The object time has one member per version too, plus two special ones. A list of dependencies is an object whose keys are the dependency names and whose values are version ranges. The schema, in the sense of a fixed list of keys, does not exist, because the keys are the data.

This is the shape that breaks the usual json_normalize-style tools, and it comes up well beyond package managers: anything that maps ids to records does it. I built a metadata file of the same layout as an npm-style registry document and used it to test the ways of reading it. The versions, dates and dependencies are generated, and the structure follows the registry format.

The layout

{"name": "demo-lib",
 "dist-tags": {"latest": "17.4.4", "next": "17.4.4"},
 "versions": {"0.1.1": {"name": "demo-lib", "version": "0.1.1",
                        "dependencies": {"tslib": "^2.4.0", "chalk": "^4.1.2"},
                        "dist": {"tarball": "https://registry.example.org/...", "integrity": "sha512-..."}},
              "0.1.2": {...}},
 "time": {"created": "2019-01-04T10:00:00.000Z", "0.1.1": "2019-01-12T04:00:00.000Z", ..., "modified": "2028-03-06T06:00:00.000Z"}}

Three structures share the property that the keys are values. versions maps a version string to a metadata object. time maps a version string (and two special words) to a timestamp. dependencies maps a package name to a range. A table of this document wants a row per version, and a row per dependency edge, and none of the keys is a column name.

DuckDB infers a map

DuckDB notices when an object has many differently named keys with similar values, and types it as a map instead of a struct:

select typeof(versions), typeof(time), typeof("dist-tags")
from read_json('pkg.json', maximum_object_size=104857600);

MAP(VARCHAR, STRUCT("name" VARCHAR, "version" VARCHAR, description VARCHAR, ...))
MAP(VARCHAR, TIMESTAMP WITH TIME ZONE)
STRUCT(latest VARCHAR, "next" VARCHAR)

versions is a map from string to struct, time a map from string to timestamp, and dist-tags, which has two stable keys, is an ordinary struct. That inference is the useful part: it chose by looking at the data, and a struct with 600 fields would have made every query unreadable. Unnesting the map gives rows:

select unnest(map_values(versions)) as v from read_json('pkg.json', maximum_object_size=104857600);

which let me count v.deprecated is not null over the 600 versions: 30 had a deprecation message. If you would rather not depend on inference, read the document as JSON text and go through the keys:

create table p as select * from read_json_objects('pkg.json');
select count(*) from (select unnest(json_keys(json->'$.versions')) k from p);   -- 600

For the dependency edges, two levels of keys:

select count(*) as dep_edges, count(distinct d) as distinct_deps from (
  select v, unnest(json_keys(json->('$.versions."' || v || '".dependencies'))) d
  from (select json, unnest(json_keys(json->'$.versions')) v from p));
-- 1781 | 10

1,781 edges across 10 distinct packages, in 0.45 s. The quotes around the version in the path matter, because a version contains dots that would otherwise read as path separators.

PostgreSQL does it with jsonb_each

select count(*) from pk, jsonb_each(d->'versions');                      -- 600  (1.5 ms)

select count(*) as dep_edges, count(distinct k) as distinct_deps
from pk, jsonb_each(d->'versions') v, jsonb_object_keys(v.value->'dependencies') k;
-- 1781 | 10                                                              (133 ms)

jsonb_each turns an object into rows of (key, value), and jsonb_object_keys returns the keys of an inner object. The same 1,781 and 10 as in DuckDB, in 133 ms over a document already loaded in a table. A lateral join of one on the other is the pattern for any map of maps (expanding JSONB into rows with jsonb_to_recordset covers the neighbors). If the shape is a column that will be queried often, store the edges in a real table once and index it.

Versions do not sort as strings

The most reliable way to get a wrong answer from this file is to sort the keys:

max(versions)                  # '9.3.2'
max(stable, key=semver_key)    # '17.4.4'

As strings, "9.3.2" is greater than "17.4.4" because "9" is greater than "1". The real maximum is 17.4.4. Sort on the numeric parts:

def key(x):
    core, _, pre = x.partition("-")
    return (tuple(int(p) for p in core.split(".")), pre == "", pre)

The tuple compares major, minor and patch as integers, and the second element puts a prerelease before the release of the same version (1.2.3-beta.1 is lower than 1.2.3). The sorted tail was 17.4.1, 17.4.2, 17.4.3, 17.4.4. For the full semantic versioning rules, with prerelease identifiers that compare piece by piece, use a library; the tuple above handles the common case. In PostgreSQL the same ordering is order by string_to_array(split_part(key,'-',1), '.')::int[] desc, which returned 17.4.4 and 17.4.3.

Do not trust either source blindly. The dist-tags object says which version is latest, and here latest was 17.4.4, equal to the highest stable version, which I confirmed. A publisher can move a tag to an older release, so the tag is a statement by the maintainer, not a computed maximum. Use the tag when the question is "what would a user install" and the sort when it is "what is the highest number".

The odd keys in time

The time map has one entry per version and two extra: created and modified.

[k for k in doc["time"] if k not in doc["versions"]]       # ['created', 'modified']

A loop that treats every key as a version will try to parse created as one. Filter by membership in versions, or by a pattern. Check the other direction too: every version should have a time entry, and in this file all 600 did, but a registry document that was edited by hand or truncated might not. Prereleases are in both maps: 29 of the 600 versions had a hyphen, such as 1.4.0-beta.1, and many reports want them excluded.

Dependencies are keys, not values

To find the most used dependency, count keys, not values:

collections.Counter(d for v in versions.values() for d in v["dependencies"]).most_common(3)
# [('tslib', 189), ('chalk', 186), ('yargs', 186)]

The values are version ranges like ^2.4.0, which are strings with their own grammar (^, ~, >=, ||). Do not compare them as strings, and do not treat them as versions. 92 versions had no dependencies at all, so any average should say whether it is over all versions or only those with at least one.

Keep two kinds of numbers apart as well: count of versions that depend on a package, and count of edges. They are the same here because a package appears once per dependencies object, but a document with dependencies and devDependencies in the same version would count a package twice if you merged them.

Practical notes

The 20 KB readme string is most of the file's non-data weight, and for large packages the metadata file can reach tens of megabytes, because every version repeats its full manifest. Read it with a parser that handles large objects (querying JSON files directly in DuckDB and expanding JSON arrays into rows with json_each in SQLite show the same key expansion elsewhere) (DuckDB's default object limit of 16 MB is why my command passed maximum_object_size), and drop the fields you do not need early (handling JSON files too large to parse covers the options).

When you store the result, normalize to three tables: versions (one row each, with the published time and the deprecation message), dependencies (version, package, range) and tags. Everything above becomes ordinary SQL. For reading the original text and checking a path, use the JSON path tester, and the JSON formatter to look at one version. The DuckDB JSON functions and the PostgreSQL JSON function list document the calls used, and the semantic versioning rules are at semver.org.

When a key-per-version object should not be flattened

You need one version. doc["versions"]["17.4.4"] is a lookup, and there is nothing to flatten.

The document is the unit of storage. If you cache package metadata per package and read it whole, keep it as a document.

You are checking integrity. Hashes and tarball URLs belong to the exact text. Flatten a copy for analysis and leave the original untouched.

The keys are not stable identifiers. In a map keyed by something that changes (a display name, a tag), the key is not an id. Make sure the key is the thing you would join on.