The Semantic Layer, From ScratchChapter 4

Chapter 4

SPARQL for people who know SQL

You already know how to ask a database a question. By the end of this chapter you will ask a graph the same questions, and watch the JOIN keyword quietly disappear into the arrows.

It is 4:55 on a Friday and someone from marketing leans over your desk: "Quick one. Which of our customers in Wisconsin spent the most?" You know the SQL: join purchase to customer on cid, customer to address on location, filter on the state, sum price times quantity, sort. You also know the catch: customers live in Aurora and purchases in Redshift, so "just join them" means an export, a staging table, or a pipeline. By Chapter 9 the graph will answer that question across both in one query. First you need the language the question is written in, and you already know most of it.

4.1What is SPARQL, and why does it exist?

SPARQL (pronounced "sparkle"; the letters stand for SPARQL Protocol and RDF Query Language) is the W3C standard query language for RDF graphs: to triples what SQL is to tables. SPARQL 1.1 (2013) added aggregates, subqueries and a write half called SPARQL Update. Every serious graph database, Stardog included, speaks it.

Why not just use SQL? Because SQL assumes tables with a fixed list of columns, and a graph has neither. You could store triples in a three-column table, triples(subject, predicate, object), but then every arrow you follow is another self-join, and SQL has no idea that <urn:…customer:225> is a global name, that "2013-04-18"^^xsd:date is a typed value, or that triples live in named graphs (Chapter 3). SPARQL builds those ideas in and makes "follow this arrow" the basic move.

The "Protocol" part defines how to send a query over HTTP and get JSON or CSV back. Every Stardog database is a SPARQL endpoint, a URL that accepts queries; that is how the agent in Chapter 14 will talk to our graph.

Term	What it is	Why it exists	Tiny example
SPARQL	The W3C query language for RDF	SQL has no notion of triples, IRIs or graphs	`SELECT ?n WHERE { ?c :name ?n }`
SPARQL endpoint	An HTTP URL that runs SPARQL	So any program can query the graph	your `kit-c360` database
Triple pattern	A triple with some blanks	It describes the facts you are looking for	`?c :name ?n .`
Variable	A named blank, starting with `?`	It marks what the engine should fill in	`?c`, `?n`
Binding	The value a variable gets for one match	Each set of bindings is one result row	`?n = "Carolyn Inworth"`
SPARQL Update	The write half of SPARQL	To add, remove and drop triples	`INSERT DATA { … }`

4.2A query is a picture with blanks

In Chapter 2 you learned that every fact is a triple: subject predicate object. Carolyn's name is customer:225 :name "Carolyn Inworth". A triple pattern is the same shape with some positions replaced by variables:

?customer  :name  ?name .

Read it aloud: "find anything that has a :name; call the thing ?customer and the name ?name." For every triple whose predicate is :name, the engine binds the variables. One match, one row.

Press Play to watch the engine match the pattern, one triple at a time.

Figure 4.1: A triple pattern is a triple with blanks. Every stored triple that fits the fixed parts produces one row, with the blanks filled in.

A full query wraps patterns in WHERE { … } and lists the variables you want back:

PREFIX : <tag:stardog:api:ecomm:>

SELECT ?customer ?name
WHERE {
  ?customer :name ?name .
}

The PREFIX line is the shorthand from Chapter 3: :name expands to <tag:stardog:api:ecomm:name>. Every query in this book declares its prefixes, so each one runs on its own.

Picture the graph as one big List<Triple>. A one-pattern SELECT is a stream over it: the fixed parts become a filter, the variables the fields you map into a row.

SPARQL

SELECT ?customer ?name
WHERE {
  ?customer :name ?name .
}

Java 21

record Triple(String s, String p, Object o) {}
record Row(String customer, Object name) {}

List<Row> rows = graph.stream()          // List<Triple>
    .filter(t -> t.p().equals(":name"))  // the fixed part
    .map(t -> new Row(t.s(), t.o()))     // fill the blanks
    .toList();

Where the analogy breaks: the Java version says how (scan every triple, in this order). SPARQL only says what shape you want. Stardog keeps triples in several sorted indexes, and its planner chooses which to read and in what order. You describe the answer; the engine writes the loop.

4.3The shared variable is the join

Now put three patterns together and reuse variables:

?customer :name    ?name .
?customer :address ?addr .
?addr     :city    ?city .

Because ?customer appears in the first two patterns, both must match the same customer; because ?addr appears in the last two, the city must belong to that customer's address. That is the whole trick: a variable used in more than one pattern is a join condition. No JOIN, no ON. Here is the same question over the Aurora tables:

SELECT c.full_name, a.city
FROM   c360.customer c
JOIN   c360.address  a ON a.id = c.location;

The SPARQL never mentions the key columns location and id. In the graph, :address is not a key to match but an arrow pointing straight at the address. You do not join on it, you follow it.

A variable is a placeholder, and a shared variable is a join.

Example: customer:225 :address ?addr . ?addr :city ?city joins on ?addr and returns "Milwaukee".

The shared variable is a hash join: build a Map keyed on the join value, then look each row up in it. ?customer and ?addr are the keys.

SPARQL

?customer :name    ?name .
?customer :address ?addr .
?addr     :city    ?city .

Java 21

Map<String, Object> addrOf = graph.stream()
    .filter(t -> t.p().equals(":address"))
    .collect(Collectors.toMap(Triple::s, Triple::o));
Map<String, Object> cityOf = graph.stream()
    .filter(t -> t.p().equals(":city"))
    .collect(Collectors.toMap(Triple::s, Triple::o));

var rows = graph.stream()
    .filter(t -> t.p().equals(":name"))
    .filter(t -> cityOf.containsKey(addrOf.get(t.s())))
    .map(t -> List.of(t.o(), cityOf.get(addrOf.get(t.s()))))
    .toList();

Where the analogy breaks: Collectors.toMap assumes one value per key and throws on a duplicate. RDF has no such rule: a customer with two :address triples gives one row per combination. And the planner, not you, picks the build side and join order.

Figure 4.2: A chain of patterns is a chain of joins. The shared variables play the part of the ON clauses; the keys never appear. (The SQL is only a picture: Aurora and Redshift cannot be joined in one SQL statement without extra plumbing.)

4.4GRAPH: which folder to look in

Named graphs are folders of triples (Chapter 3). A Stardog query with no GRAPH keyword reads only the default graph (unless the database option query.all.graphs is switched on; it is off by default and this book assumes it stays off). Our practice data lives in the folder <urn:book:ch04>, so every query in this chapter names it:

SELECT ?customer ?name
WHERE {
  GRAPH <urn:book:ch04> {
    ?customer :name ?name .
  }
}

GRAPH <iri> { … } is SPARQL's nearest thing to the c360. in FROM c360.customer. GRAPH ?g { … } searches every folder, with ?g naming the one each match came from. In Chapter 9 this keyword is the heart of federation: one GRAPH block aimed at Aurora, another at Redshift, in one query.

4.5The SQL-to-SPARQL phrasebook

The rest of SPARQL is clauses you already know, spelled differently.

You want to…	SQL	SPARQL
Pick output columns	`SELECT a, b`	`SELECT ?a ?b` (no commas)
Name the source	`FROM c360.customer`	`GRAPH <urn:…> { … }`
Join	`JOIN … ON x.k = y.k`	Reuse a variable in two patterns
Filter rows	`WHERE price > 1000`	`FILTER (?price > 1000)` inside `WHERE`
Keep rows with no match	`LEFT JOIN`	`OPTIONAL { … }`
Test for missing	`WHERE phone IS NULL`	`FILTER (!BOUND(?phone))`
Remove duplicates	`SELECT DISTINCT`	`SELECT DISTINCT`
Aggregate	`SUM(x) … GROUP BY k`	`(SUM(?x) AS ?total) … GROUP BY ?k`
Filter groups	`HAVING SUM(x) > 100`	`HAVING (SUM(?x) > 100)`
Sort and page	`ORDER BY x DESC LIMIT 10`	`ORDER BY DESC(?x) LIMIT 10`
Computed column	`price * quantity AS spend`	`BIND(?price * ?qty AS ?spend)`
Yes/no question	`SELECT EXISTS (…)`	`ASK { … }`
Insert rows	`INSERT INTO … VALUES`	`INSERT DATA { GRAPH <g> { … } }`
Delete rows	`DELETE FROM … WHERE`	`DELETE DATA { … }`
Drop a table	`DROP TABLE t`	`DROP GRAPH <g>`

Two differences deserve a warning. There is no fixed column list: a thing has whatever triples it has, and a query asks only for the ones it names. And there is no NULL: a missing value is simply an absent triple. Section 4.7 shows what that does to LEFT JOIN, and Chapter 5 why it matters.

4.6FILTER: row conditions, and why datatypes matter

Row conditions go in a FILTER inside WHERE. Filters compare numbers, strings and dates, combine with &&, || and !, and call functions such as CONTAINS, STRSTARTS, REGEX and YEAR:

?order :purchasePrice ?price ;
       :purchaseDate  ?date .
FILTER (?price > 1000 && ?date >= "2015-01-01"^^xsd:date)

Here Chapter 3's datatypes pay off. ?price > 1000 compares numerically only because the price is an xsd:decimal. Store it as the string "10479.00" and the comparison errors for that row, and the trap is that an error inside a filter does not stop the query: it counts as "not true", and the row silently disappears. (The semicolon is Turtle's "same subject, another predicate": two patterns sharing ?order, so a join.)

4.7OPTIONAL: LEFT JOIN without NULLs

OPTIONAL keeps a row even when part of the pattern has no match, like LEFT JOIN:

?c a :Customer ;
   :name ?name .
OPTIONAL { ?c :phone ?phone }

With no phone triple, the row stays and ?phone is unbound: an empty cell in Studio. Unbound is not a value; ?phone = "" will not find it. Test with FILTER (!BOUND(?phone)).

An unbound variable is closer to Optional.empty() than to null: the row survives, the slot is empty, and you test it (BOUND / isPresent()) instead of comparing it to a value.

SPARQL

?c a :Customer ;
   :name ?name .
OPTIONAL { ?c :phone ?phone }

# only the ones with no phone:
FILTER (!BOUND(?phone))

Java 21

record Row(String iri, String name, Optional<String> phone) {}

// OPTIONAL: every customer stays, phone may be empty
List<Row> all = customers.stream()
    .map(c -> new Row(c.iri(), c.name(),
            Optional.ofNullable(phoneOf.get(c.iri()))))
    .toList();

// a plain pattern is an inner join: no phone, no row
var withPhone = all.stream()
    .filter(r -> r.phone().isPresent()).toList();
// FILTER (!BOUND(?phone))
var noPhone = all.stream()
    .filter(r -> r.phone().isEmpty()).toList();

Where the analogy breaks: an Optional holds at most one value; two :phone triples would give two rows. And unbound does not mean "has no phone" (Chapter 5's open world), only that no phone triple is in the graph you queried.

Figure 4.3: OPTIONAL keeps the row and leaves the variable unbound. A plain pattern is an inner join: no phone triple, no row.

A missing triple is not a NULL: without OPTIONAL, the whole row disappears.

Example: Marlane has no :phone, so ?c :phone ?p drops her; OPTIONAL { ?c :phone ?p } keeps her.

4.8Aggregates, ASK, and changing data

Aggregates work as in SQL (COUNT, SUM, AVG, MIN, MAX, and GROUP_CONCAT for string_agg), with one spelling rule: in SELECT an aggregate goes in parentheses with a name, (SUM(?price * ?qty) AS ?spend). That formula is the SUM(price * quantity) behind the book's headline number, Carolyn Inworth's $502,529.42. SELECT DISTINCT matters because following arrows multiplies rows: "cities of customers who ordered" gives one row per order without it. And put a LIMIT on exploratory queries; from Chapter 9 on, they reach a Redshift cluster that bills by the second.

GROUP BY with SUM is Collectors.groupingBy with a summing collector; ORDER BY DESC(...) LIMIT is sorted(...).limit(...). BigDecimal, because this is money.

SPARQL

SELECT ?name (SUM(?price * ?qty) AS ?spend)
WHERE {
  ?order :purchasedBy   ?customer ;
         :purchasePrice ?price ;
         :quantity      ?qty .
  ?customer :name ?name .
}
GROUP BY ?name
ORDER BY DESC(?spend)
LIMIT 3

Java 21

record Order(String name, BigDecimal price, int qty) {}

Map<String, BigDecimal> spend = orders.stream()
    .collect(Collectors.groupingBy(Order::name,
        Collectors.reducing(BigDecimal.ZERO,
            o -> o.price().multiply(BigDecimal.valueOf(o.qty())),
            BigDecimal::add)));

var top3 = spend.entrySet().stream()
    .sorted(Map.Entry.<String, BigDecimal>comparingByValue()
                     .reversed())
    .limit(3)
    .toList();

Where the analogy breaks: the obvious Collectors.summingDouble would reproduce the cent drift from Chapter 3; SPARQL sums xsd:decimal exactly, like BigDecimal. And the Java runs as written, while the SPARQL planner may reorder patterns, use indexes, or (from Chapter 9 on) push the whole SUM down into Redshift as SQL.

Two more query forms: ASK returns only true or false ("does this shape exist anywhere?"), and CONSTRUCT returns new triples built from a template instead of a table.

Writes use SPARQL Update, and you need three statements: INSERT DATA adds literal triples (no variables; think INSERT … VALUES), DELETE DATA removes exactly the triples you list, and DROP GRAPH empties a whole named graph (how every scratch exercise cleans up).

4.9The practice dataset

The values below are real c360 rows from Aurora and Redshift; only the selection is ours. Two customers: Carolyn Inworth (cid 225, Milwaukee, Wisconsin) and Marlane Shadwick (cid 88, Brooksville, Florida), the number-two spender overall. Their addresses, two orders each, and the four products those orders point at. The IRIs are exactly the ones the virtual graphs will mint in Chapters 8 and 9. One deliberate edit: we left out Marlane's phone so the OPTIONAL exercise has a gap to find.

Order (id, shortened)	Customer	Product	Date	Qty	Price	Price × qty
363f9784…	Carolyn (225)	Wax Paper	2013-04-18	21	10479.00	220,059.00
4d7483bc…	Carolyn (225)	Softball Cleats	2019-02-22	10	199.70	1,997.00
433d33e7…	Marlane (88)	Tent	2017-10-03	15	74.85	1,122.75
3552866c…	Marlane (88)	Lemonade	2022-12-25	25	13749.75	343,743.75

Step 1: load the practice data. In Studio's Workspace, open a new query tab on database kit-c360, reasoning off, paste and run. This writes only to the scratch graph <urn:book:ch04>. Paste it; don't save it as a file. This block is a SPARQL update (it starts with INSERT DATA), not a Turtle file. Studio's Load Data only reads Turtle, and rejects this with Expected ':', found ' ' [L4]. Turtle files hold bare triples, like Chapter 3's .ttl; the Workspace runs statements like this one.

PREFIX :    <tag:stardog:api:ecomm:>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

INSERT DATA {
  GRAPH <urn:book:ch04> {

    # --- two customers (from Aurora, c360.customer) ---
    <urn:stardog:demos:c360:customer:225> a :Customer ;
        :firstName "Carolyn" ;
        :lastName  "Inworth" ;
        :name      "Carolyn Inworth" ;
        :email     "cinworth68@g.co" ;
        :phone     "704-619-5453" ;
        :address   <urn:stardog:demos:c360:location:b80ea2ce-2361-408a-b443-721594cbbb35> .

    <urn:stardog:demos:c360:customer:88> a :Customer ;
        :firstName "Marlane" ;
        :lastName  "Shadwick" ;
        :name      "Marlane Shadwick" ;
        :email     "mshadwick2f@xing.com" ;
        :address   <urn:stardog:demos:c360:location:d2b31bd9-cd91-4665-942a-d25e105a722e> .
        # (Marlane's phone is left out on purpose: we need a gap for OPTIONAL.)

    # --- their two addresses (Aurora, c360.address) ---
    <urn:stardog:demos:c360:location:b80ea2ce-2361-408a-b443-721594cbbb35> a :Address ;
        :streetAddress "78945 Thompson Plaza" ;
        :city "Milwaukee" ; :state "Wisconsin" ; :zipCode "53285" .

    <urn:stardog:demos:c360:location:d2b31bd9-cd91-4665-942a-d25e105a722e> a :Address ;
        :streetAddress "36 Manitowish Point" ;
        :city "Brooksville" ; :state "Florida" ; :zipCode "34605" .

    # --- four of their orders (Redshift, c360.purchase) ---
    <urn:stardog:demos:c360:purchase:us:pos363f9784-3d57-4a40-a273-4362b436cd8f> a :Order ;
        :purchasedBy   <urn:stardog:demos:c360:customer:225> ;
        :itemPurchased <urn:stardog:demos:c360:product:268> ;
        :purchaseDate  "2013-04-18"^^xsd:date ;
        :quantity      21 ;
        :purchasePrice 10479.00 .

    <urn:stardog:demos:c360:purchase:us:pos4d7483bc-7561-4372-bebe-647802ba6a0b> a :Order ;
        :purchasedBy   <urn:stardog:demos:c360:customer:225> ;
        :itemPurchased <urn:stardog:demos:c360:product:445> ;
        :purchaseDate  "2019-02-22"^^xsd:date ;
        :quantity      10 ;
        :purchasePrice 199.70 .

    <urn:stardog:demos:c360:purchase:us:pos3552866c-baf6-4fbe-bfef-ae81dff1ac56> a :Order ;
        :purchasedBy   <urn:stardog:demos:c360:customer:88> ;
        :itemPurchased <urn:stardog:demos:c360:product:449> ;
        :purchaseDate  "2022-12-25"^^xsd:date ;
        :quantity      25 ;
        :purchasePrice 13749.75 .

    <urn:stardog:demos:c360:purchase:us:pos433d33e7-bb10-4475-a90f-0db175fbebd8> a :Order ;
        :purchasedBy   <urn:stardog:demos:c360:customer:88> ;
        :itemPurchased <urn:stardog:demos:c360:product:34> ;
        :purchaseDate  "2017-10-03"^^xsd:date ;
        :quantity      15 ;
        :purchasePrice 74.85 .

    # --- the four products those orders point at (Redshift, c360.product) ---
    <urn:stardog:demos:c360:product:268> a :Product ; :name "Wax Paper" .
    <urn:stardog:demos:c360:product:445> a :Product ; :name "Softball Cleats" .
    <urn:stardog:demos:c360:product:449> a :Product ; :name "Lemonade" .
    <urn:stardog:demos:c360:product:34>  a :Product ; :name "Tent" .
  }
}

Step 2: count it. Expect 55 triples (customer 225: 7, customer 88: 6, two addresses: 5 each, four orders: 6 each, four products: 2 each).

SELECT (COUNT(*) AS ?triples) WHERE { GRAPH <urn:book:ch04> { ?s ?p ?o } }

4.10Five graded queries

Run each one and compare with the expected result. If yours differs, the difference is the lesson.

Query 1: a list

"Every customer and their name." ?customer a :Customer means "things of type Customer"; a is short for rdf:type, the star of Chapter 5.

PREFIX : <tag:stardog:api:ecomm:>
SELECT ?customer ?name
WHERE {
  GRAPH <urn:book:ch04> {
    ?customer a :Customer ;
              :name ?name .
  }
}
ORDER BY ?name

Expected: 2 rows: …customer:225 Carolyn Inworth, then …customer:88 Marlane Shadwick.

Query 2: a filter

"Which orders have a price above 1,000?"

PREFIX : <tag:stardog:api:ecomm:>
SELECT ?order ?date ?price
WHERE {
  GRAPH <urn:book:ch04> {
    ?order a :Order ;
           :purchaseDate  ?date ;
           :purchasePrice ?price .
    FILTER (?price > 1000)
  }
}
ORDER BY ?date

Expected: 2 rows: wax paper (2013-04-18, 10479.00) and lemonade (2022-12-25, 13749.75). Studio may print 10479.0; same decimal. Now change the filter to FILTER (?price > 1000 && ?date >= "2015-01-01"^^xsd:date) and add PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> at the top: only the lemonade remains.

Query 3: a join by following arrows

"For each order: who bought it, where do they live, what did they buy?" Zero join keywords.

PREFIX : <tag:stardog:api:ecomm:>
SELECT ?customerName ?city ?productName ?date
WHERE {
  GRAPH <urn:book:ch04> {
    ?order    :purchasedBy   ?customer ;
              :itemPurchased ?product ;
              :purchaseDate  ?date .
    ?customer :name    ?customerName ;
              :address ?addr .
    ?addr     :city    ?city .
    ?product  :name    ?productName .
  }
}
ORDER BY ?customerName ?date

customerName	city	productName	date
Carolyn Inworth	Milwaukee	Wax Paper	2013-04-18
Carolyn Inworth	Milwaukee	Softball Cleats	2019-02-22
Marlane Shadwick	Brooksville	Tent	2017-10-03
Marlane Shadwick	Brooksville	Lemonade	2022-12-25

Expected: 4 rows. In the finished system this exact shape spans Redshift (orders) and Aurora (customers); the query only cares that the IRIs agree. Bonus: SELECT DISTINCT ?city gives 2 rows instead of 4.

Query 4: OPTIONAL

"Every customer, with a phone if we have one."

PREFIX : <tag:stardog:api:ecomm:>
SELECT ?name ?phone
WHERE {
  GRAPH <urn:book:ch04> {
    ?c a :Customer ;
       :name ?name .
    OPTIONAL { ?c :phone ?phone }
  }
}
ORDER BY ?name

Expected: 2 rows; Carolyn with 704-619-5453, Marlane with an empty phone cell. Now delete OPTIONAL { and its closing brace and rerun: 1 row, Carolyn only. That is Figure 4.3 on your screen.

Query 5: GROUP BY and total spend

"Orders and total spend per customer, biggest first": the headline question in miniature.

PREFIX : <tag:stardog:api:ecomm:>
SELECT ?name (COUNT(?order) AS ?orders) (SUM(?price * ?qty) AS ?spend)
WHERE {
  GRAPH <urn:book:ch04> {
    ?order    :purchasedBy   ?customer ;
              :purchasePrice ?price ;
              :quantity      ?qty .
    ?customer :name ?name .
  }
}
GROUP BY ?name
ORDER BY DESC(?spend)

name	orders	spend
Marlane Shadwick	2	344866.50
Carolyn Inworth	2	222056.00

Expected: 2 rows, Marlane first (Studio may show 344866.5 and 222056.0). Marlane beats Carolyn, the overall number one, because we loaded only two orders each. A total is only as complete as the data you queried: keep that thought for Chapter 5.

Check yourself

What is SPARQL, and why not just use SQL on a table of triples?

The W3C query language (and HTTP protocol) for RDF. In a three-column SQL table every hop is another self-join, and SQL knows nothing about IRIs, typed literals or named graphs. SPARQL makes "follow this arrow" the basic operation.

In SPARQL, what plays the role of JOIN … ON a.k = b.k?

Using the same variable in two or more triple patterns: all must match the same value, an equi-join. You follow the arrow instead of matching key columns.

You add ?c :email ?email . to a working customer report and three customers disappear. Why, and how do you fix it?

Every plain pattern is an inner join, so customers with no email triple drop out. Wrap it: OPTIONAL { ?c :email ?email }.

A query with no GRAPH keyword returns nothing, though you know the data is there. The likely cause?

The triples are in a named graph, and without GRAPH Stardog reads only the default graph (query.all.graphs is off by default). Add GRAPH <urn:book:ch04> { … }, or GRAPH ?g { … } to search every named graph.

When would FILTER (?price > 1000) silently return nothing?

When the prices are plain strings, not typed numbers. Comparing a string with a number is an error, and a filter error counts as false: the rows vanish silently.

Chapter recap

SPARQL is the W3C query language and protocol for RDF; every Stardog database is a SPARQL endpoint.
A query is a picture of triples with blanks; each place it fits is one row.
A variable shared between patterns is a join: you follow arrows, never key columns.
GRAPH <iri> picks the folder. FILTER needs correct datatypes, and its errors silently drop rows.
OPTIONAL is LEFT JOIN; there is no NULL, only unbound variables and absent triples.
Aggregates, DISTINCT, ORDER BY and LIMIT work as in SQL. INSERT DATA is idempotent because a graph is a set.
On two orders each, Marlane Shadwick (344,866.50) outspent Carolyn Inworth (222,056.00). You then dropped <urn:book:ch04>.