This document defines the procedures and rules to be applied when converting tabular data into JSON. Tabular data may be complemented with metadata annotations that describe its structure, the meaning of its content and how it may form part of a collection of interrelated tabular data. This document specifies the effect of this metadata on the resulting JSON.
TheCSV on the Web Working Group waschartered to produce a recommendation "Access methods for CSV Metadata" as well as recommendations for "Metadata vocabulary for CSV data" and "Mapping mechanism to transforming CSV into various formats (e.g., RDF, JSON, or XML)". This document aims to satisfy the JSON variant of the mapping recommendation.
This document describes the processing oftabular data to create a set of nested objects thatMUST be serialized as JSON [RFC7159].
Note
The conversion of CSV content to JSON is intended for web developers who need not care about the complexities of RDF [rdf11-concepts]. Where the formality of RDF is required, [csv2rdf] provides the procedures for mapping from CSV content to RDF which may be serialized to [json-ld].
The conversion procedure described in this specification operates on theannotated tabular data model. This specification does not specify the processes needed to convert CSV-encoded data into tabular data form. Please refer to [tabular-data-model] for details ofparsing tabular data.
Conversion applicationsMUST provide at least two modes of operation:standard andminimal.
Standard mode conversion frames the information gleaned from thecells of thetabular data with details of therows,tables, and agroup of tables within which that information is provided.
Minimal mode conversion includesonly the information gleaned from thecells of thetabular data within the output.
As well as sections marked as non-normative, all authoring guidelines, diagrams, examples, and notes in this specification are non-normative. Everything else in this specification is normative.
The key wordsMAY,MUST, andSHOULD are to be interpreted as described in [RFC2119].
Tabular dataMUST conform to the description from [tabular-data-model]. In particular note that eachrowMUST contain the same number ofcells (although some of thesecells may be empty).
Note
Not all CSV-encoded data can be parsed into a tabular data model. An algorithm for parsing CSV-based files is described in [tabular-data-model].
3.Typographical conventions
The following typographic conventions are used in this specification:
markup
Markup (elements, attributes, properties), machine processable values (string, characters, media types), property name, or a file name is in red-orange monospace font.
variable
A variable in pseudo-code or in an algorithm description is in italics.
definition
A definition of a term, to be used elsewhere in this or other specifications, is in bold and italics.
A references to a definitionin this document, when the reference itself is also a markup, is underlined, red-orange monospace font, and is also an active link to the definition itself.
A reference to a definitionin another document, when the reference itself is also a markup, is underlined, in italics red-orange monospace font, and is also an active link to the definition itself.
A document reference (normative or informative) is enclosed in square brackets and links to the references section.
Note
Notes are in light green boxes with a green left border and with a "Note" header in green. Notes are normative or informative depending on the whether they are in a normative or informative section, respectively.
Example 1
Examples are in light khaki boxes, with khaki left border, and with a numbered "Example" header in khaki. Examples are always informative. The content of the example is in monospace font and may be syntax colored.
Anarray is defined in JSON ([RFC7159]) as an ordered sequence of zero or more values, where a value is a string, number, boolean, null,object, orarray.
In the context of this specification,name is used as defined in JSON ([RFC7159]); that is, thatname is a string that provides a unique key within a set of name-value pairs within a JSONobject.
Anobject is defined in JSON ([RFC7159]) as an unordered collection of zero or more name-value pairs, wherename is a string andvalue is a string, number, boolean, null,object, orarray.
A conformant JSON conversion applicationMUST produce output conforming to this algorithm according to the chosen mode of conversion:standard orminimal.
The [tabular-data-model] specifies that string values withintabular data (such ascolumn titles orcell string values)MUST contain only Unicode characters. No Unicode normalization (as specified in [UAX15]) is applied to these string values during the conversion to JSON.
4.2.1Minimal mode
The steps in the algorithm defined here apply tominimal mode.
Insert an emptyarrayA into the JSON output. Theobjects containing the name-value pairs associated with thecell values will be subsequently inserted into thisarray.
whereAR is anarray into which theobjects describing therows will be subsequently inserted.
Eachrow within thetable is processed sequentially in order. For eachrow in the currenttable:
Insert an emptyobjectR into thearrayAR to represent therow.
Specify therow numbern for therow; insert the following name-value pair intoobjectR:
name
rownum
value
n
Specify therow source numbernsource for therow within the source tabular data fileURL using a fragment-identifier as specified in [RFC7111]; ifrow source number is notnull, insert the following name-value pair intoobjectR:
name
url
value
URL#row=nsource
Specify anytitles for therow; ifrow titles is notnull, insert the following name-value pair intoobjectR:
name
titles
value
t
wheret is the single value or array of values provided by therow titles annotation.
Note
JSON has no native support for expressing language information; therefore any such information associated with therow titles is ignored.
The steps in the algorithm defined here apply to bothstandardandminimal modes.
This algorithm generates a sequence ofobjects,S1 toSn, each of which corresponds to asubject described by the currentrow. The algorithm inserts name-value pairs intoSi depending on the cell values as outlined in the following steps.
If the identifier forsubjecti,IS, is notnull, then insert the following name-value pair intoobjectSi:
name
@id
value
IS
Eachcell referring tosubjecti is then processed sequentially according to the order of thecolumns.
For eachcell referring tosubjecti, where thesuppress output annotation for thecolumn associated with thatcell isfalse, insert a name-value pair intoobjectSi as described below:
Else,nameN takes the URI decoded value of thename annotation for thecolumn associated with thecell. (URI decoding is necessary asname may have been encoded if it was taken from a supplied title.)
If thevalue URL for the currentcell is notnull, then insert the following name-value pair intoobjectSi:
name
N
value
Vurl
whereVurl is the value ofvalue URL annotation for the currentcell expressed as a string in the JSON output. IfN is@type, compactVurl according to the rules as defined inURL Compaction in [tabular-metadata].
Else, if thecell value is alist that is not empty, then thecell value provides a sequence of values for inclusion within the JSON output; insert anarrayAv containing each valueV of the sequence intoobjectSi:
name
N
value
Av
Each of the valuesV derived from the sequenceMUST be expressed in the JSON output according to thedatatype ofV as defined below insection4.5Interpreting datatypes.
Note
Sincearrays are implicitlyordered in JSON, theordered annotation has no effect on the JSON output.
Else, if thecell value is notnull, then thecell value provides a single valueV for inclusion within the JSON output; insert the following name-value pair intoobjectSi:
IfnameN occurs more than once withinobjectSi, the name-value pairs from each occurrence ofnameNMUST be compacted to form a single name-value pair withnameN and whose value is an array containing all values from each of those name-value pairs. Where the value from one or more contributing name-value pairs is of type array, the values from contributing arrays are included directly to the resulting array (i.e. arrays of values are flattened).
4.4Generating Nested Objects
The steps in the algorithm defined herein apply to bothstandardandminimal modes.
Where the currentrow describes multiplesubjects, itMAY be possible to organize theobjects associated with thosesubjects such that someobjects arenested within others; e.g. where thevalue URL annotation for onecell matches theabout URL annotation for anothercell in the samerow. This algorithm considers a sequence ofobjects generated according to4.3Generating Objects,S1 toSn, each of which corresponds to asubject described by the currentrow. It generates a new sequence ofrootobjects,SR1 toSRm, thatMAY includenestedobjects.
Note
Where the currentrow describes only a singlesubject, this algorithm may be bypassed as no nesting is possible. In such a case, therootobjectSR1 is identical to the originalobjectS1.
Note
This nesting algorithm is based on the interrelationships betweensubjects described within a givenrow that are specified using thevalue URL annotation.Cell values expressing the identity of asubject in the currentrow (i.e., as a simple literal) will be ignored by this algorithm.
The algorithm uses the following terms:
child
If twovertices are connected in atree, the one which is further away from theroot of thetree is referred to as thechild of the other.
descendant
AvertexN is adescendant of avertexM if eitherN is thechildM, or there areverticesV1,…,Vk, such thatV1=M,Vk=N, andVk+1 is achild ofVk.
edge
One of the main constituents ofgraphs;edges are used to establish relationships amongvertices. In the context of this algorithm,edges are expressed in JSON using a name-value pair whose value is anotherobject or anarray ofobjects.
forest
A collection of disjointtrees. For the purpose of this algorithm, the order oftrees is important, i.e., forests can also be viewed as an ordered sequence ofroots.
graph
Data structure consisting ofvertices (or "nodes") andedges. See, for example, [Knuth] for further details.
Atree (orrooted tree) is a connected, acyclicgraph where onevertex has been designated as theroot, in which case theedges have a natural orientationtowards oraway from theroot.
vertex
One of the main constituents ofgraphs; avertex usually holds further information or data. In the context of this algorithm,vertices are used to represent the JSONobjects.
The nesting algorithm is defined as follows:
For allcells in the currentrow, determine thevalue URLs,Vurl, that occuronly once. The list of these uniquely occurringvalue URLs is referred to as theURL-list.
Determine the identity ofobjectSi:IS. If present inobjectSi, the name-value pair withname@id provides the value ofIS. Else,objectSi is not explicitly identified andIS isnull.
For allcells associated with the currentobjectSi (e.g. whoseabout URL annotation matchesIS):
If thevalue URL annotation of the currentcell is defined and its value,Vurl, appears in theURL-list, then check each of theotherobjects in the sequenceS1 toSn to determine ifVurl identifies one of thoseobjects.
ForobjectSj, if the name-value pair withname@id is present and its value matchesVurl, then:
Eachvertex inforestF represents an object in the original sequence ofobjectsS1 toSn and is associated with asubject described by the currentrow. RearrangeobjectsS1 toSn such that they mirror the structure of thetrees inforestF as follows: IfvertexM, representingobjectSi, is achild ofvertexN, representingobjectSj, then the name-value pair inobjectSj associated with theedge relatingM andNMUST be modified such that the (literal) value,Vurl, from that name-value pair is replaced byobjectSi thus creating anestedobject.
Instances of JSON reserved characters within string valuesMUST be escaped as defined in [RFC7159].
JSON has no native support for expressing language information; therefore thelanguage of a value has no effect on the JSON output.
Note
Only thebase annotation value is used to determine the primitive type used within the JSON output. Additional restrictions to thecell value's datatype, such as theid annotation, are ignored for the purposes of conversion to JSON.
Note
Adatatype'sformat is irrelevant to the conversion procedure defined in this specification; thecell value has already been parsed from the contents of thecell according to theformat annotation.
Cell errorsMUST be recorded by applications where the contents of acell cannot be parsed or validated (seeParsing Cells andValidating Tables in [tabular-data-model] respectively). In cases wherecell errors are recorded, applications may attempt to determine the appropriate JSON primitive type during the subsequent conversion process according to local rules.
Conversion applications may have other means to createannotated tables, e.g. through some application specific APIs. In such cases the exact format for non-core annotations or notes may be different. Specifications for such annotation processes should specify how these annotations are converted into JSON.
Name-value pairs fromnotes andnon-core annotations annotations are generally copied verbatim from the metadata description subject to the exceptions below:
Name-value pairs whose value is anobject using the [json-ld] keyword@value, for example:
name
N
value
{ "@value": "V" }
are transformed to:
name
N
value
V
Name-value pairs occurring within the value object that use [json-ld] keywords@language and@type are ignored.
Name-value pairs whose value is anobject using the [json-ld] keyword@id to coerce a string-value to be interpreted as an IRI, for example:
Each of the examples expresses more complex conversions - it is recommended that readers of this specification work through the examples in sequential order.
6.1Simple example
This example comprises a singleannotated table containing information attributes about countries; country code, position (latitude, longitude) and name. Whilst the input tabular data file, published athttp://example.org/countries.csv, includes aheader line, no further metadata annotations are given. The tabular data file is provided below:
Example 2: http://example.org/countries.csv
countryCode,latitude,longitude,nameAD,42.5,1.6,AndorraAE,23.4,53.8,"United Arab Emirates"AF,33.9,67.7,Afghanistan
Theannotated table generated from parsing the tabular data file is shown below and provides the basis for the conversion to JSON.
Annotations for the resultingtableT, with 4 columns and 3 rows, are shown below:
Minimal mode output for this example is provided below:
Example 3: http://example.org/countries-minimal.json
[{"countryCode":"AD","latitude":"42.5","longitude":"1.6","name":"Andorra"},{"countryCode":"AE","latitude":"23.4","longitude":"53.8","name":"United Arab Emirates"},{"countryCode":"AF","latitude":"33.9","longitude":"67.7","name":"Afghanistan"}]
Note
Theabout URL annotation has not been set for cells in tableT ({ "url": "http://example.org/countries.csv"});cells in a givenrow whereabout URL has not been specified are assumed to refer to the samesubject and so the name-value pairs associated with thecell values of thatrow occur within the sameobject.
Given that theproperty URL isnull for cells in tableT ({ "url": "http://example.org/countries.csv"}), the simplifiedname is used in the name-value pairs; e.g.countryCode rather thanhttp://example.org/countries.csv#countryCode
Standard mode output for this example is provided below:
Example 4: http://example.org/countries-standard.json
{"tables":[{"url":"http://example.org/countries.csv","row":[{"url":"http://example.org/countries.csv#row=2","rownum":1,"describes":[{"countryCode":"AD","latitude":"42.5","longitude":"1.6","name":"Andorra"}]},{"url":"http://example.org/countries.csv#row=3","rownum":2,"describes":[{"countryCode":"AE","latitude":"23.4","longitude":"53.8","name":"United Arab Emirates"}]},{"url":"http://example.org/countries.csv#row=4","rownum":3,"describes":[{"countryCode":"AF","latitude":"33.9","longitude":"67.7","name":"Afghanistan"}]}]}]}
The name-value pair withnameurl provides reference to the original tabular data file and to specificrows therein.
Therow number is provided for eachrow using name-value pair withnamerownum.
Theobject containing the name-values pairs associated with thecell values of arow are related to theobject for thatrow using the name-value pair withnamedescribes.
6.2Example with single table and rich annotations
This example is based onUse Case #11 - City of Palo Alto Tree Data and comprises a singleannotated table describing an inventory of tree maintenance operations. The input tabular data file, published athttp://example.org/tree-ops-ext.csv, and the associated metadata descriptionhttp://example.org/tree-ops-ext.csv-metadata.json are provided below:
Example 5: http://example.org/tree-ops-ext.csv
GID,On Street,Species,Trim Cycle,Diameter at Breast Ht,Inventory Date,Comments,Protected,KML1,ADDISON AV,Celtis australis,Large Tree Routine Prune,11,10/18/2010,,,"<Point><coordinates>-122.156485,37.440963</coordinates></Point>"2,EMERSON ST,Liquidambar styraciflua,Large Tree Routine Prune,11,6/2/2010,,,"<Point><coordinates>-122.156749,37.440958</coordinates></Point>"6,ADDISON AV,Robinia pseudoacacia,Large Tree Routine Prune,29,6/1/2010,cavity or decay; trunk decay; codominant leaders; included bark; large leader or limb decay; previous failure root damage; root decay; beware of BEES,YES,"<Point><coordinates>-122.156299,37.441151</coordinates></Point>"
Example 6: http://example.org/tree-ops-ext.csv-metadata.json
{"@context":["http://www.w3.org/ns/csvw",{"@language":"en"}],"@id":"http://example.org/tree-ops-ext","url":"tree-ops-ext.csv","dc:title":"Tree Operations","dcat:keyword":["tree","street","maintenance"],"dc:publisher":[{"schema:name":"Example Municipality","schema:url":{"@id":"http://example.org"}}],"dc:license":{"@id":"http://opendefinition.org/licenses/cc-by/"},"dc:modified":{"@value":"2010-12-31","@type":"xsd:date"},"notes":[{"@type":"oa:Annotation","oa:hasTarget":{"@id":"http://example.org/tree-ops-ext"},"oa:hasBody":{"@type":"oa:EmbeddedContent","rdf:value":"This is a very interesting comment about the table; it's a table!","dc:format":{"@value":"text/plain"}}}],"dialect":{"trim":true},"tableSchema":{"columns":[{"name":"GID","titles":["GID","Generic Identifier"],"dc:description":"An identifier for the operation on a tree.","datatype":"string","required":true,"suppressOutput":true},{"name":"on_street","titles":"On Street","dc:description":"The street that the tree is on.","datatype":"string"},{"name":"species","titles":"Species","dc:description":"The species of the tree.","datatype":"string"},{"name":"trim_cycle","titles":"Trim Cycle","dc:description":"The operation performed on the tree.","datatype":"string","lang":"en"},{"name":"dbh","titles":"Diameter at Breast Ht","dc:description":"Diameter at Breast Height (DBH) of the tree (in feet), measured 4.5ft above ground.","datatype":"integer"},{"name":"inventory_date","titles":"Inventory Date","dc:description":"The date of the operation that was performed.","datatype":{"base":"date","format":"M/d/yyyy"}},{"name":"comments","titles":"Comments","dc:description":"Supplementary comments relating to the operation or tree.","datatype":"string","separator":";"},{"name":"protected","titles":"Protected","dc:description":"Indication (YES / NO) whether the tree is subject to a protection order.","datatype":{"base":"boolean","format":"YES|NO"},"default":"NO"},{"name":"kml","titles":"KML","dc:description":"KML-encoded description of tree location.","datatype":"xml"}],"primaryKey":"GID","aboutUrl":"http://example.org/tree-ops-ext#gid-{GID}"}}
Theannotated table generated from parsing the tabular data file and associated metadata is shown below and provides the basis for the conversion to JSON.
Core annotations for the resultingtableT, with 9columns and 3rows, are shown below:
"cavity or decay; trunk decay; codominant leaders; included bark; large leader or limb decay; previous failure root damage; root decay; beware of BEES"
"cavity or decay","trunk decay","codominant leaders","included bark","large leader or limb decay","previous failure root damage","root decay","beware of BEES"
The lists of values fromcells incolumnC7 ("name": "comments") are assumed to beunordered as the booleanordered annotation, which defaults tofalse, has not be set within the metadata description.
Minimal mode output for this example is provided below:
Example 7: http://example.org/tree-ops-ext-minimal.json
[{"@id":"http://example.org/tree-ops-ext#gid-1","on_street":"ADDISON AV","species":"Celtis australis","trim_cycle":"Large Tree Routine Prune","dbh":11,"inventory_date":"2010-10-18","protected":false,"kml":"<Point><coordinates>-122.156485,37.440963</coordinates></Point>"},{"@id":"http://example.org/tree-ops-ext#gid-2","on_street":"EMERSON ST","species":"Liquidambar styraciflua","trim_cycle":"Large Tree Routine Prune","dbh":11,"inventory_date":"2010-06-02","protected":false,"kml":"<Point><coordinates>-122.156749,37.440958</coordinates></Point>"},{"@id":"http://example.org/tree-ops-ext#gid-6","on_street":"ADDISON AV","species":"Robinia pseudoacacia","trim_cycle":"Large Tree Routine Prune","dbh":29,"inventory_date":"2010-06-01","comments":["cavity or decay","trunk decay","codominant leaders","included bark","large leader or limb decay","previous failure root damage","root decay","beware of BEES"],"protected":true,"kml":"<Point><coordinates>-122.156299,37.441151</coordinates></Point>"}]
Note
Thesubject described by eachrow is explicitly defined using theabout URL annotation; e.g. thesubject ofrowR1 ishttp://example.org/tree-ops-ext#gid-1.
CellsC1.7 andC2.7 (rowsR1 andR2;column,{ "name": "comments" }) havenullvalues - no output is included for thesecells.
CellC3.7 (rowR3;column,{ "name": "comments" }) contains a sequence of values; the set of values are included in anarray.
Standard mode output for this example is provided below:
Example 8: http://example.org/tree-ops-ext-standard.json
{"tables":[{"@id":"http://example.org/tree-ops-ext","url":"http://example.org/tree-ops-ext.csv","dc:title":"Tree Operations","dcat:keyword":["tree","street","maintenance"],"dc:publisher":[{"schema:name":"Example Municipality","schema:url":"http://example.org"}],"dc:license":"http://opendefinition.org/licenses/cc-by/","dc:modified":"2010-12-31","notes":[{"@type":"oa:Annotation","oa:hasTarget":"http://example.org/tree-ops-ext","oa:hasBody":{"@type":"oa:EmbeddedContent","rdf:value":"This is a very interesting comment about the table; it's a table!","dc:format":"text/plain"}}],"row":[{"url":"http://example.org/tree-ops-ext.csv#row=2","rownum":1,"describes":[{"@id":"http://example.org/tree-ops-ext#gid-1","on_street":"ADDISON AV","species":"Celtis australis","trim_cycle":"Large Tree Routine Prune","dbh":11,"inventory_date":"2010-10-18","protected":false,"kml":"<Point><coordinates>-122.156485,37.440963</coordinates></Point>"}]},{"url":"http://example.org/tree-ops-ext.csv#row=3","rownum":2,"describes":[{"@id":"http://example.org/tree-ops-ext#gid-2","on_street":"EMERSON ST","species":"Liquidambar styraciflua","trim_cycle":"Large Tree Routine Prune","dbh":11,"inventory_date":"2010-06-02","protected":false,"kml":"<Point><coordinates>-122.156749,37.440958</coordinates></Point>"}]},{"url":"http://example.org/tree-ops-ext.csv#row=4","rownum":3,"describes":[{"@id":"http://example.org/tree-ops-ext#gid-6","on_street":"ADDISON AV","species":"Robinia pseudoacacia","trim_cycle":"Large Tree Routine Prune","dbh":29,"inventory_date":"2010-06-01","comments":["cavity or decay","trunk decay","codominant leaders","included bark","large leader or limb decay","previous failure root damage","root decay","beware of BEES"],"protected":true,"kml":"<Point><coordinates>-122.156299,37.441151</coordinates></Point>"}]}]}]}
Note
TableT ({ "url": "http://example.org/tree-ops-ext.csv"}) has been explicitly identified:{ "@id": "<http://exmple.org/tree-ops-ext>"}.
Non-core annotations andnotes specified fortableT ({ "url": "http://example.org/tree-ops-ext.csv"}) are included in the output.
6.3Example with single table and usingvirtual columns to produce multiple subjects per row
This example uses a singleannotated table describing a listing of music events. Eachrow from the tabular data file corresponds to three resources; the music event itself, the location where that event occurs and the offer to sell tickets for that event. The goal is to convert the CSV content intoschema.org markup that a search engine such asGoogle can use to index music events. Details of how Google expects this information to be structured can be foundhere.
The input tabular data file, published athttp://example.org/events-listing.csv, and the associated metadata descriptionhttp://example.org/events-listing.csv-metadata.json are provided below:
The CSV to JSON translation is limited to providing one statement, or triple, percolumn in thetable. The targetschema.org markup requires 10 statements to describe each event. As the base tabular data file contains 5 columns, an additional 5virtualcolumns have been added in order to provide for the full complement of statements—including the relationships between the 3 resources (event, location, and offer) described by eachrow of thetable. Note that thevirtual annotation istrue for thesevirtualcolumns.
Furthermore, note that no attempt is made to reconcile between locations or offers that may be associated with more than one event; everyrow in thetable will create both a new location resource and offer resource in addition to the event resource. If considered necessary, applications such asOpenRefine may be used to identify and reconcile duplicate location resources once the JSON output has been generated.
Theannotated table generated from parsing the tabular data file and associated metadata is shown below and provides the basis for the conversion to JSON.
Annotations for the resultingtableT, with 10columns and 2rows, are shown below:
ColumnsC6,C7 andC8 ({ "name": "type_event"},{ "name": "type_place"} and{ "name": "type_offer"}) define the semantic types of the resources described by eachrow:schema:MusicEvent,schema:Place andschema:Offer respectively—noting that the use ofrdf:type is converted to thename@type (as used in [json-ld]) by this conversion application.
ColumnC9 ({ "name": "location"}) uses theabout URL,property URL andvalue URL to assert the relationship between the event and location resources.
The resources described by eachrow are explicitly defined using theabout URL annotation—in this case three resources perrow (event, location, and offer). Theobjects containing the name-values pairs associated with thecell values of arow are related to theobject for eachsubject in thatrow using the name-value pair withnamedescribes.
6.4Example with table group comprising four interrelated tables
This example is based onUse Case #4 - Publication of public sector roles and salaries and uses fourannotated tables published as agroup of tables. Information about senior roles and junior roles within a government department or organization are published in CSV format by each department. These are validated against a centrally publishedschema to ensure that all the data published by departments is consistent. Additionally, lists of organizations and professions are also published centrally, providingcontrolled vocabularies against which departmental submissions are validated.
Information published about junior and senior roles provides summary information for each post within the government department or organization. Whilst the junior role information is anonymous, providing only an indication of the number of full-time-equivalent (FTE) staff occupying a given post, the senior role information specifies the named individual occupying each post. As such, eachrow from the tabular data file describing senior roles corresponds to two resources; thepost and theperson occupying that post.
This example is concerned only with converting the information provided by each government department or organization rather than the centrally published information listing organizations and professions.
The input tabular data files and associated metadata descriptions are provided below:
Example 13: http://example.org/gov.uk/data/organizations.csv
Organization Unique Reference,Organization Name,Department Referencehefce.ac.uk,Higher Education Funding Council for England,bis.gov.ukbis.gov.uk,"Department for Business, Innovation and Skills",xx
Example 14: http://example.org/gov.uk/data/professions.csv
Post Unique Reference,Name,Grade,Job Title,Reports to Senior Post,Profession,Organization Reference90115,Steve Egan,SCS1A,Deputy Chief Executive,90334,Finance,hefce.ac.uk90334,Sir Alan Langlands,SCS4,Chief Executive,xx,Policy,hefce.ac.uk
Example 16: http://example.org/junior-roles.csv
Reporting Senior Post,Grade,Payscale Minimum (£),Payscale Maximum (£),Generic Job Title,Number of Posts (FTE),Profession,Organization Reference90115,4,17426,20002,Administrator,8.67,Operational Delivery,hefce.ac.uk90115,5,19546,22478,Administrator,0.5,Operational Delivery,hefce.ac.uk
Example 20: http://example.org/gov.uk/schema/senior-roles.json
{"@id":"http://example.org/gov.uk/schema/senior-roles.json","@context":"http://www.w3.org/ns/csvw","columns":[{"name":"ref","titles":"Post Unique Reference","datatype":"string","required":true,"propertyUrl":"dc:identifier"},{"name":"name","titles":"Name","datatype":"string","aboutUrl":"http://example.org/organization/{organizationRef}/person/{_row}","propertyUrl":"foaf:name"},{"name":"grade","titles":"Grade","datatype":"string","propertyUrl":"http://example.org/gov.uk/def/grade"},{"name":"job","titles":"Job Title","datatype":"string","propertyUrl":"http://example.org/gov.uk/def/job"},{"name":"reportsTo","titles":"Reports to Senior Post","datatype":"string","null":"xx","propertyUrl":"org:reportsTo","valueUrl":"http://example.org/organization/{organizationRef}/post/{reportsTo}"},{"name":"profession","titles":"Profession","datatype":"string","propertyUrl":"http://example.org/gov.uk/def/profession"},{"name":"organizationRef","titles":"Organization Reference","datatype":"string","propertyUrl":"org:postIn","valueUrl":"http://example.org/organization/{organizationRef}","required":true},{"name":"post_holder","virtual":true,"propertyUrl":"org:heldBy","valueUrl":"http://example.org/organization/{organizationRef}/person/{_row}"}],"primaryKey":"ref","aboutUrl":"http://example.org/organization/{organizationRef}/post/{ref}","foreignKeys":[{"columnReference":"reportsTo","reference":{"schemaReference":"http://example.org/gov.uk/schema/senior-roles.json","columnReference":"ref"}},{"columnReference":"profession","reference":{"schemaReference":"http://example.org/gov.uk/schema/professions.json","columnReference":"name"}},{"columnReference":"organizationRef","reference":{"schemaReference":"http://example.org/gov.uk/schema/organizations.json","columnReference":"ref"}}]}
Example 21: http://example.org/gov.uk/schema/junior-roles.json
{"@id":"http://example.org/gov.uk/schema/junior-roles.json","@context":"http://www.w3.org/ns/csvw","columns":[{"name":"reportsToSenior","titles":"Reporting Senior Post","datatype":"string","propertyUrl":"org:reportsTo","valueUrl":"http://example.org/organization/{organizationRef}/post/{reportsToSenior}","required":true},{"name":"grade","titles":"Grade","datatype":"string","propertyUrl":"http://example.org/gov.uk/def/grade"},{"name":"min_pay","titles":"Payscale Minimum (£)","datatype":"integer","propertyUrl":"http://example.org/gov.uk/def/min_pay"},{"name":"max_pay","titles":"Payscale Maximum (£)","datatype":"integer","propertyUrl":"http://example.org/gov.uk/def/max_pay"},{"name":"job","titles":"Generic Job Title","datatype":"string","propertyUrl":"http://example.org/gov.uk/def/job"},{"name":"number","titles":"Number of Posts (FTE)","datatype":"number","propertyUrl":"http://example.org/gov.uk/def/number_of_posts"},{"name":"profession","titles":"Profession","datatype":"string","propertyUrl":"http://example.org/gov.uk/def/profession"},{"name":"organizationRef","titles":"Organization Reference","datatype":"string","propertyUrl":"org:postIn","valueUrl":"http://example.org/organization/{organizationRef}","required":true}],"foreignKeys":[{"columnReference":"reportsToSenior","reference":{"schemaReference":"http://example.org/gov.uk/schema/senior-roles.json","columnReference":"ref"}},{"columnReference":"profession","reference":{"schemaReference":"http://example.org/gov.uk/schema/professions.json","columns":"name"}},{"columnReference":"organizationRef","reference":{"schemaReference":"http://example.org/gov.uk/schema/organizations.json","columns":"ref"}}]}
Note
This example makes extensive use of theexample.org domain. As described in [RFC6761], this domain is used for illustrative examples within documentation. In reality, the resources described here with the URL path/gov.uk would be centrally published by the UK Government at, say, the domaindata.gov.uk.
Given that these resources are centrally published with an aspiration for reuse, theschema descriptions have been factored out into separate resources. As such, the top-level metadata description resourcemetadata.json simply provides the list oftables and binds each of them to the appropriateschema that is defined elsewhere.
Finally, note that because the centrally published metadata descriptions are intended to be reused across many government departments and organizations, extra consideration has been given to defining URIs for theperson andpost resources defined in each row of the senior roles tabular data and subsequently referenced from the junior roles tabular data. To ensure that naming clashes are avoided, the unique reference for the organization to which theperson orpost belongs has been included in a path segment of the identifier. For example, theURI template propertyaboutUrl used to identify the seniorpost is specified ashttp://example.org/organization/{organizationRef}/post/{ref}, thus yielding the URIhttp://example.org/organization/hefce.ac.uk/post/90115 for thepost described in the firstrow of the senior roles tabular data.
Thegroup of tables generated from parsing the tabular data files and associated metadata is shown below and provides the basis for the conversion to JSON.
Annotations for thegroup of tablesG and the fourtablesTa,Tb,Tc, andTd are shown below:
In this example, output for the centrally published lists of organizations and professions, tablesTa andTb (http://example.org/gov.uk/data/organizations.csv andhttp://example.org/gov.uk/data/professions.csv respectively), are not required; only information from the departmental submissions is to be translated to RDF. Note thesuppress output annotation on thistable.
ColumnCc8, with thevirtual annotation specified astrue, is used to relate theperson resource, whose name is provided incolumnCc2, to the associatedpost resource within the currentrow oftableTc ({ "url": "http://example.org/senior-roles.csv" }).
Notice thatvalue URL is not specified forcellsCa2.3 andCc2.5 because in each case thecell value isnull and thevirtual annotation ofcolumnCb5 isnot defined.
Minimal mode output for this example is provided below:
Example 22: http://example.org/public-sector-roles-and-salaries-minimal.json
[{"@id":"http://example.org/organization/hefce.ac.uk/post/90115","dc:identifier":"90115","org:heldBy":{"@id":"http://example.org/organization/hefce.ac.uk/person/1","foaf:name":"Steve Egan"},"http://example.org/gov.uk/def/grade":"SCS1A","http://example.org/gov.uk/def/job":"Deputy Chief Executive","org:reportsTo":"http://example.org/organization/hefce.ac.uk/post/90334","http://example.org/gov.uk/def/profession":"Finance","org:postIn":"http://example.org/organization/hefce.ac.uk"},{"@id":"http://example.org/organization/hefce.ac.uk/post/90334","dc:identifier":"90334","org:heldBy":{"@id":"http://example.org/organization/hefce.ac.uk/person/2","foaf:name":"Sir Alan Langlands"},"http://example.org/gov.uk/def/grade":"SCS4","http://example.org/gov.uk/def/job":"Chief Executive","http://example.org/gov.uk/def/profession":"Policy","org:postIn":"http://example.org/organization/hefce.ac.uk"},{"org:reportsTo":"http://example.org/organization/hefce.ac.uk/post/90115","http://example.org/gov.uk/def/grade":"4","http://example.org/gov.uk/def/min_pay":17426,"http://example.org/gov.uk/def/max_pay":20002,"http://example.org/gov.uk/def/job":"Administrator","http://example.org/gov.uk/def/number_of_posts":8.67,"http://example.org/gov.uk/def/profession":"Operational Delivery","org:postIn":"http://example.org/organization/hefce.ac.uk"},{"org:reportsTo":"http://example.org/organization/hefce.ac.uk/post/90115","http://example.org/gov.uk/def/grade":"5","http://example.org/gov.uk/def/min_pay":19546,"http://example.org/gov.uk/def/max_pay":22478,"http://example.org/gov.uk/def/job":"Administrator","http://example.org/gov.uk/def/number_of_posts":0.5,"http://example.org/gov.uk/def/profession":"Operational Delivery","org:postIn":"http://example.org/organization/hefce.ac.uk"}]
Output fortablesTa andTb ({ "url": "http://example.org/gov.uk/data/organizations.csv" } and{ "url": "http://example.org/gov.uk/data/professions.csv" }) are not included as thesuppress output annotation has the valuetrue for each of thetables.
ColumnsCc5 andCd1 ({ "name": "reportsTo" } and{ "name": "reportsToSenior" }) use theabout URL,property URL andvalue URL annotations to assert the relationship between the post described by a givenrow and the senior post to which it reports. However, since senior posts and junior posts are described in different tables it is not possible to create nestedobjects for this particular case.
Similarly,columnsCc7 andCd8 (both with{ "name": "organizationRef" }) use theabout URL,property URL andvalue URL annotations to assert the relationship between the post described by a givenrow and the organization to which it belongs.
Finally, note that two resources are created for eachrow withintableTc ({ "url": "http://example.org/senior-roles.csv" }): theperson and thepost they occupy. The relationship between these resources is specified viavirtualcolumnCc8 ({ "name": "post_holder" }) using theabout URL,property URL andvalue URL annotations. Thepersonobject provides the value of the name-value pair with correspondingnameorg:heldBy, thus nesting thepersonobject within thepostobject.
Standard mode output for this example is provided below:
Example 23: http://example.org/public-sector-roles-and-salaries-standard.json
{"tables":[{"url":"http://example.org/senior-roles.csv","row":[{"url":"http://example.org/senior-roles.csv#row=2","rownum":1,"describes":[{"@id":"http://example.org/organization/hefce.ac.uk/post/90115","dc:identifier":"90115","org:heldBy":{"@id":"http://example.org/organization/hefce.ac.uk/person/1","foaf:name":"Steve Egan"},"http://example.org/gov.uk/def/grade":"SCS1A","http://example.org/gov.uk/def/job":"Deputy Chief Executive","org:reportsTo":"http://example.org/organization/hefce.ac.uk/post/90334","http://example.org/gov.uk/def/profession":"Finance","org:postIn":"http://example.org/organization/hefce.ac.uk"}]},{"url":"http://example.org/senior-roles.csv#row=3","rownum":2,"describes":[{"@id":"http://example.org/organization/hefce.ac.uk/post/90334","dc:identifier":"90334","org:heldBy":{"@id":"http://example.org/organization/hefce.ac.uk/person/2","foaf:name":"Sir Alan Langlands"},"http://example.org/gov.uk/def/grade":"SCS4","http://example.org/gov.uk/def/job":"Chief Executive","http://example.org/gov.uk/def/profession":"Policy","org:postIn":"http://example.org/organization/hefce.ac.uk"}]}]},{"url":"http://example.org/junior-roles.csv","row":[{"url":"http://example.org/junior-roles.csv#row=2","rownum":1,"describes":[{"org:reportsTo":"http://example.org/organization/hefce.ac.uk/post/90115","http://example.org/gov.uk/def/grade":"4","http://example.org/gov.uk/def/min_pay":17426,"http://example.org/gov.uk/def/max_pay":20002,"http://example.org/gov.uk/def/job":"Administrator","http://example.org/gov.uk/def/number_of_posts":8.67,"http://example.org/gov.uk/def/profession":"Operational Delivery","org:postIn":"http://example.org/organization/hefce.ac.uk"}]},{"url":"http://example.org/junior-roles.csv#row=3","rownum":2,"describes":[{"org:reportsTo":"http://example.org/organization/hefce.ac.uk/post/90115","http://example.org/gov.uk/def/grade":"5","http://example.org/gov.uk/def/min_pay":19546,"http://example.org/gov.uk/def/max_pay":22478,"http://example.org/gov.uk/def/job":"Administrator","http://example.org/gov.uk/def/number_of_posts":0.5,"http://example.org/gov.uk/def/profession":"Operational Delivery","org:postIn":"http://example.org/organization/hefce.ac.uk"}]}]}]}
A.Acknowledgements
At the time of publication, the following individuals had participated in the Working Group, in the order of their first name: Adam Retter, Alf Eaton, Anastasia Dimou, Andy Seaborne, Axel Polleres, Christopher Gutteridge, Dan Brickley, Davide Ceolin, Eric Stephan, Erik Mannens, Gregg Kellogg, Ivan Herman, Jeni Tennison, Jeremy Tandy, Jürgen Umbrich, Rufus Pollock, Stasinos Konstantopoulos, William Ingram, and Yakov Shafranovich.
B.Changes since previous versions
B.1Changes since candidate recommendation of 16 July 2015
Clarification of behaviour when creating JSON output from a row where multiple cells that share the sameproperty URL annotation and contain lists of values. The JSON output will contain a name-value pair whose name is derived from theproperty URL annotation and whose value is a single array containing the values from all contributing cells. (Seeissue 755)
B.2Changes since the working draft of 16 April 2015
Thename annotation is URI decoded before used as a property.
A note has been added to thesection on datatype making it clear that the new@id annotation in the model is ignored for JSON.
Removed the separate section on "Mapping Core Tabular Data" (to be in line with the latest version of thetabular model specification).
Reformulated the mappings in terms of table "annotations" (i.e., based on thetabular model) rather than table "properties" (i.e., not based on the specificities of themetadata vocabulary).
Provided a set ofstandard mappings from the general datatypes and the corresponding JSON types.