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.
Status of This Document
This section describes the status of this document at the time of its publication. Other documents may supersede this document. A list of current W3C publications and the latest revision of this technical report can be found in the W3C technical reports index at http://www.w3.org/TR/.
The CSV on the Web Working Group was chartered 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 has been reviewed by W3C Members, by software developers, and by other W3C
groups and interested parties, and is endorsed by the Director as a W3C Recommendation.
It is a stable document and may be used as reference material or cited from another
document. W3C's role in making the Recommendation is to draw attention to the
specification and to promote its widespread deployment. This enhances the functionality
and interoperability of the Web.
This document describes the processing of tabular data to create a set of nested objects that MUST 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 the annotated 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 of parsing tabular data.
Conversion applications MUST provide at least two modes of operation: standard and minimal.
Standard mode conversion frames the information gleaned from the cells of the tabular data with details of the rows, tables, and a group of tables within which that information is provided.
Minimal mode conversion includes only the information gleaned from the cells of the tabular 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 words MAY, MUST, and SHOULD are
to be interpreted as described in [RFC2119].
Tabular dataMUST conform to the description from [tabular-data-model]. In particular note that each rowMUST contain the same number of cells (although some of these cells 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 definition in 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 definition in 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.
4. Converting Tabular Data to JSON
The procedures for converting tabular data into JSON are described below for both standard and minimal modes.
An array is defined in JSON ([RFC7159]) as an ordered sequence of zero or more values, where a value is a string, number, boolean, null, object, or array.
In the context of this specification, name is used as defined in JSON ([RFC7159]); that is, that name is a string that provides a unique key within a set of name-value pairs within a JSON object.
An object is defined in JSON ([RFC7159]) as an unordered collection of zero or more name-value pairs, where name is a string and value is a string, number, boolean, null, object, or array.
A conformant JSON conversion application MUST produce output conforming to this algorithm according to the chosen mode of conversion: standard or minimal.
The [tabular-data-model] specifies that string values within tabular data (such as column titles or cell 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.1 Minimal mode
The steps in the algorithm defined here apply to minimal mode.
Insert an empty arrayA into the JSON output. The objects containing the name-value pairs associated with the cell values will be subsequently inserted into this array.
The subject(s) described by each row are determined according to the about URL annotation for each cell in the current row. Where about URL is undefined, a default subject for the row is used.
Insert the following name-value pair into objectT:
name
row
value
AR
where AR is an array into which the objects describing the rows will be subsequently inserted.
Each row within the table is processed sequentially in order. For each row in the current table:
Insert an empty objectR into the arrayAR to represent the row.
Specify the row numbern for the row; insert the following name-value pair into objectR:
name
rownum
value
n
Specify the row source numbernsource for the row within the source tabular data file URL using a fragment-identifier as specified in [RFC7111]; if row source number is not null, insert the following name-value pair into objectR:
name
url
value
URL#row=nsource
Specify any titles for the row; if row titles is not null, insert the following name-value pair into objectR:
name
titles
value
t
where t is the single value or array of values provided by the row titles annotation.
Note
JSON has no native support for expressing language information; therefore any such information associated with the row titles is ignored.
The subject(s) described by each row are determined according to the about URL annotation for each cell in the current row. Where about URL is undefined, a default subject for the row is used.
The steps in the algorithm defined here apply to both standardandminimal modes.
This algorithm generates a sequence of objects, S1 to Sn, each of which corresponds to a subject described by the current row. The algorithm inserts name-value pairs into Si depending on the cell values as outlined in the following steps.
Determine the unique subjects for the current row. The subject(s) described by each row are determined according to the about URL annotation for each cell in the current row. A default subject for the row is used for any cells where about URL is undefined.
(i is the index number with values from 1 to n, where n is the number of subjects for the row)
Subjecti is identified according to the about URL annotation of its associated cells: IS. For a defaultsubject where about URL is not specified by its cells, IS is null.
If the identifier for subjecti, IS, is not null, then insert the following name-value pair into objectSi:
name
@id
value
IS
Each cell referring to subjecti is then processed sequentially according to the order of the columns.
For each cell referring to subjecti, where the suppress output annotation for the column associated with that cell is false, insert a name-value pair into objectSi as described below:
Else, nameN takes the URI decoded value of the name annotation for the column associated with the cell. (URI decoding is necessary as name may have been encoded if it was taken from a supplied title.)
If the value URL for the current cell is not null, then insert the following name-value pair into objectSi:
name
N
value
Vurl
where Vurl is the value of value URL annotation for the current cell expressed as a string in the JSON output. If N is @type, compact Vurl according to the rules as defined in URL Compaction in [tabular-metadata].
Else, if the cell value is a list that is not empty, then the cell value provides a sequence of values for inclusion within the JSON output; insert an arrayAv containing each value V of the sequence into objectSi:
name
N
value
Av
Each of the values V derived from the sequence MUST be expressed in the JSON output according to the datatype of V as defined below in section 4.5Interpreting datatypes.
Note
Since arrays are implicitly ordered in JSON, the ordered annotation has no effect on the JSON output.
Else, if the cell value is not null, then the cell value provides a single value V for inclusion within the JSON output; insert the following name-value pair into objectSi:
If nameN occurs more than once within objectSi, the name-value pairs from each occurrence of nameNMUST be compacted to form a single name-value pair with nameN 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.4 Generating Nested Objects
The steps in the algorithm defined herein apply to both standardandminimal modes.
Where the current row describes multiple subjects, it MAY be possible to organize the objects associated with those subjects such that some objects are nested within others; e.g. where the value URL annotation for one cell matches the about URL annotation for another cell in the same row. This algorithm considers a sequence of objects generated according to 4.3Generating Objects, S1 to Sn, each of which corresponds to a subject described by the current row. It generates a new sequence of rootobjects, SR1 to SRm, that MAY include nestedobjects.
Note
Where the current row describes only a single subject, this algorithm may be bypassed as no nesting is possible. In such a case, the rootobjectSR1 is identical to the original objectS1.
Note
This nesting algorithm is based on the interrelationships between subjects described within a given row that are specified using the value URL annotation. Cell values expressing the identity of a subject in the current row (i.e., as a simple literal) will be ignored by this algorithm.
The algorithm uses the following terms:
child
If two vertices are connected in a tree, the one which is further away from the root of the tree is referred to as the child of the other.
descendant
A vertexN is a descendant of a vertexM if either N is the childM, or there are verticesV1,…,Vk, such that V1=M, Vk=N, and Vk+1 is a child of Vk.
edge
One of the main constituents of graphs; edges are used to establish relationships among vertices. In the context of this algorithm, edges are expressed in JSON using a name-value pair whose value is another object or an array of objects.
forest
A collection of disjoint trees. For the purpose of this algorithm, the order of trees is important, i.e., forests can also be viewed as an ordered sequence of roots.
graph
Data structure consisting of vertices (or "nodes") and edges. See, for example, [Knuth] for further details.
A tree (or rooted tree) is a connected, acyclic graph where one vertex has been designated as the root, in which case the edges have a natural orientation towards or away from the root.
vertex
One of the main constituents of graphs; a vertex usually holds further information or data. In the context of this algorithm, vertices are used to represent the JSON objects.
The nesting algorithm is defined as follows:
For all cells in the current row, determine the value URLs, Vurl, that occur only once. The list of these uniquely occurring value URLs is referred to as the URL-list.
Determine the identity of objectSi: IS. If present in objectSi, the name-value pair with name@id provides the value of IS. Else, objectSi is not explicitly identified and IS is null.
For all cells associated with the current objectSi (e.g. whose about URL annotation matches IS):
If the value URL annotation of the current cell is defined and its value, Vurl, appears in the URL-list, then check each of the otherobjects in the sequence S1 to Sn to determine if Vurl identifies one of those objects.
For objectSj, if the name-value pair with name@id is present and its value matches Vurl, then:
If the root of the tree containing vertexN is a vertex that represents objectSj, then objectSi is already a descendant of objectSj; no further action SHOULD be taken for this instance of Vurl.
Note
This clause in the algorithm prevents circular loops from being created.
Furthermore, because the URL-list contains value URLs that occur only once for the current row, objectSi cannot be a descendant of intermediate vertices in the tree.
Each vertex in forestF represents an object in the original sequence of objectsS1 to Sn and is associated with a subject described by the current row. Rearrange objectsS1 to Sn such that they mirror the structure of the trees in forestF as follows: If vertexM, representing objectSi, is a child of vertexN, representing objectSj, then the name-value pair in objectSj associated with the edge relating M and NMUST be modified such that the (literal) value, Vurl, from that name-value pair is replaced by objectSi thus creating a nestedobject.
Instances of JSON reserved characters within string values MUST be escaped as defined in [RFC7159].
JSON has no native support for expressing language information; therefore the language of a value has no effect on the JSON output.
Note
Only the base annotation value is used to determine the primitive type used within the JSON output. Additional restrictions to the cell value's datatype, such as the id annotation, are ignored for the purposes of conversion to JSON.
Note
A datatype'sformat is irrelevant to the conversion procedure defined in this specification; the cell value has already been parsed from the contents of the cell according to the format annotation.
Cell errorsMUST be recorded by applications where the contents of a cell cannot be parsed or validated (see Parsing Cells and Validating Tables in [tabular-data-model] respectively). In cases where cell 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 create annotated 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 from notes and non-core annotations annotations are generally copied verbatim from the metadata description subject to the exceptions below:
Name-value pairs whose value is an object 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 an object 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.1 Simple example
This example comprises a single annotated table containing information attributes about countries; country code, position (latitude, longitude) and name. Whilst the input tabular data file, published at http://example.org/countries.csv, includes a header line, no further metadata annotations are given. The tabular data file is provided below:
Example 2: http://example.org/countries.csv
countryCode,latitude,longitude,name
AD,42.5,1.6,Andorra
AE,23.4,53.8,"United Arab Emirates"
AF,33.9,67.7,Afghanistan
The annotated table generated from parsing the tabular data file is shown below and provides the basis for the conversion to JSON.
Annotations for the resulting tableT, with 4 columns and 3 rows, are shown below:
id
core annotations
url
columns
rows
T
http://example.org/countries.csv
C1, C2, C3, C4
R1, R2, R3
Annotations for the columns, rows and cells in tableT are shown in the tables 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
The about URL annotation has not been set for cells in table T ({ "url": "http://example.org/countries.csv"}); cells in a given row where about URL has not been specified are assumed to refer to the same subject and so the name-value pairs associated with the cell values of that row occur within the same object.
Given that the property URL is null for cells in table T ({ "url": "http://example.org/countries.csv"}), the simplified name is used in the name-value pairs; e.g. countryCode rather than http://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 with nameurl provides reference to the original tabular data file and to specific rows therein.
The row number is provided for each row using name-value pair with namerownum.
The object containing the name-values pairs associated with the cell values of a row are related to the object for that row using the name-value pair with namedescribes.
6.2 Example with single table and rich annotations
This example is based on Use Case #11 - City of Palo Alto Tree Data and comprises a single annotated table describing an inventory of tree maintenance operations. The input tabular data file, published at http://example.org/tree-ops-ext.csv, and the associated metadata description http://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,KML
1,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}"}}
The annotated 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 resulting tableT, with 9 columns and 3 rows, 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 from cells in columnC7 ("name": "comments") are assumed to be unordered as the boolean ordered annotation, which defaults to false, 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
The subject described by each row is explicitly defined using the about URL annotation; e.g. the subject of rowR1 is http://example.org/tree-ops-ext#gid-1.
CellsC1.7 and C2.7 (rowsR1 and R2; column, { "name": "comments" }) have nullvalues - no output is included for these cells.
CellC3.7 (rowR3; column, { "name": "comments" }) contains a sequence of values; the set of values are included in an array.
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 and notes specified for tableT ({ "url": "http://example.org/tree-ops-ext.csv"}) are included in the output.
6.3 Example with single table and using virtual columns to produce multiple subjects per row
This example uses a single annotated table describing a listing of music events. Each row 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 into schema.org markup that a search engine such as Google can use to index music events. Details of how Google expects this information to be structured can be found here.
The input tabular data file, published at http://example.org/events-listing.csv, and the associated metadata description http://example.org/events-listing.csv-metadata.json are provided below:
The CSV to JSON translation is limited to providing one statement, or triple, per column in the table. The target schema.org markup requires 10 statements to describe each event. As the base tabular data file contains 5 columns, an additional 5 virtual columns 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 each row of the table. Note that the virtual annotation is true for these virtual columns.
Furthermore, note that no attempt is made to reconcile between locations or offers that may be associated with more than one event; every row in the table will create both a new location resource and offer resource in addition to the event resource. If considered necessary, applications such as OpenRefine may be used to identify and reconcile duplicate location resources once the JSON output has been generated.
The annotated 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 resulting tableT, with 10 columns and 2 rows, are shown below:
id
core annotations
url
columns
rows
T
http://example.org/events-listing.csv
C1, C2, C3, C4, C5, C6, C7, C8, C9, C10
R1, R2
Annotations for the columns, rows and cells in tableT are shown in the tables below:
ColumnsC6, C7 and C8 ({ "name": "type_event"}, { "name": "type_place"} and { "name": "type_offer"}) define the semantic types of the resources described by each row: schema:MusicEvent, schema:Place and schema:Offer respectively—noting that the use of rdf:type is converted to the name@type (as used in [json-ld]) by this conversion application.
ColumnC9 ({ "name": "location"}) uses the about URL, property URL and value URL to assert the relationship between the event and location resources.
The resources described by each row are explicitly defined using the about URL annotation—in this case three resources per row (event, location, and offer). The objects containing the name-values pairs associated with the cell values of a row are related to the object for each subject in that row using the name-value pair with namedescribes.
6.4 Example with table group comprising four interrelated tables
This example is based on Use Case #4 - Publication of public sector roles and salaries and uses four annotated tables published as a group 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 published schema to ensure that all the data published by departments is consistent. Additionally, lists of organizations and professions are also published centrally, providing controlled 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, each row from the tabular data file describing senior roles corresponds to two resources; the post and the person 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 Reference
hefce.ac.uk,Higher Education Funding Council for England,bis.gov.uk
bis.gov.uk,"Department for Business, Innovation and Skills",xx
Example 14: http://example.org/gov.uk/data/professions.csv
Profession
Finance
Information Technology
Operational Delivery
Policy
Example 15: http://example.org/senior-roles.csv
Post Unique Reference,Name,Grade,Job Title,Reports to Senior Post,Profession,Organization Reference
90115,Steve Egan,SCS1A,Deputy Chief Executive,90334,Finance,hefce.ac.uk
90334,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 Reference
90115,4,17426,20002,Administrator,8.67,Operational Delivery,hefce.ac.uk
90115,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 the example.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 domain data.gov.uk.
Given that these resources are centrally published with an aspiration for reuse, the schema descriptions have been factored out into separate resources. As such, the top-level metadata description resource metadata.json simply provides the list of tables and binds each of them to the appropriate schema 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 the person and post 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 the person or post belongs has been included in a path segment of the identifier. For example, the URI template propertyaboutUrl used to identify the senior post is specified as http://example.org/organization/{organizationRef}/post/{ref}, thus yielding the URI http://example.org/organization/hefce.ac.uk/post/90115 for the post described in the first row of the senior roles tabular data.
The group 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 the group of tablesG and the four tablesTa, Tb, Tc, and Td are shown below:
In this example, output for the centrally published lists of organizations and professions, tables Ta and Tb (http://example.org/gov.uk/data/organizations.csv and http://example.org/gov.uk/data/professions.csv respectively), are not required; only information from the departmental submissions is to be translated to RDF. Note the suppress output annotation on this table.
The following foreign keys are defined:
id
columns in table
columns in referenced table
Fa1
Ca3
Ca1
Fc1
Cc5
Cc1
Fc2
Cc6
Cb1
Fc3
Cc7
Ca1
Fd1
Cd1
Cc1
Fd2
Cd7
Cb1
Fd3
Cd8
Ca1
Annotations for the columns, rows and cells in tableT are shown in the tables below:
ColumnCc8, with the virtual annotation specified as true, is used to relate the person resource, whose name is provided in columnCc2, to the associated post resource within the current row of tableTc ({ "url": "http://example.org/senior-roles.csv" }).
Notice that value URL is not specified for cellsCa2.3 and Cc2.5 because in each case the cell value is null and the virtual annotation of columnCb5 is not 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 for tablesTa and Tb ({ "url": "http://example.org/gov.uk/data/organizations.csv" } and { "url": "http://example.org/gov.uk/data/professions.csv" }) are not included as the suppress output annotation has the value true for each of the tables.
ColumnsCc5 and Cd1 ({ "name": "reportsTo" } and { "name": "reportsToSenior" }) use the about URL, property URL and value URL annotations to assert the relationship between the post described by a given row 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 nested objects for this particular case.
Similarly, columnsCc7 and Cd8 (both with { "name": "organizationRef" }) use the about URL, property URL and value URL annotations to assert the relationship between the post described by a given row and the organization to which it belongs.
Finally, note that two resources are created for each row within tableTc ({ "url": "http://example.org/senior-roles.csv" }): the person and the post they occupy. The relationship between these resources is specified via virtualcolumnCc8 ({ "name": "post_holder" }) using the about URL, property URL and value URL annotations. The personobject provides the value of the name-value pair with corresponding nameorg:heldBy, thus nesting the personobject within the postobject.
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.1 Changes since candidate recommendation of 16 July 2015
Clarification of behaviour when creating JSON output from a row where multiple cells that share the same property URL annotation and contain lists of values. The JSON output will contain a name-value pair whose name is derived from the property URL annotation and whose value is a single array containing the values from all contributing cells. (See issue 755)
B.2 Changes since the working draft of 16 April 2015
The name annotation is URI decoded before used as a property.
A note has been added to the section 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 the tabular model specification).
Reformulated the mappings in terms of table "annotations" (i.e., based on the tabular model) rather than table "properties" (i.e., not based on the specificities of the metadata vocabulary).
Provided a set of standard mappings from the general datatypes and the corresponding JSON types.