hubComposing with SQL

pgrdf.sparql(query) is an ordinary PostgreSQL set-returning function, so everything PostgreSQL does with a row source works with it: JOIN, WITH, CREATE TABLE AS, INSERT … SELECT, views. Graph data and relational data meet in one query, one transaction, one connection.

The examples on this page share one small graph:

sql

SELECT pgrdf.add_graph('https://example.com/graph/people');

SELECT pgrdf.parse_turtle($$
@prefix ex:   <http://example.com/> .
@prefix foaf: <http://xmlns.com/foaf/0.1/> .
ex:alice a ex:Engineer ; foaf:name "Alice" ; foaf:mbox <mailto:alice@example.com> ;
         foaf:knows ex:bob, ex:carol .
ex:bob   a ex:Engineer ; foaf:name "Bob"   ; foaf:mbox <mailto:bob@example.com> ;
         foaf:knows ex:carol .
ex:carol foaf:name "Carol" ; foaf:age 34 .
$$, pgrdf.graph_id('https://example.com/graph/people'));
-- 11

Dollar quoting ($$ … $$) saves you escaping the quotes inside SPARQL.

The row shape

Each result row is one jsonb column named sparql, with one key per projected variable:

sql

SELECT sparql FROM pgrdf.sparql($$
  PREFIX foaf: <http://xmlns.com/foaf/0.1/>
  SELECT ?person ?name ?age
   WHERE { ?person foaf:name ?name OPTIONAL { ?person foaf:age ?age } }
   ORDER BY ?name
$$);

                                sparql
----------------------------------------------------------------------
 {"age": null, "name": "Alice", "person": "http://example.com/alice"}
 {"age": null, "name": "Bob", "person": "http://example.com/bob"}
 {"age": "34", "name": "Carol", "person": "http://example.com/carol"}

Take a value out with sparql->>'name'.
Every value is a string, numbers included ("34"). Cast in SQL when you need a type: (sparql->>'age')::int.
An unbound variable is JSON null, so sparql->>'age' is SQL NULL.
IRIs come back as plain strings, without angle brackets.

sql

SELECT sparql->>'name'       AS name,
       (sparql->>'age')::int AS age
  FROM pgrdf.sparql($$
    PREFIX foaf: <http://xmlns.com/foaf/0.1/>
    SELECT ?name ?age
     WHERE { ?person foaf:name ?name OPTIONAL { ?person foaf:age ?age } }
     ORDER BY ?name
  $$);

 name  | age
-------+-----
 Alice |
 Bob   |
 Carol |  34

Aliasing the function

PostgreSQL names the column of a single-column function after its table alias. After FROM pgrdf.sparql(…) AS f the column is f, and f.sparql fails with column f.sparql does not exist. Leave the alias off, or keep the column name with AS f(sparql).

Join graph data to a table

sql

CREATE TABLE users (iri text PRIMARY KEY, region text);
INSERT INTO users VALUES
  ('http://example.com/alice', 'EU'),
  ('http://example.com/bob',   'US'),
  ('http://example.com/carol', 'EU');

SELECT u.region, count(*) AS knows_count
  FROM pgrdf.sparql($$
         PREFIX foaf: <http://xmlns.com/foaf/0.1/>
         SELECT ?a ?b WHERE { ?a foaf:knows ?b }
       $$)
  JOIN users u ON u.iri = sparql->>'a'
 GROUP BY u.region
 ORDER BY knows_count DESC;

 region | knows_count
--------+-------------
 EU     |           2
 US     |           1

Name the result in a CTE

A CTE turns the JSONB into ordinary columns once, so the rest of the query reads like plain SQL:

sql

WITH knows AS (
  SELECT sparql->>'a' AS a, sparql->>'b' AS b
    FROM pgrdf.sparql($$
      PREFIX foaf: <http://xmlns.com/foaf/0.1/>
      SELECT ?a ?b WHERE { ?a foaf:knows ?b }
    $$)
)
SELECT ua.region AS from_region, ub.region AS to_region, count(*)
  FROM knows k
  JOIN users ua ON ua.iri = k.a
  JOIN users ub ON ub.iri = k.b
 GROUP BY 1, 2
 ORDER BY 1, 2;

 from_region | to_region | count
-------------+-----------+-------
 EU          | EU        |     1
 EU          | US        |     1
 US          | EU        |     1

Materialise into a table

CREATE TABLE AS takes a snapshot:

sql

CREATE TABLE person_emails AS
SELECT sparql->>'person'  AS person_iri,
       sparql->>'mailbox' AS mailbox
  FROM pgrdf.sparql($$
    PREFIX foaf: <http://xmlns.com/foaf/0.1/>
    SELECT ?person ?mailbox WHERE { ?person foaf:mbox ?mailbox }
  $$);

SELECT * FROM person_emails ORDER BY person_iri;

        person_iri        |         mailbox
--------------------------+--------------------------
 http://example.com/alice | mailto:alice@example.com
 http://example.com/bob   | mailto:bob@example.com

INSERT … SELECT feeds an existing table, with all of PostgreSQL's conflict handling:

sql

INSERT INTO users (iri, region)
SELECT sparql->>'person', 'unassigned'
  FROM pgrdf.sparql($$
    PREFIX foaf: <http://xmlns.com/foaf/0.1/>
    SELECT ?person WHERE { ?person foaf:name ?name }
  $$)
ON CONFLICT (iri) DO NOTHING;

Wrap it in a view

A view runs its SPARQL query every time it is read, so it always reflects the current graph:

sql

CREATE VIEW engineers AS
SELECT sparql->>'person' AS person,
       sparql->>'name'   AS name
  FROM pgrdf.sparql($$
    PREFIX ex:   <http://example.com/>
    PREFIX foaf: <http://xmlns.com/foaf/0.1/>
    SELECT ?person ?name WHERE { ?person a ex:Engineer ; foaf:name ?name }
  $$);

SELECT * FROM engineers ORDER BY name;
--  Alice, Bob

SELECT * FROM pgrdf.sparql($$
  PREFIX ex:   <http://example.com/>
  PREFIX foaf: <http://xmlns.com/foaf/0.1/>
  INSERT DATA { GRAPH <https://example.com/graph/people> { ex:dana a ex:Engineer ; foaf:name "Dana" } }
$$);

SELECT * FROM engineers ORDER BY name;

          person          | name
--------------------------+-------
 http://example.com/alice | Alice
 http://example.com/bob   | Bob
 http://example.com/dana  | Dana

Reporting tools and ORMs see the view as an ordinary table. A WHERE written against the view is applied after the SPARQL query has run, so put selective conditions inside the SPARQL (FILTER, VALUES, LIMIT) when the graph is large.

hubComposing with SQL ​

The row shape ​

Join graph data to a table ​

Name the result in a CTE ​

Materialise into a table ​

Wrap it in a view ​

See also ​