Chapter 14: Mapping a JSON API Dump

A week after the submission, the Regional Veterinary Board wrote back. This time there was no form to fill in, just an attachment: Venue.json, an export from their API listing every place where the next vaccination campaign would run. “Please link your records to these venues.”

Dr. Portbridge opened it. It was not a table. It was nested braces, lists inside objects inside lists. Typing each venue as a fact by hand was out of the question.

The problem

Facts (Chapter 8) name one instance at a time. Real data arrives in bulk, and more and more often it arrives as JSON: nested, with no obvious “row”. A mapping declares the shape of an instance once and lets the file supply the values. For JSON it needs two things a spreadsheet never asks for: what counts as one record, and where each value sits inside it.

The file

Here is the board’s export:

{
  "venue": [
    {
      "latitude": 50.901389,
      "longitude": 4.484444,
      "location": { "continent": "EU", "country": "BE", "city": "Brussels" }
    }
  ]
}

One venue, with a position and a nested location. We want two things out of it: a city, and the coordinates the city is linked to.

The whole mapping


prefix <http://example.com/ns#> as ex

mapping from <package://Venue.json> as JSONPath
  for each "$.venue[*]"
  map:
    "$.latitude" as ?lat
    "$.longitude" as ?long
    "$.location.continent" as ?continent
    "$.location.country" as ?country
    "$.location.city" as ?city
  to:
    fact <http://loc.example.com/city/{?city}> a ex:City
      ex:countryCode ?country
      ex:latlong <http://loc.example.com/latlong/{?lat},{?long}>
      ex:onContinent ?continent
    fact <http://loc.example.com/latlong/{?lat},{?long}> a ex:latlong
      ex:lat {?lat}
      ex:long {?long}

It reads top to bottom in four steps.

1. The source

mapping from <package://Venue.json> as JSONPath names the file, relative to the package root (the directory holding package.dlf), and picks the JSON reader. Values will be addressed with JSONPath strings, where $ is the root and .name steps into an object member.

2. `for each`: what is a row?

for each "$.venue[*]" says: every element of the venue array is one record. The mapping runs once per element. With three venues in the array, the to: block would run three times.

Leave the line out and the whole document is a single record.

3. `map:`: where the values are

Each line binds a path to a variable. The paths are relative to the current venue: inside map:, $ is the venue being processed, not the whole file. So "$.latitude" reads that venue’s latitude, and "$.location.city" steps into its nested location object.

JSON values bind as plain text. The number 50.901389 binds the text "50.901389", exactly as a spreadsheet cell would. To get a typed literal, add a cast in the template ({?lat} as float), the same casts a CSV mapping uses.

4. `to:`: what to build

The to: block is ordinary fact syntax with {?var} holes. There are two fact templates, and both run for every venue:

the first builds a city, named after ?city, with its country, continent and a link to a coordinates node;
the second builds that coordinates node, named after ?lat and ?long.

The link works because both templates build the same IRI, <http://loc.example.com/latlong/{?lat},{?long}>, from the same variables.

What comes out

Building the package expands the mapping before Turtle is generated, so the venue becomes two subjects, exactly as if they had been written by hand:

<http://loc.example.com/city/Brussels> rdf:type owl:NamedIndividual
  , ex:City
  ; rdfs:isDefinedBy <http://example.org/venues/venues>
  .
<http://loc.example.com/city/Brussels> ex:countryCode "BE" .
<http://loc.example.com/city/Brussels> ex:latlong <http://loc.example.com/latlong/50.901389,4.484444> .
<http://loc.example.com/city/Brussels> ex:onContinent "EU" .

<http://loc.example.com/latlong/50.901389,4.484444> rdf:type owl:NamedIndividual
  , ex:latlong
  ; rdfs:isDefinedBy <http://example.org/venues/venues>
  .
<http://loc.example.com/latlong/50.901389,4.484444> ex:lat "50.901389" .
<http://loc.example.com/latlong/50.901389,4.484444> ex:long "4.484444" .

Each subject is typed by its template (ex:City, ex:latlong) and carries its mapped properties: three for the city, two for the coordinates. The owl:NamedIndividual and rdfs:isDefinedBy lines are added to every fact, mapped or hand-written.

When a path does not match

Real exports are messy. Suppose a second venue had no location.city. Then "$.location.city" matches nothing in that venue, and ?city is empty, exactly like an empty spreadsheet cell. Since ?city builds the city’s subject IRI, there is no sensible subject to build, so that whole venue is skipped with a diagnostic, and the other venues are unaffected.

An empty value that only feeds an optional property just drops that one property. JSON null counts as empty too. The other cases:

a path that hits an object or an array ("$.location") skips the venue: a whole subtree is never turned into a text literal;
a path that hits several values binds the first one and warns; to get one record per value, move that path into for each instead.

The syntax reference lists every rule.

The board’s venues landed in the system as cities and coordinates, linked to each other, without a single line typed by hand. When the board published next month’s export, Dr. Portbridge replaced the file and rebuilt. The mapping did not change.

Keyboard shortcuts

Dolfin Documentation