Querying JSON and NDJSON Files
This topic provides details about the structure to use when writing GDI queries to read or ingest data from JSON or NDJSON files. It also includes example queries that may be useful as a starting point for writing your own GDI queries.
Query Syntax
The following query syntax shows the structure of a GDI query for JSON sources. The clauses, patterns, and placeholders that are links are described below.
# PREFIX Clause PREFIX s: <http://cambridgesemantics.com/ontologies/DataToolkit#> PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#> PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> PREFIX owl: <http://www.w3.org/2002/07/owl#> PREFIX anzo: <http://openanzo.org/ontologies/2008/07/Anzo#> PREFIX zowl: <http://openanzo.org/ontologies/2009/05/AnzoOwl#> PREFIX dc: <http://purl.org/dc/elements/1.1/> # Result Clause { [ GRAPH ${targetGraph} { ] triple_patterns [ } ] } [ ${usingSources} ] WHERE { # SERVICE Clause: Include the following service call when reading or inserting data. SERVICE [ TOPDOWN ] <http://cambridgesemantics.com/services/DataToolkit> # View SERVICE Clause: Or use the service call below when constructing a view. SERVICE <http://cambridgesemantics.com/services/DataToolkitView>(${targetGraph}) { ?data a s:FileSource ; s:url "string" ; [ s:options [ file_storage_connection_options ] ; ] [ s:pattern "string" ; ] [ s:maxDepth int ; ] [ s:format [ source_format_options ; ] ; ] [ s:mimetype "string" ; ] [ s:username "string" ; ] [ s:password "string" ; ] [ s:timeout int ; ] [ s:batching boolean | int ; ] [ s:paging [ pagination_options ; ] [ s:concurrency int | [ list_of_properties ] ; ] [ s:rate int | "string" ; ] [ s:locale "string" ; ] [ s:sampling int ; ] [ s:selector "string" | [ list ] ; ] [ s:model "string" ; ] [ s:key ("string") ; ] [ s:reference [ s:model "string" ; s:using ("string") ] [ s:formats [ datatype_formatting_options ] ; ] [ s:normalize boolean | [ normalization_rules ] ; ] [ s:count ?variable ; ] [ s:offset int ; ] [ s:limit int ; ] # Mapping variables and hierarchical bindings ?mapping_variable ( [ "binding" ] [ datatype ] [ "datetime_format" ] ) ; ... ; . # Additional clauses such as BIND, VALUES, FILTER } }
For readability, the parameters below exclude the base URI <http://cambridgesemantics.com/ontologies/DataToolkit#> as well as the s: prefix. As shown in the examples, however, the s: prefix or full property URI does need to be included in queries.
| Option | Type | Description |
|---|---|---|
| PREFIX Clause | N/A |
The PREFIX clause declares the standard and custom prefixes for GDI service queries. Generally, queries include the prefixes from the query template (or a subset of them) plus any data-specific declarations.
|
| Result Clause | N/A | The result clause defines the type of SPARQL query to run and the set of results to return, i.e., whether you want to read (SELECT or CONSTRUCT) from the source or ingest the data into Anzo (INSERT). |
| GRAPH ${targetGraph} | N/A | Include the GRAPH keyword and target graph parameter ${targetGraph} when writing an INSERT query to ingest data into a graphmart. Anzo automatically populates the query with the appropriate target URIs when the query runs. |
| ${usingSources} | N/A | Include the source graph parameter ${usingSources} when writing a "topdown" query that passes values from the data that is in the graphmart to the data source. Anzo automatically populates the query with the appropriate FROM clauses when the query runs. When passing literal values to the remote source, you do not need to include the source graph parameter. The SERVICE Clause description below includes more information about passing input to data sources. |
| SERVICE Clause | N/A |
Include the SERVICE call
SERVICE [ TOPDOWN ] <http://cambridgesemantics.com/services/DataToolkit> to invoke the GDI service when you are running a SELECT, INSERT, or CONSTRUCT query that is not creating a view. When writing a CONSTRUCT query in a View Step, use the DataToolkitView service call, as described below in View SERVICE Clause. Include the optional TOPDOWN keyword when you want to pass input values from the graphmart to the data source. When you include TOPDOWN in the service call, it indicates that the rest of the query produces values to send to the source. In this case, the GDI makes repeated calls to pass in each of the specified values and retrieve the data that is based on those values. |
| View SERVICE Clause | N/A |
When writing a CONSTRUCT query that creates a view of the data (usually in a View Step), include the following SERVICE call:
SERVICE <http://cambridgesemantics.com/services/DataToolkitView>(${targetGraph}). Using the DataToolkitView call optimizes query execution because it tells the GDI to inspect the query and determine which filters to push to the data source. It also limits the result set and retrieves only the data that is needed, i.e., the source data is fully mapped but all of the mapped data is not necessarily returned. |
| url | string | This property specifies the file system location of the source file or directory of files. When specifying a directory (such as s:url "/opt/shared-files/loads/"), the GDI loads all of the file formats it recognizes. To specify a directory but limit the number or type of files that are read, you can include the pattern and/or maxDepth properties. |
| options | RDF list |
If additional connection information needs to be provided to access the file storage system, include the
options property to list any storage-specific connection parameters. See File Storage Connection Options for information about the supported properties for each storage type. |
| pattern | string |
This property is used to specify a wildcard pattern for matching file names. For example,
s:pattern "common_prefix*.json". You can include one s:pattern property per FileSource. The GDI supports Unix file globbing syntax outside of parentheses. Within parentheses, full Java regular expression language is supported. For example, including s:pattern "data/**/customer_*.json" tells the GDI to load all files that match the pattern "customer_*.json" from any number of subdirectories under the data directory. Similarly s:pattern "(\d+)/transaction_*.json" tells the GDI to load all files that match the pattern "transaction_*.json" in all subdirectories. |
| maxDepth | int |
This property can be used to limit the directory traversal depth. By default, when
s:url specifies a directory (and a s:pattern that limits that traversal depth is not specified), all subdirectories are processed. To process only the files in the top level directory, set maxDepth to 0 (s:maxDepth 0). To process the files in the top level directory plus the first-level subdirectories, set maxDepth to 1 (s:maxDepth 1), and so on. |
| format | RDF list |
You can include the
format property to add parameters that describe the source files. See File Source Format Options for details about the supported parameters. |
| mimetype | string | This property can be included to specify the MIME type of the data. |
| username | string |
If authentication is required to access the source, include this property to specify the user name.
|
| password | string |
This property lists the password for the given username.
|
| timeout | int |
This property can be used to specify the timeout (in milliseconds) to use for requests against the source. For example,
s:timeout 5000 configures a 5 second timeout. |
| batching | boolean or int |
This property can be used to disable batching, or it can be used to change the default the batch size. By default, batching is set to 5000 (
s:batching 5000). To disable batching, you can include s:batching false in the query. Typically users do not change the batching size. However, it can be useful to control the batch size when performing updates. To configure the size, include s:batching int in the query. For example, s:batching 3000. |
| paging | RDF list |
This property can be used to configure paging so that the GDI can access large amounts of data across a number of smaller requests. For details about the
paging property, see Paginating Requests. |
| concurrency | int or RDF list |
This property can be included to configure the maximum level of concurrency for the query. The value can be an integer, such as
s:concurrency 8. If the value is an integer, it configures a maximum limit on the number of slices that can execute the query. For finer-grained control over the number of nodes and slices to use, concurrency can also be included as an object with limit, nodes, and/or executorsPerNode properties. For example, the following object configures a concurrency model that allows a maximum of 24 executors distributed across 4 nodes with 8 executors per node:
|
| rate | int or string |
This property can be included to control the frequency with which a request is sent to the source. The limit applies to the number of requests a single slice can make. If you specify an integer for the rate, then the value is treated as the maximum number of requests to issue per minute. If you specify a string, you have more flexibility in configuring the rate. The sample values below show the types of values that are supported:
To enforce the rate limit, the GDI introduces a sleep between requests that is equal to the rate delay. The more executing slices, the longer the rate delay needs to be to enforce the limit in aggregate. Given the example of |
| locale | string |
This property can be used to specify the locale to use when parsing locale-dependent data such as numbers, dates, and times.
|
| sampling | int |
This property can be used to configure the number of records in the source to examine for data type inferencing.
|
| selector | string or RDF list |
This property can be used for JSON path extraction to traverse nested structures and target specific data. For example,
s:selector "projects" targets the projects class of data. To express a hierarchy, use dot notation. For example, s:selector "region.state.city" navigates a hierarchy to target city data. For more information about binding components and the selector property, see Using Binding Trees and Selector Paths. |
| model | string |
This property defines the class (or table) name for the type of data that is generated from the specified data source. For example,
s:model "employees". Model is optional when querying a single source. If your query targets multiple sources, however, and you want to define resource templates (primary keys) and object properties (foreign keys), you must specify the model value for each source. |
| key | string |
This property can be used to define the primary key column for the source file or table. This column is leveraged in a resource template for the instances that are created from the source. For example,
s:key ("EMPLOYEE_ID"). For more information about key, see Data Linking Options. |
| reference | RDF list |
This property can be used to specify a foreign key column. The reference property is an RDF list that includes the
model property to list the target table and a using property that defines the foreign key column. For more information about reference, see Data Linking Options. |
| formats | RDF list |
To give users control over the data types that are used when coercing strings to other types, this property can be included in GDI queries to define the desired types. In addition, it can be used to describe the formats of date and time values in the source to ensure that they are recognized and parsed to the appropriate date, time, and/or dateTime values. For details about the
formats property, see Data Type Formatting Options. |
| normalize | RDF list |
To give users control over the labels and URIs that are generated, the GDI offers several options for normalizing the model and/or the fields that are created from the specified data source(s). For details about the
normalize property, see Model Normalization Options. |
| count | variable |
If you want to turn the query into a COUNT query, you can include this property with a
?variable to perform a count. For example, s:count ?count. |
| offset | int |
This property can be used to offset the data that is returned by a number of rows.
|
| limit | int |
You can include this property to limit the number of results that are returned.
s:limit maps to the SPARQL LIMIT clause. |
| mapping_variable | variable |
The mapping variables, in
?mapping_variable (["binding"] [datatype] ["datetime_format"]) format, define the triple patterns to output. When the specified ?variable matches the source column name, the GDI uses the variable as the source data selector. If you specify an alternate variable name, a binding needs to be specified to map the new variable to the source. You also have the option to transform the data using the datatype and datetime_format options.See Hierarchical Bindings and Arrays below for more information about configuring mapping variables and unpacking JSON files with nested objects and arrays. |
| binding | string |
The
binding is a literal value that binds a ?mapping_variable to a source column. If you specify a ?variable that matches the source column name, then that variable name is the data selector and it is not necessary to specify a binding. If you specify an alternate variable name or there is a hierarchical path to the source column, then the binding is needed to map the new variable to that source column.For example for CSV, the following pattern simply binds the source column AIRLINE to the lowercase variable ?airline: For FileSource, periods (.), forward slashes (/), and brackets ([ ]) are parsed as path notation. Therefore, if a source column name includes any of those characters they must be escaped in the binding. Use two backslashes (\\) as an escape character. For example, if a column name is average/day, the variable and binding pattern could be written as |
| datatype | URI |
The
datatype is the data type to convert the column to. If you do not specify a data type, the GDI infers the type. The GDI supports the following types:
|
| datetime_format | string |
This option is used to specify the format to use for date and time data types. The GDI supports Java date and time formats. Specify days as "d," months as "M," and years as "y." For the time, specify "H" for hours, "m" for minutes, and "s" for seconds. For example,
"yyyyMMdd HH:mm:ss" or "ddMMMyy" to display date values such as "01JAN19."
The GDI's default base year is 2000. If the source data has years with only two digits, such as |
Hierarchical Bindings and Arrays
When configuring the mapping variables in a query, the GDI provides syntax for unpacking JSON files with nested objects and arrays. One way to express hierarchies in queries is to use brackets ( [ ] ) to group objects into binding trees. For example, the WHERE clause snippet below organizes mapping variable objects into an hourly/data hierarchy by nesting the ?data patterns inside the ?hourly [ ] tree:
WHERE
{
SERVICE <http://cambridgesemantics.com/services/DataToolkit>
{
?data a s:FileSource;
s:url "/mnt/data/json/weather.json" ;
?latitude (xsd:double) ;
?longitude (xsd:double) ;
?timezone (xsd:string) ;
?hourly [
?data [
?time (xsd:long) ;
?rainProbability ("precipProbability" xsd:double) ;
?temperature (xsd:double) ;
?feelsLike ("apparentTemperature" xsd:double) ;
?windSpeed (xsd:double) ;
] ;
] .
}
}
When constructing object binding trees, if you choose to introduce the hierarchy with a variable name that is not an exact match to the source label, include a selector property to list the value from the source. For example, in the WHERE clause snippet below, s:selector is included to select eventHeader in the source as ?event in the query and statLocation as ?location.
WHERE
{
SERVICE <http://cambridgesemantics.com/services/DataToolkit>
{
?data a s:FileSource ;
s:url "/mnt/data/json/part_1.json" ;
?event [
s:selector "eventHeader" ;
?eventId (xsd:string) ;
?eventName (xsd:string) ;
?eventVersion (xsd:string) ;
?eventTime (xsd:dateTime) ;
] ;
?location [
s:selector "statLocation" ;
?locationId (xsd:string) ;
?lineNo (xsd:int) ;
?statNo (xsd:int) ;
?statId (xsd:int) ;
] .
}
}
As an alternative to grouping objects in binding trees, the selector property also supports using dot notation to specify paths. For example, the WHERE clause snippet below rewrites the first example query to express the same hourly/data hierarchy as a path in the s:selector value:
WHERE
{
SERVICE <http://cambridgesemantics.com/services/DataToolkit>
{
?data a s:FileSource;
s:url "/mnt/data/json/weather.json" ;
?latitude (xsd:double) ;
?longitude (xsd:double) ;
?timezone (xsd:string) ;
s:selector: "hourly.data" ;
?time (xsd:long) ;
?rainProbability ("precipProbability" xsd:double) ;
?temperature (xsd:double) ;
?feelsLike ("apparentTemperature" xsd:double) ;
?windSpeed (xsd:double) .
}
}
In addition to object binding trees and selectors, the GDI offers additional syntax for reading or ingesting JSON sources with nested objects and arrays. For example, following the JSON sample file below is a query that captures each value in the arrays:
{
"payload" :
{
"IBP_IndEvent_MSR" :
{
"unit" : "ms",
"value" : [ 0, 1 ]
},
"IBP_IndEvent_RMF" :
{
"unit" : "-",
"value" : [ 0.012, 1.398, 3.1415 ]
}
}
}
To read the JSON file above, the following query uses an object binding (?values [ ]) to drill down to the value arrays in the source. An @ selector is specified in the ?value variable binding (?value ("@" xsd:double)) to retrieve each of the array values. For an array of primitive values, the @ selector captures each value in the array. If the source value was an array of objects, the @ selector would retrieve a JSON representation for each object in the array. In addition to creating a new binding context for the primitive array values, the ?values object binding also includes ?index ("!array::index") to capture the index array with the primitive value.
PREFIX s: <http://cambridgesemantics.com/ontologies/DataToolkit#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
SELECT *
WHERE {
SERVICE <http://cambridgesemantics.com/services/DataToolkit> {
?data a s:FileSource ;
s:url "/mnt/data/json/array-index.json" ;
s:selector "payload.*" ;
?unit (xsd:string) ;
?values [
s:selector "value" ;
?value ("@" xsd:double) ;
?index ("!array::index") ;
] .
}
}
The results of the query are shown below:
unit | value | index -----+--------+------- ms | 0 | 0 ms | 1 | 1 - | 0.012 | 0 - | 1.398 | 1 - | 3.1415 | 2
If you do not want to retrieve all of the values in an array, you can include the specific index number to retrieve instead of using the @ symbol. In the variable binding, the index number is appended in brackets ([ ]) to the binding column name. For example, the following variable binding retrieves the second index value (the third value in the array) from a "projects" array: ?project ("projects[2]"). The next example uses the following JSON file:
{
"field1" : "value1" ,
"arrayfield" : [
"arrayvalue1",
"arrayvalue2"
]
}
To retrieve only the second value in the array, the following query appends the index value 1 to the array column name, arrayfield:
PREFIX s: <http://cambridgesemantics.com/ontologies/DataToolkit#>
SELECT *
WHERE {
SERVICE <http://cambridgesemantics.com/services/DataToolkit> {
?json a s:FileSource ;
s:url "/mnt/data/json/array-index-2.json" ;
?field1 (xsd:string) ;
?arrayval ("arrayfield[1]" xsd:string) .
}
}
The results of the query are shown below:
field1 | arrayval ---------+---------- value1 |arrayvalue2
Capturing Property Keys
In GDI Generator queries, the names of property keys can be captured from files by including a variable as the s:selector and using the same variable as the s:key. For example, the GDI query below ingests the following simple JSON file.
# company.json
{
"AAPL": {
"name": "Apple Corp"
},
"MSFT": {
"name": "Microsoft"
},
"IBM": {
"name": "IBM"
}
}
In the query, the keys "AAPL," "MSFT," and "IBM" are selected as the ?TickerSymbol variable and the key is set to the same value.
PREFIX s: <http://cambridgesemantics.com/ontologies/DataToolkit#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
INSERT ${targetGraph} {
?s ?p ?o .
}
}
WHERE {
SERVICE <http://cambridgesemantics.com/services/DataToolkit> {
?data a s:FileSource ;
s:url "/opt/shared/data/company.json" ;
s:selector "?TickerSymbol" ;
s:key (?TickerSymbol) ;
s:model "Company" ;
?TickerSymbol (xsd:string) ;
?name (xsd:string) .
?rdf a s:RdfGenerator, s:OntologyGenerator;
s:as (?s ?p ?o) ;
s:ontology <http://cambridgesemantics.com/ontologies/company> ;
s:base ${targetGraph} .
}
}
Selecting the predicates and objects from the graph shows the tickerSymbol predicate and value.
SELECT ?p ?o
${usingSources}
WHERE { ?s ?p ?o . }
ORDER BY desc(?o)p | o -------------------------------------------------------------+------------- http://anzograph.com/ontologies/company#Company.name | Microsoft http://anzograph.com/ontologies/company#Company.tickerSymbol | MSFT http://anzograph.com/ontologies/company#Company.name | IBM http://anzograph.com/ontologies/company#Company.tickerSymbol | IBM http://anzograph.com/ontologies/company#Company.name | Apple Corp http://anzograph.com/ontologies/company#Company.tickerSymbol | AAPL
...
Query Examples
The example query below reads a JSON file that contains data about weather. Since the file is hierarchical, the s:selector property is included to specify the path to data in the hourly/data hierarchy:
PREFIX s: <http://cambridgesemantics.com/ontologies/DataToolkit#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
SELECT *
WHERE
{
SERVICE <http://cambridgesemantics.com/services/DataToolkit>
{
?data a s:FileSource;
s:url "/mnt/data/json/weather.json" ;
?latitude (xsd:double) ;
?longitude (xsd:double) ;
?timezone (xsd:string) ;
s:selector: "hourly.data" ;
?time (xsd:long) ;
?rainProbability ("precipProbability" xsd:double) ;
?temperature (xsd:double) ;
?feelsLike ("apparentTemperature" xsd:double) ;
?windSpeed (xsd:double) .
}
}
The following example query ingests data from a JSON file that contains data about the New York Times best selling books.
PREFIX s: <http://cambridgesemantics.com/ontologies/DataToolkit#>
PREFIX books: <http://cambridgesemantics.com/ontologies/NYT_Bestsellers_Ontology#>
INSERT {
GRAPH ${targetGraph}{
?book a books:Book ;
books:p_Title ?title ;
books:p_Description ?description ;
books:p_Author ?author ;
books:p_Publisher ?publisher ;
books:p_Date ?rawdate .
}
}
WHERE {
SERVICE <http://cambridgesemantics.com/services/DataToolkit>
{
?data a s:FileSource ;
s:url "/mnt/data/json/nyt_best_sellers.json" ;
?title () ;
?author () ;
?description () ;
?publisher () ;
?price() ;
?rawdate ("bestsellers_date.$date.$numberLong").
}
BIND(IRI(CONCAT("http://cambridgesemantics.com/ontologies/NYT_Bestsellers_Ontology/", ENCODE_FOR_URI(?title))) AS ?book) .
}
A snippet of the file's contents is shown below:
{
"_id": {
"$oid": "5b4aa4ead3089013507db18b"
},
"bestsellers_date": {
"$date": {
"$numberLong": "1211587200000"
}
},
"published_date": {
"$date": {
"$numberLong": "1212883200000"
}
},
"amazon_product_url": "http://www.amazon.com/Odd-Hours-Dean-Koontz/dp/0553807056?tag=NYTBS-20",
"author": "Dean R Koontz",
"description": "Odd Thomas, who can communicate with the dead, confronts evil forces in a California coastal town.",
"price": {
"$numberInt": "27"
},
"publisher": "Bantam",
"title": "ODD HOURS",
"rank": {
"$numberInt": "1"
},
"rank_last_week": {
"$numberInt": "0"
},
"weeks_on_list": {
"$numberInt": "1"
}
}